Overview

Querying Tables Subject to Reorg

When a large volume of DML occurs on a table, a Reorg may be necessary to recycle space and improve query performance.

Tables subject to Reorg can be selected based on generated statistics information, but it is difficult to accurately measure usage based on statistics alone.

To check usage accurately, you can use the DBMS_SPACE package.

Method

Query

SET serveroutput ON
SET PAGESIZE 100
SET LINESIZE 100

ACCEPT owner PROMPT 'Enter Owner : '
variable owner       varchar2(100)

DECLARE
       USED_BLOCKS NUMBER;
       FRM_BLOCKS NUMBER;
       FRM_RATIO NUMBER;
       uf   NUMBER;
       ub   NUMBER;
       f1   NUMBER;
       f1b  NUMBER;
       f2   NUMBER;
       f2b  NUMBER;
       f3   NUMBER;
       f3b  NUMBER;
       f4   NUMBER;
       f4b  NUMBER;
       fbl  NUMBER;
       fby  NUMBER;
BEGIN
       :owner := upper('&owner');
       FOR tb_list in( SELECT OWNER,TABLE_NAME
                         FROM DBA_TABLES
                        WHERE OWNER = :owner
                       ) LOOP
               BEGIN
                       dbms_space.space_usage(tb_list.owner, tb_list.table_name,'TABLE',uf,ub,f1,f1b,f2,f2b,f3,f3b,f4,f4b,fbl,fby);
                       USED_BLOCKS  :=  f1 + f2 + f3 + f4 + fbl;
                       FRM_BLOCKS := f3+f4;
                       IF USED_BLOCKS = 0 THEN
                               dbms_output.put_line('----------------------');
                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' is not used.');
                       ELSE
                               FRM_RATIO:= ROUND(FRM_BLOCKS/ USED_BLOCKS*100);
                               IF USED_BLOCKS>100 THEN
                                       IF FRM_RATIO> 20 THEN  -- reorg target table fragmentation threshold set to 20% or higher (modifiable)
                                               dbms_output.put_line('#######################');
                                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' is fragmented '||FRM_RATIO||'%');
                                               dbms_output.put_line('unformatted blocks:  ' || TO_CHAR(uf));
                                               dbms_output.put_line('blocks 0-25% free:   ' || TO_CHAR(f1));
                                               dbms_output.put_line('blocks 25-50% free:  ' || TO_CHAR(f2));
                                               dbms_output.put_line('blocks 50-75% free:  ' || TO_CHAR(f3));
                                               dbms_output.put_line('blocks 75-100% free: ' || TO_CHAR(f4));
                                               dbms_output.put_line('full blocks:         ' || TO_CHAR(fbl));
                                       ELSE
                                               dbms_output.put_line('----------------------');
                                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' pass ['|| FRM_RATIO ||'%]');
                                       End If;
                               END IF;
                       END IF;
               EXCEPTION
               WHEN OTHERS THEN
                       dbms_output.put_line( TO_CHAR (SQLCODE) || ':' || SQLERRM);
               END;
       END LOOP;
END;
/