-----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博客 ” ，链接：https://blog.itpub.net/26477398/viewspace-2122498/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
