Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_REDEFINITION
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

DBMS_REDEFINITION

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.


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.