按用户重建索引

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;

oracle9i好像不需要STORAGE子句

ALTER INDEX index_name REBUILD ONLINE TABLESPACE tablespace_name;

如果表上建有位图索引（BITMAP Index），则只能将索引删除以后，重建.

select dbms_metadata.get_ddl('INDEX','CMS_TEMPLATE_ISACTIVE') from dual;