schema_table_frag_scripts_sql

发表于 2015 年 01 月 13 日 由 Travel.liu

前几天闲着没事,捣鼓了查看整个用户下的表碎片的信息,脚本根据表的统计信息来计算的。现在此脚本只考虑了分区表和普通表,其他类型的表统计出来的信息会可能存在问题.

SET SERVEROUTPUT ON
DECLARE
 
    L_BLOCKSIZE     NUMBER;
    L_OWNER         VARCHAR2(40) :=UPPER('TRAVEL');--user name
    L_CT            NUMBER ;
 
    CURSOR C_TABLE IS
    SELECT OWNER,
           TABLE_NAME,
           BLOCKS,
           AVG_ROW_LEN,
           NUM_ROWS,
           PARTITIONED,
           LAST_ANALYZED,
           TABLESPACE_NAME
    FROM DBA_TABLES
    WHERE OWNER = L_OWNER;
 
    CURSOR C_TABLE_PRAT(TAB_NAME IN DBA_TAB_PARTITIONS.TABLE_NAME%TYPE)  IS
    SELECT PARTITION_NAME,
           SUBPARTITION_COUNT,
           BLOCKS,
           AVG_ROW_LEN,
           NUM_ROWS,
           LAST_ANALYZED,
           SAMPLE_SIZE,
           TABLESPACE_NAME
    FROM DBA_TAB_PARTITIONS
    WHERE TABLE_OWNER=L_OWNER
      AND TABLE_NAME=TAB_NAME;
 
    CURSOR C_TABLE_SUBPRAT(TAB_NAME IN DBA_TAB_PARTITIONS.TABLE_NAME%TYPE,TAB_PART_NAME IN DBA_TAB_SUBPARTITIONS.PARTITION_NAME%TYPE)  IS
    SELECT PARTITION_NAME,
           SUBPARTITION_NAME,
           BLOCKS,
           AVG_ROW_LEN,
           NUM_ROWS,
           LAST_ANALYZED,
           SAMPLE_SIZE,
           TABLESPACE_NAME
    FROM DBA_TAB_SUBPARTITIONS
    WHERE TABLE_OWNER=L_OWNER
      AND TABLE_NAME=TAB_NAME
      AND PARTITION_NAME=TAB_PART_NAME;
 
BEGIN
    DBMS_OUTPUT.ENABLE (1000000000);
    DBMS_OUTPUT.PUT_LINE ('OWNER           TABLE NAME                                     TABLE SIZE        DATA SIZE       FRAG SIZE');
    DBMS_OUTPUT.PUT_LINE ('--------------- -------------------------------------------- ------------ ---------------- ---------------');
 
    FOR R_TAB IN C_TABLE LOOP
        IF R_TAB.PARTITIONED='NO' THEN
            SELECT BLOCK_SIZE INTO L_BLOCKSIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=R_TAB.TABLESPACE_NAME;
            DBMS_OUTPUT.PUT_LINE (RPAD(R_TAB.OWNER, 16, ' ')
                ||RPAD(R_TAB.TABLE_NAME, 40, ' ')
                ||LPAD(ROUND((R_TAB.BLOCKS*L_BLOCKSIZE/1024),2),17,' ')
                ||LPAD(ROUND((R_TAB.NUM_ROWS*R_TAB.AVG_ROW_LEN/1024),2),17,' ')
                ||LPAD( ROUND((R_TAB.BLOCKS*L_BLOCKSIZE-R_TAB.NUM_ROWS*R_TAB.AVG_ROW_LEN)/1024,2),16,' ' ));
        ELSIF R_TAB.PARTITIONED='YES' THEN
            FOR  R_TAB_PART IN C_TABLE_PRAT(R_TAB.TABLE_NAME) LOOP
                IF R_TAB_PART.SUBPARTITION_COUNT = 0 THEN
                    SELECT BLOCK_SIZE INTO L_BLOCKSIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=R_TAB_PART.TABLESPACE_NAME;
                    DBMS_OUTPUT.PUT_LINE (RPAD(R_TAB.OWNER, 16, ' ')
                    ||RPAD(R_TAB.TABLE_NAME||'.PART->'||R_TAB_PART.PARTITION_NAME, 40, ' ')
                    ||LPAD(ROUND((R_TAB_PART.BLOCKS*L_BLOCKSIZE/1024),2),17,' ')
                    ||LPAD(ROUND((R_TAB_PART.NUM_ROWS*R_TAB_PART.AVG_ROW_LEN/1024),2),17,' ')
                    ||LPAD( ROUND((R_TAB_PART.BLOCKS*L_BLOCKSIZE-R_TAB_PART.NUM_ROWS*R_TAB_PART.AVG_ROW_LEN)/1024,2),16,' ' ));
                ELSE
                    FOR  R_TAB_SUBPART IN C_TABLE_SUBPRAT(R_TAB.TABLE_NAME,R_TAB_PART.PARTITION_NAME) LOOP
                        SELECT BLOCK_SIZE INTO L_BLOCKSIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=R_TAB_SUBPART.TABLESPACE_NAME;
                        DBMS_OUTPUT.PUT_LINE (RPAD(R_TAB.OWNER, 16, ' ')
                        ||RPAD(R_TAB.TABLE_NAME||'.SUBPART->'||R_TAB_SUBPART.SUBPARTITION_NAME, 40, ' ')
                        ||LPAD(ROUND((R_TAB_SUBPART.BLOCKS*L_BLOCKSIZE/1024),2),17,' ')
                        ||LPAD(ROUND((R_TAB_SUBPART.NUM_ROWS*R_TAB_SUBPART.AVG_ROW_LEN/1024),2),17,' ')
                        ||LPAD( ROUND((R_TAB_SUBPART.BLOCKS*L_BLOCKSIZE-R_TAB_SUBPART.NUM_ROWS*R_TAB_SUBPART.AVG_ROW_LEN)/1024,2),16,' ' ));
                    END LOOP;
                END IF;
            END LOOP;
        END IF;
    END LOOP;
END;
/
此条目发表在 未分类 分类目录。将固定链接加入收藏夹。

评论功能已关闭。