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;


-- 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;


-- 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;