dbms_redefinition example

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.

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.

BEGIN

  DBMS_REDEFINITION.CAN_REDEF_TABLE ('MOVIES', 'CUSTOMER', DBMS_REDEFINITION.CONS_USE_PK);

END;

/

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

as

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.

BEGIN

  DBMS_REDEFINITION.START_REDEF_TABLE('MOVIES','CUSTOMER','CUSTOMER_INTERIM');

END;

/

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.

DECLARE

  num_errors PLS_INTEGER;

BEGIN

  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('MOVIES',  'CUSTOMER', 'CUSTOMER_INTERIM',

    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

/

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.

BEGIN

  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('MOVIES', 'CUSTOMER', 'CUSTOMER_INTERIM');

END;

/

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.

BEGIN

  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('MOVIES', 'CUSTOMER', 'CUSTOMER_INTERIM');

END;

/

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.