Rebuild Indexes

-----check INDEX SIZE more than 1 gega bytes

SELECT OWNER AS OWNER,

       SEGMENT_NAME AS SEGMENT_NAME,

       SEGMENT_TYPE,

       SUM(BYTES) / 1024 / 1024 AS "SEGMENT_SIZE(M)"

  FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = '&TBS'

AND BYTES > 1073741824

GROUP BY OWNER, SEGMENT_NAME,SEGMENT_TYPE

ORDER BY 3;

----analyze the INDEXES in tablespace which the space used rate are too high

SELECT 'ANALYZE INDEX ' || SEGMENT_NAME || ' VALIDATE STRUCTURE;'

  FROM DBA_SEGMENTS

 WHERE TABLESPACE_NAME = '&TBS'

   AND BYTES > 1073741824

   AND SEGMENT_TYPE = 'INDEX'

 ORDER BY  SEGMENT_NAME ;

----check the empty blocks in INDEXES

SELECT NAME, BLOCKS, LF_BLKS, BR_BLKS, BLOCKS - (LF_BLKS + BR_BLKS) EMPTY

  FROM INDEX_STATS

 WHERE NAME IN (SELECT SEGMENT_NAME

                  FROM DBA_SEGMENTS

                 WHERE TABLESPACE_NAME = '&TBS'

                   AND BYTES > 1073741824

                   AND SEGMENT_TYPE = 'INDEX'

                 ORDER BY SEGMENT_NAME);

----check delete rows in INDEXES

SELECT NAME,

       LF_ROWS,

       T.LF_BLKS,

       T.DEL_LF_ROWS,

       (T.DEL_LF_ROWS / T.LF_ROWS) * 100 RATIO

  FROM INDEX_STATS T

WHERE T.NAME = '&IND_NAME';

----check INDEXES degree and usage rate

SELECT HEIGHT, NAME, BTREE_SPACE, USED_SPACE, PCT_USED FROM INDEX_STATS;

if found the degree of indexes are bigger than 4, We can rebuild those indexes

----gather table

SELECT 'exec dbms_stats.gather_table_stats(''' || OWNER || ''',''' ||

       TABLE_NAME || ''',' || 'DEGREE=>8,CASCADE => TRUE);'

  FROM DBA_INDEXES

WHERE TABLESPACE_NAME = '&TBS';

----ensure indexes used size

SELECT OWNER AS OWNER,

       SEGMENT_NAME AS SEGMENT_NAME,

       SUM(BYTES) / 1024 / 1024 AS "SEGMENT_SIZE(M)"

  FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'MD0002ASTCOM_SNAP_INDEX'

GROUP BY OWNER, SEGMENT_NAME

ORDER BY 3;

 

----connect the schema where indexes is

alter session set current_schema=MD0002ASTCOM;

----rebuild the indexes which size are bigger than 2G

select 'alter index '||owner||'.'||index_name|| ' rebuild online' from dba_indexes where tablespace_name='MD0002ASTCOM_SNAP_INDEX';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26477398/viewspace-2122498/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26477398/viewspace-2122498/