 |
|
Oracle Tips by Burleson |
Oracle 10g Example of online redefinition of a table
privileges to the interim table. This procedure
is newly introduced in Oracle Database 10g to ease the redefinition
process.
SQL>Declare
2 blk_out pls_integer ;
3 BEGIN
4 blk_out := 0 ;
5 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('test1', 'dly_price','int_dly_price',1,
6 TRUE, TRUE, TRUE, FALSE, blk_out);
7 END;
8 /
PL/SQL
procedure successfully completed.
Step 5.
Now we finish the process with the
finish_redef_table procedure.
SYS>exec
DBMS_REDEFINITION.FINISH_REDEF_TABLE('test1', 'dly_price','int_dly_price')
;
PL/SQL
procedure successfully completed.
Now, the redefined table shows the structure
below:
SYS>desc
dly_price ;
Name Null? Type
---------------------- --------
--------------- TICKER VARCHAR2(6)
CLOSE_PX NUMBER
LOW_PX NUMBER
HIGH_PX NUMBER
DATE_PX DATE
Step 6.
Then we drop the interim table after ensuring
the desired results are achieved.
You can also query dba_redefinition_errors to
determine whether any errors occurred during the cloning of
dependent objects with the copy_table_dependents procedure. This
view shows the errors that occurred while cloning dependent objects
involved in ongoing redefinitions. This view is new in 10g.
In the next section, we will examine the new
features and improvements in maintaining the synonyms and views.
Get the complete Oracle10g story:
To get the code instantly, click here:
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
|
