Oracle10g high_px open_px start_redef_table orderby_cols dbms_redefinition.cons_use_pk copy_table

Oracle Tips by Burleson

Oracle 10g Example of online redefinition of a table

Step 2.

We need to create the interim table with an appropriate definition. This table structure will reflect addition, modification, and deletion of columns.

SQL>create table int_dly_price
2 ( ticker varchar2(6),
3    close_px number,
4    low_px number,
5    high_px number,
6    date_px date) ;

Table created.

As we see in the create table statement, we have added a high_px column and removed the open_px column. Also notice that we have increased ticker column size to 6 from 4.

Step 3.

Now start the online redefinition process by executing the start_redef_table procedure. Supply the parameters, such as table to be redefined, interim table name, the column mapping, and the method of redefinition. Optionally, we can also specify the columns to be used for ordering rows by using the orderby_cols. This is a new feature in Oracle Database 10g.

SQL>BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('test1', 'dly_price','int_dly_price',
3 'ticker ticker, close_px close_px, low_px low_px, 0 high_px, date_px date_px',
4 dbms_redefinition.cons_use_pk);
5 END ;
/

PL/SQL procedure successfully completed.

As you will notice, high_px is a new column and will have the value of ‘0’. And open_px does not appear in the column mapping, since it gets removed after redefinition.

Step 4.

In this step, we will use the copy_table_dependents procedure to automatically copy all dependent objects, such as triggers, indexes, and

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