oldrebuildindex.sql

来自「本人在工程上收集的比较常用的oracle sql语句 有问题请发email:l」· SQL 代码 · 共 36 行

	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 c:\rebuild.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;