HOW TO SHRINK A TABLE USING ONLINE REDEFINITION

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.2 [Release 9.0.1 to 11.2]
Information in this document applies to any platform.

GOAL

This article will demonstrate the use online redefinition (DBMS_REDEFINITION) to shrink the size of a sparsely populated table.

ALTER TABLE ... SHRINK space will lock a table for the duration of operation .. as such ... it may be desirable to resize the table online.

NOTE : To shrink a table in this manner will temporarily need the amount of space required to store the original table ... as well as the shrunk table

SOLUTION

The use of DBMS_REDEFINITION will, by its very nature, make the resulting table smaller if the original table is sparse enough such that the space created by deleting rows causes enough space to be freed that the resulting redefined table uses less storage space (total of extent sizes) than the original.

The case study below creates a table and then loads data into it such that 40 ... 1mb extents are created ... and then 66% of the rows in the table are deleted. 

After this the sparse table is redefined and the resulting table only has 14 ... 1mb extents 


CASE STUDY

-- INITIAL SETUP FOR BOTH CASE STUDIES

CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/v11202/TEST01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

create user test identified by test;
grant dba to test;
alter user test default tablespace test;
connect test/test;

create table ORIGINAL (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));

-- LOAD DATA INTO THE ORIGINAL TABLE

declare
   v_out varchar2(1000);
begin
   v_out := null;
   for i in 1..1000 loop
      v_out := v_out||'A';
   end loop;
   for i in 1..10000 loop
      insert into ORIGINAL values (i,v_out,v_out,v_out);
      if i/10000 = trunc(i/1000) then
         commit;
      end if;
   end loop;
   commit;
end;
/

-- DELETE 2/3 OF THE ROWS IN ORIGINAL TO MAKE THE TABLE SPARSE

delete from ORIGINAL where (COL1/3) <> trunc(COL1/3);

-- 6667 rows deleted.

COMMIT;

-- EXAMINE THE STORAGE 

column segment_name format a20

SELECT COUNT(*), SEGMENT_NAME FROM USER_EXTENTS GROUP BY SEGMENT_NAME;

COUNT(*) SEGMENT_NAME
----------     --------------------
40             ORIGINAL

-- DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

SET SERVEROUTPUT ON

BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- CREATE THE INTERIM TABLE 

create table INTERIM (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));

-- START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                 uname => 'TEST',
                 orig_table => 'ORIGINAL',
                 int_table => 'INTERIM',
                 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- PL/SQL procedure successfully completed.

-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST', 'ORIGINAL', 'INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- PL/SQL procedure successfully completed.

-- DO ONE FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'ORIGINAL', 'INTERIM');
END;
/

-- PL/SQL procedure successfully completed.

-- FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','ORIGINAL','INTERIM');

-- PL/SQL procedure successfully completed.

-- EXAMINE THE STORAGE AFTER THE REDEFINITION

SELECT COUNT(*), SEGMENT_NAME FROM USER_EXTENTS GROUP BY SEGMENT_NAME;

COUNT(*) SEGMENT_NAME
----------      --------------------
14              ORIGINAL
40              INTERIM

-- DROP THE INTERIM TABLE (as it is no longer needed) 

DROP TABLE INTERIM; 

Table dropped.


-- CLEAN UP 

CONNECT / AS SYSDBA

DROP USER TEST CASCADE;
DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;