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

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







dbms_redefinition example

Oracle Database Tips by Donald BurlesonJuly 3, 2015

Also see concepts of 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:

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

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

n  Data could be momentarily inconsistent between key steps

n  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 this 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.

Partition a Table

One of the most common table reorganization tasks is to partition a table that is currently not partitioned but that could benefit in manageability and/or performance by becoming partitioned. It may be that this table is a throwback from an earlier Oracle database version like those that were created long ago before partitioning was available or that it simply has grown over time to the point where partitioning makes sense. Another example might be that it is partitioned, but it is so by an older partitioning method or scheme. So if one wants to rebuild a hash partitioned table using Oracle 11g's new interval partitioning, there are many other partitioning scenarios, but the basic idea is this: the table is currently not partitioned or partitioned incorrectly, and this needs to be remedied.


Return once again to the MOVIES demo schema and partition the CUSTOMER table. And like any real world database, the rest of the database design depends on the customer, such as there are foreign keys to it. Not only that, but CUSTOMER has additional indexes and triggers. Here is the complete DDL for CUSTOMER. So as can be seen, it is much more than just a simple standalone table since there are also indexes and triggers that go with this table.


<  .Complete CUSTOMER table DDL












    "GENDER" CHAR(1),

     CHECK (Gender in ('M','F')) ENABLE,

     CHECK (CustomerId > 0) ENABLE,










  ON customer



  -- Declare User Defined Exception

  bad_length  exception;

  pragma exception_init(bad_length,-20001);

  bad_date    exception;

  pragma exception_init(bad_date,-20002);


  -- Check Values for Correct Length

  if (length(rtrim( < 10 or

      length(rtrim(:new.state)) <  2 or

      length(rtrim(   <  5) then

    raise bad_length;

  end if;

  -- Check Dates for Reasonableness

  if (:new.birthdate > sysdate-18*365) then

    raise bad_date;

  end if;

  -- Force Values to All Upper Case

  :new.state  := upper(:new.state);

  :new.gender := upper(:new.gender);


  when bad_length then

    raise_application_error(-20001, 'Illegal length: value shorter than required');

  when bad_date then

    raise_application_error(-20002, 'Illegal date: value fails reasonableness test');



Step 1: Verify that the table is a candidate for online redefinition

This is a very easy step, but it is also a very critical step. If this step fails, then do not attempt to use dbms_redefinition  to rebuild or redefine the table. Since it is known that customer has a primary key from reviewing the prior DD, then it can be verified that it can be used as the redefinition driver. Otherwise, redefinition must function utilizing the data's ROWID. Remember, dbms_redefinition is simply using materialized views behind the scenes.






Step 2: Create an interim table

Assuming that the table is a valid candidate, the interim table can then be created. This will be the partitioned table for the demonstration scenario. Note that the CREATE TABLE AS SELECT (CTAS) method is being used to save time here. The rows are not actually being copied because the SELECT WHERE clause evaluates to false. This is just a relatively easy shorthand method for the copy and, of course, adding the partitioning clause.


create table movies.customer_interim

partition by hash(zip) partitions 8


select * from movies.customer

where 1=0;

Step 3: Enable parallel DML operations

Now for those on multi-processor database servers, parallel operations can be enabled for the session to speed up the redefinition process. This is an optional step, but generally worth considering. Just make sure not to overdo using parallelization. If there is a very fast I/O subsystem and nothing else is really running, then consider up to two or four times of the actual CPU core count. It would also be good to check the db_writers  init.ora  parameter as well because it should be more than one if the choice is to force massive parallel operations that require extensive I/O.  Here are the commands for this.


alter session force parallel dml parallel 4;

alter session force parallel query parallel 4;

Step 4: Start the redefinition process

From this step forward, watch the time between steps. This means that the following steps need to happen in sequence and without major delays between them. This is pointed out because some DBAs are hesitant to put these reorganization steps in a script as they want to manually monitor each step of the process. That is fine, just do not go to lunch or home between them. If everything is ready to proceed to completion, then start the redefinition process.






Step 5: Copy dependent objects

This step performs one of the most critical and easily forgotten steps if this process was done without dbms_redefinition  ? to automatically create any required triggers, indexes, materialized view logs, grants, and/or constraints on the table. If one refers back to the section about DDL extraction via dbms_metadata , it is easy to guess that Oracle is eating their own cooking internally here. Now it makes a little more sense as to why dbms_metadata was designed as it is. Look how easy it is to copy all dependent objects with just a single call to dbms_redefinition.



  num_errors PLS_INTEGER;






Step 6: Check for any errors

It is advisable now to check that this last operation completed successfully. This is stated because remember that it is doing quite a few things in the background here. It is quite possible for some things to need reviewing and possibly fixed manually. In most cases there should be no rows returned, so proceed.


select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

Step 7: Synchronize the interim table (optional)

If there has been any activity or transaction between the start of the redefinition and now, it might be advisable to resynchronize the interim table one more time. When in doubt, it is very much like chicken soup here - it may not help, but it will not hurt anything either.






Step 8: Complete the redefinition

This step does two things: it severs the behind-the-scenes materialized view connection and swaps the data dictionary entries for the table and interim table. So now, what was the interim table is caught up on structural modifications and any data transactions. Thus, it is safe to make this data dictionary entry swap.






Step 9: Drop the interim table

The interim table is now finished which, as of the last step, is actually the original table via the dictionary entry swap done by the finish operation. So drop that table. And if there is a concern about the data, an option is to do a SELECT against the new original table to verify that nothing has been lost.


drop table movies.customer_interim cascade constraints purge;

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster