Oracle dbms_redefinition


THE REAL TIME APPROACH TO DEFRAGMENT LARGE TABLES OF SIZE 1-2 TB AND MORE THIS APPROAC IS USED. IT SIGNIFICANTLY REDUCES THE DOWN TIME.

(for table of 700 GB it took around 20 min) where as traditional export,import method would take 2-3 days.

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.

The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the “Create table as select” command:

Here is a simple execution of an online table reorganization:

Here is a simple execution of an online table reorganization:

— Check table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table(‘SCOTT’, ‘EMPLOYEES’);
— Create new table with CTAS
CREATE TABLE scott.employees2
TABLESPACE tools AS
SELECT empno, first_name, salary as sal
FROM employees WHERE 1=2;
— Start Redefinition
EXEC Dbms_Redefinition.Start_Redef_Table( –
  ‘SCOTT’, –
  ‘EMPLOYEES’, –
  ‘EMPLOYEES2’, –
  ‘EMPNO EMPNO, FIRST_NAME FIRST_NAME, SALARY*1.10 SAL);
— Optionally synchronize new table with interim data
EXEC dbms_redefinition.sync_interim_table( –
  ‘SCOTT’, ‘EMPLOYEES’, ‘EMPLOYEES2’);
— Add new keys, FKs and triggers
ALTER TABLE employees2 ADD
(CONSTRAINT emp_pk2 PRIMARY KEY (empno)
USING INDEX
TABLESPACE indx);
— Complete redefinition
EXEC Dbms_Redefinition.Finish_Redef_Table( –
  ‘SCOTT’, ‘EMPLOYEES’, ‘EMPLOYEES2’);
 
— Remove original table which now has the name of the new table
DROP TABLE employees2;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s