Its input parameters are the username and the table name:
execute DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','CUSTOMER');
The table is a candidate for online redefinition if the procedure returns the message
PL/SQL procedure successfully completed.
If it returns an error, the table cannot be redefined online, and the error message will give the
reason.
Next, create an interim table, in the same schema, with the desired attributes of the redefined
table. For example, we can partition the CUSTOMER table (to simplify this example, the tablespace
and storage clauses for the partitions are not shown):
create table CUSTOMER_INTERIM
(Name VARCHAR2(25) primary key,
?–
?–
?–
?–
?–
?–
?–
508 Oracle Database 11g DBA Handbook
Street VARCHAR2(50),
City VARCHAR2(25),
State CHAR(2),
Zip NUMBER)
partition by range (Name)
(partition PART1 values less than ('L'),
partition PART2 values less than (MAXVALUE))
;
You can now execute the START_REDEF_TABLE procedure of the DBMS_REDEFINITION
package to start the redefinition process. Its input variables are the schema owner, the table to be
redefined, the interim table name, and the column mapping (similar to the list of column names
in a select query). If no column mapping is supplied, all the column names and definitions in the
original table and the interim table must be the same.
execute DBMS_REDEFINITION.START_REDEF_TABLE -
('SCOTT','CUSTOMER','CUSTOMER_INTERIM');
Next, create any triggers, indexes, grants, or constraints required on the interim table.
Pages:
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768