 |
|
Oracle Tips by Burleson |
Oracle 10g New Additions and Changes
In Oracle9i, there were many restrictions to
performing redefinition. It was even necessary to create dependent
objects, such as constraints and indexes, manually. Oracle Database
10g introduces many enhancements to facilitate easier administration
and to minimize manual tasks. They are discussed next.
Support for additional data types, such as CLOB
and BLOB has been introduced. Tables containing character LOBS and
binary LOBS can now be redefined online. However, tables with BFILE
columns cannot be redefined online.
Tables that are involved in master-master
replication can undergo redefinition. However, there is a
restriction that no horizontal or vertical sub-setting, or column
transformations, are allowed.
Three new procedures are added to the package
dbms_redefinition. They are as follows:
-
copy_table_dependents – clones the
dependent objects of the table being redefined.
-
register_dependent_object –
registers a dependent object on the table being redefined and on
the interim table
-
unregister_dependent_object –
un-registers a dependent object on the table being redefined and
on the interim table
The following constants are also introduced in
specifying the type of the dependent object.
-
cons_constraint - specifies the
dependent object type CONSTRAINT.
-
cons_index - specifies the
dependent object type is of type Index.
-
cons_trigger - specifies the
dependent object type is of TRIGGER
-
copy_orig_params - specifies that
the indexes on the original table be copied using the storage
parameters of the original.
A new optional parameter, orderby_cols, has
been introduced for the start_redef_table procedure. You can use
this parameter to specify the columns that should be used to order
the rows during the initial instantiation of the interim table. In
addition, you can also use the optional key word ASCENDING or
DESCENDING.
The remaining restrictions are shown below:
-
The table to be redefined cannot be part of a
cluster.
-
Tables in the SYS and SYSTEM schemas cannot be
online redefined.
-
Temporary tables cannot be redefined.
-
Tables with BFILE columns cannot be online
redefined.
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
|
