脚本
set pagesize 0
set termout off feedback off echo off
col obj format a200
spool d:\dropTableConstraint.sql
select 'alter table ' table_name ' drop constraint ' constraint_name ' ;' obj
from all_constraints
where owner = 'DXSQ_INTER'
and constraint_type <> 'C'
;
spool
set pagesize 20
2、移除表索引的脚本
set pagesize 0
set termout off feedback off echo off
col obj format a200
spool d:\dropTableIndexes.sql
select 'drop index ' owner '.' segment_name ' ;' obj
from dba_segments
where owner = 'DXSQ_INTER'
and segment_type = 'INDEX'
and tablespace_name = 'TS_NPDXSQ_INTER_IDX'
;
spool
set pagesize 20
3、重建表索引的脚本
set pagesize 0
set termout off feedback off echo off
spool d:\rebuildTableIndexes.sql
select 'REM +-----------------------------------------------+' chr(10)
'REM INDEX NAME : ' owner '.' segment_name
lpad(' ', 33 - (length(owner) + length(segment_name)) )
chr(10)
'REM BYTES : ' bytes
lpad (' ', 34-(length(bytes)) ) chr(10)
'REM EXTENTS : ' extents
lpad (' ', 34-(length(extents)) ) chr(10)
'REM +-----------------------------------------------+' chr(10)
'ALTER INDEX ' owner '.' segment_name chr(10)
'REBUILD ' chr(10)
'TABLESPACE ' tablespace_name chr(10)
'STORAGE ( ' chr(10)
' INITIAL ' initial_extent chr(10)
' NEXT ' next_extent chr(10)
' MINEXTENTS ' min_extents chr(10)
' MAXEXTENTS ' max_extents chr(10)
' PCTINCREASE ' pct_increase chr(10)
');' chr(10) chr(10)
FROM dba_segments
WHERE segment_type = 'INDEX'
AND owner='&username'
AND tablespace_name = '&space'
ORDER BY owner, bytes DESC;
spool off
set pagesize 20
--转自