Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle dbms_redefinition

Oracle Tips by Burleson Consulting
 

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

- See here for a full example of dbms_redefinition

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;

 

Using Oracle dbms_redefinition

To solve the problem of doing table reorgs while the database accepts updates, Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.

The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the
table, enqueue changes during the redefinition, and then re-synchronize the restructured table
with the changes that have accumulated during reorganization.

exec dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');

alter table titles add constraint pk_titles primary key (title_id);

exec dbms_redefinition.can_redef_table('PUBS','TITLES');

create table titles2

as

select * from titles;

exec dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id title_id,title
title,type type,pub_id pub_id,price price,advance advance,royalty*1.1 royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');

exec dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');

exec dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');

If your reorganization fails, you must take special steps to make it re-start. Because the
redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to
release the snapshot to re-start you procedure.

The ‘dbms_redefinition.abort_redef_table’ procedure which accepts 3 parameters (schema, original table name, holding table name), and which “pops the stack” and allows you to start over.



No database is 100% self-reliant or self-maintaining, which is a good thing for DBA job security. However, the last few major versions of Oracle have greatly increased its self-diagnostic and self-monitoring capabilities. Only database structural reorganization remains one of those tasks best left to the DBA to decide when it is appropriate to perform and when to schedule its execution. That is because data is the life blood of any modern organization, and while doing various database reorganizations, the following possibilities exist:

  • The process could blow-up mid-stream, so data may be left offline

  • The process is resource-intensive and takes significant time to execute

  • Data could be momentarily inconsistent between key steps

  • Probably advisable to consider doing a backup operation just prior to

The key point is that structural reorganizations are generally important events in any database’s life cycle. Even when a reorganization activity can theoretically be performed entirely online with little or no downtime, it is often a safer bet to perform any such activities in a controlled environment. Because the one time something that can not go wrong does, the DBA will be in a better situation to resume or recover if there are not frantic customers breathing down his neck. So schedule any reorganization event with extreme caution and over- compensation.

 

Now  with all that said, Oracle provides a robust and reliable package for performing many common online table level reorganizations – dbms_redefinition. Much like the dbms_metadata package, dbms_redefinition provides an almost limitless set of use cases or scenarios that it can address. Many people will probably just use the OEM graphical interface, but  here is a very common example that should fulfill as key need as well as serve as a foundation for one’s own modifications. The following are the key basic steps:

1.      Verify that the table is a candidate for online redefinition

2.      Create an interim table

3.      Enable parallel DML operations

4.      Start the redefinition process (and do not stop until step 9 is done)

5.      Copy dependent objects

6.      Check for any errors

7.      Synchronize the interim table (optional)

8.      Complete the redefinition

9.      Drop the interim table

A common question is what is happening behind the scenes here? In other words, how and what is Oracle doing? Essentially, the redefinition package is merely an API to an intelligent materialized view with a materialized view log. So a local replication of the object shows while the reorganization occurs. Then it refreshes to get up-to-date for any transaction that occurred during reorganization.

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.