SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET PAGESIZE 0;
SET TERMOUT ON;
SET HEADING OFF;
ACCEPT username CHAR PROMPT 'Enter the index username: ';
spool /oracle/rebuild_&username.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'
ORDER BY owner, bytes DESC;
spool off;
-----------------------------------------------------------------------------
如果用的是WINDOWS系统, 想改变输出文件的存放目录, 修改spool后面的路径成:spool c:/oracle/rebuild_&username.sql;
如果只想对大于max_bytes的索引重建索引, 可以修改上面的SQL语句: 在AND owner='&username' 后面加个限制条件 AND bytes> &max_bytes
如果想修改索引的存储参数, 在重建索引rebuild_&username.sql里改也可以. 比如把pctincrease不等于零的值改成是零.
如果想把index从一个TABLESPACE转移到另外一个TABLESPACE,也可以修改其中的tablespace_name
--转自