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;