DBMS REDEFINITION package for tables online movement data in Oracle

 
Following are the Steps involved for On-line fragmentation removal or movement:
Note: Before start take the full database backup.

Step 1: Check the table count of record for verification at end.

Select count(*) from table_name;

Step 2: Get the structure/DDL statement of the table with scripts:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','TRAN','SCOTT') from dual;

Step 3: Check the invalid objects of the table.

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;

Step 4: Check the v$access view to monitor the access of the table.

Select * from v$access

Step 5: Start the redefinition of the package.

exec dbms_redefinition.can_redef_table('schema_name','Table_name',dbms_redefinition.cons_use_rowid);

Step 6: Created the second table.

create table schema_name.table_name_temp tablespace APPS_TS_ARCHIVE as select * from schema_name.table_name where 1=2;

Step 7: Start the process of redefinition.

exec dbms_redefinition.start_redef_table('schema_name','table_name','table_name_temp',NULL,dbms_redefinition.cons_use_rowid);

Step 8: Start copy process of dependence.

SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname             => 'schema_name',
orig_table        => 'table_name',
int_table         => 'table_name_temp',
copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers     => TRUE,  -- Default
copy_constraints  => TRUE,  -- Default
copy_privileges   => TRUE,  -- Default
ignore_errors     => TRUE, -- Default
num_errors        => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

Step 9: Start sync process.

exec dbms_redefinition.sync_interim_table('schema_name','table_name','table_name_temp');

Step 10: Start finish process.

exec dbms_redefinition.finish_redef_table('schema_name','table_name','table_name_temp');

Step 11: drop the temp table created.

drop table schema_name.table_name_temppurge;

Step 12: Verify the count of table.

select count(*) from schema_name.table_name;

Step 13: Check the invalid objects.

select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;