Oracle Tips by Burleson

Oracle 10g Example of online redefinition of a table

Let us look at the total online redefinition process using these newly introduced features and enhancements. We have a table called dly_price with the structure shown below and it has a primary key on ticker and date_px. It also has two additional indexes.

SQL>desc  dly_price

 Name                   Null?    Type
 ---------------------- -------- ----------
 TICKER                          VARCHAR2(4)
 CLOSE_PX                        NUMBER
 OPEN_PX                         NUMBER
 LOW_PX                          NUMBER
 DATE_PX                         DATE

It also has the following indexes.

Table_Name      Index_Name     Pos Column_Name
-------------- --------------  --- --------------
* DLY_PRICE    DLY_PRICE_IND1    1   TICKER
* DLY_PRICE    DLY_PRICE_IND2    1   DATE_PX
* DLY_PRICE    SYS_C003340       1   TICKER
* DLY_PRICE    SYS_C003340       2   DATE_PX

We intend to redefine the table by dropping one column, modifying another column, and adding an additional column.

Step 1.

First, determine the method of redefinition. Here, we will use the primary key to perform the redefinition. When we set the value of dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys. If you set the value to dbms_redefinition.cons_use_rowid, then the redefinition is done using ROWIDs. This procedure will also determine whether the table is redefinable according to the documented limitations, and if not, issue an error message telling why the table is not redefinable.

SQL>exec DBMS_REDEFINITION.CAN_REDEF_TABLE('test1', -'dly_price', dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

With Oracle database 10g, it is possible to use pseudo-primary keys, unique keys with NOT NULL constraints in all component columns.

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm