[Oracle 10g] shrink table / partition table Script

Table shrink space 測試

目標:

  消除無用空間for tables/partition tables

說明:

   1.相關表格需先分析過才可撈取正確used space , total allocated space ,  使用比率可自行定義 再做table move.

   2.索引如須一起處理, 須下以下指令
      Alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION P_Name shrink space cascade;

以下為一般表格測試

SQL>

create tablespace shrink_tbs datafile '/oracle/oradata/orcl/shrink.dbf' size 8M autoextend on next 8M;

create table test.shrink_tab as select * from dba_objects;

col segment_name for a20

select segment_name , bytes/1024/1024 "MB" from dba_segments where segment_name='SHRINK_TAB';

SEGMENT_NAME                 MB

-------------------- ----------

SHRINK_TAB                    6

insert into test.shrink_tab select * from dba_objects;

commit;

select segment_name , bytes/1024/1024 "MB" from dba_segments where segment_name='SHRINK_TAB';

SEGMENT_NAME                 MB

-------------------- ----------

SHRINK_TAB                   12

declare

su number;

sa number;

pn varchar2(20);

begin

FOR c1_row IN (SELECT table_name , owner

FROM dba_tables where table_name='SHRINK_TAB'

) LOOP

dbms_space.object_space_usage(c1_row.owner ,c1_row.table_name ,'TABLE',0.0,su,sa,pn);

--dbms_output.put_line('----- Object space used in Kb -----');

--dbms_output.put_line(su/1024);

--dbms_output.put_line('----- Object space allocated in Kb-----');

--dbms_output.put_line(sa/1024);

 if ( round(( su / sa ) ,2 ) < 0.9 ) then

    dbms_output.put_line('Table '||c1_row.owner||'.'||c1_row.table_name ||' used ratio is '||round(( su / sa )*100 ,2 )||'%');

    dbms_output.put_line('---------------------------------------------------------------');

    dbms_output.put_line('alter table '||c1_row.owner||'.'||c1_row.table_name ||' enable row movement;');

    dbms_output.put_line('alter table '||c1_row.owner||'.'||c1_row.table_name ||' shrink space;');

  

    

 end if;

END LOOP;

end;

/

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> delete from test.shrink_tab;

51809 rows deleted.

SQL> commit;

Commit complete.

SQL>

declare

su number;

sa number;

pn varchar2(20);

begin

FOR c1_row IN (SELECT table_name , owner

FROM dba_tables where table_name='SHRINK_TAB'

) LOOP

dbms_space.object_space_usage(c1_row.owner ,c1_row.table_name ,'TABLE',0.0,su,sa,pn);

--dbms_output.put_line('----- Object space used in Kb -----');

--dbms_output.put_line(su/1024);

--dbms_output.put_line('----- Object space allocated in Kb-----');

--dbms_output.put_line(sa/1024);

 if ( round(( su / sa ) ,2 ) < 0.7 ) then

    dbms_output.put_line('Table '||c1_row.owner||'.'||c1_row.table_name ||' used ratio is '||round(( su / sa )*100 ,2 )||'%');

    dbms_output.put_line('---------------------------------------------------------------');

    dbms_output.put_line('alter table '||c1_row.owner||'.'||c1_row.table_name ||' enable row movement;');

    dbms_output.put_line('alter table '||c1_row.owner||'.'||c1_row.table_name ||' shrink space;');

  

    

 end if;

END LOOP;

end;

/

SQL>

Table TEST.SHRINK_TAB used ratio is 13%

---------------------------------------------------------------

alter table TEST.SHRINK_TAB enable row movement;

alter table TEST.SHRINK_TAB shrink space;

PL/SQL procedure successfully completed.

SQL>

以下為分割表格測試 For partition tables

DDL:

CREATE TABLE HR.EMPLOYEE_BIRTH

(BIRTHDAY date,

EMPLOYEE_NAME varchar(20))

PARTITION BY RANGE(BIRTHDAY)

(PARTITION p1 VALUES LESS THAN(TO_DATE('01/04/2011','DD/MM/YYYY') )tablespace 2011_Q1,

PARTITION p2 VALUES LESS THAN(TO_DATE('01/07/2011','DD/MM/YYYY') )tablespace 2011_Q2,

PARTITION p3 VALUES LESS THAN(TO_DATE('01/10/2011','DD/MM/YYYY') )tablespace 2011_Q3,

PARTITION p4 VALUES LESS THAN(TO_DATE('01/01/2012','DD/MM/YYYY') )tablespace 2011_Q4;

SQL>

declare

su number;

sa number;

pn varchar2(20);

begin

FOR c1_row IN (SELECT table_name , table_owner , partition_name

FROM dba_tab_partitions where table_name='EMPLOYEE_BIRTH'

) LOOP

dbms_space.object_space_usage(c1_row.table_owner ,c1_row.table_name ,'TABLE PARTITION',0.0,su,sa,pn,c1_row.partition_name);

--dbms_output.put_line('----- Object space used in Kb -----');

--dbms_output.put_line(su/1024);

--dbms_output.put_line('----- Object space allocated in Kb-----');

--dbms_output.put_line(sa/1024);

 if ( round(( su / sa ) ,2 ) < 0.7 ) then

    dbms_output.put_line('Table '||c1_row.table_owner||'.'||c1_row.table_name ||' partition '||c1_row.partition_name||' used ratio is '||round(( su / sa )*100 ,2 )||'%');

    dbms_output.put_line('---------------------------------------------------------------');

    dbms_output.put_line('alter table '||c1_row.table_owner||'.'||c1_row.table_name ||' enable row movement;');

    dbms_output.put_line('alter table '||c1_row.table_owner||'.'||c1_row.table_name ||' MODIFY PARTITION '||c1_row.partition_name||' shrink space;');

  

    

 end if;

END LOOP;

end;

/

SQL>

Table HR.EMPLOYEE_BIRTH partition P3 used ratio is .16%

---------------------------------------------------------------

alter table HR.EMPLOYEE_BIRTH enable row movement;

alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION P3 shrink space;

Table HR.EMPLOYEE_BIRTH partition P4 used ratio is .16%

---------------------------------------------------------------

alter table HR.EMPLOYEE_BIRTH enable row movement;

alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION P4 shrink space;

Table HR.EMPLOYEE_BIRTH partition PMAX used ratio is .16%

---------------------------------------------------------------

alter table HR.EMPLOYEE_BIRTH enable row movement;

alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION PMAX shrink space;

Table HR.EMPLOYEE_BIRTH partition P1 used ratio is .16%

---------------------------------------------------------------

alter table HR.EMPLOYEE_BIRTH enable row movement;

alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION P1 shrink space;

Table HR.EMPLOYEE_BIRTH partition P2 used ratio is .16%

---------------------------------------------------------------

alter table HR.EMPLOYEE_BIRTH enable row movement;

alter table HR.EMPLOYEE_BIRTH MODIFY PARTITION P2 shrink space;

PL/SQL procedure successfully completed.

SQL>