dbms_redefinition.sql

alter session force parallel dml parallel 16;
alter session force parallel query parallel 16;

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
    PROCEDURE migrateTable(sourceTable varchar2) IS
        interimTable varchar2(30) := 'XX' || substr(sourceTable, '3');

        str varchar2(1000);
        cnt pls_integer;

    BEGIN
        -- Create interimTable
        -- first approach creates table with NOT NULL constraints
        -- second approach create table without NOT NULL constraints; these can be added back later
        -- str := 'create table '||interimTable||' as select * from '||sourceTable||' WHERE rownum=0';
        str := 'create table '||interimTable||' as select * from '||sourceTable||' WHERE 1=2 MINUS select * from '||sourceTable||' WHERE 1=2';
        EXECUTE IMMEDIATE str;
        str := 'alter table '||interimTable||' pctfree 5';
        EXECUTE IMMEDIATE str;

        -- Clear prior redefinition jobs for sourceTable, if any
        dbms_redefinition.ABORT_REDEF_TABLE(user, sourceTable, interimTable);
        
        -- Start table redefinition
        dbms_redefinition.START_REDEF_TABLE(user, sourceTable, interimTable);

        -- Sync table
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);
        
        -- Copy table dependents
        cnt := 0;
        dbms_redefinition.COPY_TABLE_DEPENDENTS(user, sourceTable, interimTable, 1, TRUE, TRUE, TRUE, FALSE, cnt);
        
        -- Sync table
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);

        -- Check for errors
        IF cnt > 0 THEN
            str := 'There were errors. See DBA_REDEFINITION_ERRORS for details.';
            RAISE_APPLICATION_ERROR(-20000, str);
        END IF;

        -- Sync table
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);

        -- Copy data defaults
        FOR r IN (
            SELECT column_name, data_default
              FROM user_tab_columns
             WHERE table_name = sourceTable
               AND data_default IS NOT NULL
        ) LOOP
            str := 'ALTER TABLE '||interimTable||' modify ('||r.column_name||' default '||r.data_default||')' ;
            EXECUTE IMMEDIATE str;
        END LOOP;

        -- Clean up column constraints
        FOR r IN (
            SELECT 'ALTER TABLE '||table_name||' ENABLE VALIDATE CONSTRAINT '||constraint_name as cmd
              FROM user_constraints WHERE validated = 'NOT VALIDATED' AND table_name = interimTable
        ) LOOP
            DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);
            EXECUTE IMMEDIATE r.cmd;
        END LOOP;

        -- Sync table
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);
        
        -- Gather stats
        DBMS_STATS.GATHER_TABLE_STATS(user, interimTable, estimate_percent => 15, cascade=>true, degree=>8, method_opt => 'FOR ALL COLUMNS SIZE 1');

        -- Sync table
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user, sourceTable, interimTable);

        -- Finish table redefinition
        DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, sourceTable, interimTable);

        -- Clean up referential constraints
        -- Child tables now have referential constraints to both sourceTable and interimTable
        FOR r IN (
            SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name as cmd
              FROM user_constraints WHERE r_constraint_name IN (
                       SELECT constraint_name FROM user_constraints WHERE table_name = interimTable and constraint_type IN ('P','U')
                   )
        ) LOOP
            EXECUTE IMMEDIATE r.cmd;
        END LOOP;

        -- Drop interim table
        str := 'drop table '||interimTable;
        EXECUTE IMMEDIATE str;
        
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
    END;

BEGIN
    migrateTable('MY_TABLE');
    migrateTable('ANOTHER_TABLE');
END;
/