怎么在线对表进行分区（How to perform online redefinition）(二)
3 uname=>'RHYS',
4 ORIG_TABLE=>'AMY',
5 INT_TABLE=>'AMY_TEMP',
6 OPTIONS_FLAG=>(DBMS_REDEFINITION.CONS_USE_ROWID)
7 );
8 end ;
9 /
PL/SQL procedure successfully completed.
SQL> select * from dba_redefinition_objects;
OBJECT_TYPE OBJECT_OWNER OBJECT_NAME BASE_TABLE_OWNER BASE_TABLE_NAME INTERIM_OBJECT_OWNER INTERIM_OBJECT_NAME
------------ -------------------- ------------------------------ ------------------------------ ------------------------------ -------------------- ------------------------------
EDITION_NAME
------------------------------
TABLE RHYS AMY RHYS AMY RHYS AMY_TEMP
执行完后，查看相关信息：
SQL>
SQL> select count(*) from amy_temp;
COUNT(*)
----------
1032509
SQL> select count(*) from amy;
COUNT(*)
----------
1032509
SQL> select PARTITIONED from user_tables where table_name='AMY';
PAR
---
NO
SQL> SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='AMY_TEMP';
PAR
---
YES
SQL> SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AMY';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS PRIMARY_KEY_ID P AMY
SQL> SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AMY_TEMP';
no rows selected
SQL>
SQL> select table_name,column_name,data_type from user_tab_cols where table_name='AMY';
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------
AMY ID NUMBER
AMY CREATE_DATE TIMESTAMP(6)
SQL>
COL TABLE_NAME FOR A50
SQL> COL DATA_TYPE FOR A60
SQL> R
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY'
TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------------------------------------- ------------------------------ ------------------------------------------------------------
AMY ID NUMBER
AMY CREATE_DATE TIMESTAMP(6)
SQL> C /AMY/AMY_TEMP
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY_TEMP'
SQL> R
1* select table_name,column_name,data_type from user_tab_cols where table_name='AMY_TEMP'
TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------------------------------------- ------------------------------ ------------------------------------------------------------
AMY_TEMP ID NUMBER
AMY_TEMP CREATE_DATE TIMESTAMP(6)
AMY_TEMP M_ROW$$ VARCHAR2
SQL>
SQL> SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='AMY_TEMP';
no rows selected
SQL> SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME='AMY_TEMP';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_SNAP$_AMY_TEMP AMY_TEMP
SQL>
开始进行复制依赖对象。
SQL> var v_num number;
SQL> get p5.sql
1 begin
2 dbms_redefinition.copy_table_dependents(
3 uname=>'RHYS',
4 orig_table=>'AMY',
5 INT_TABLE=>'AMY_TEMP',
6 COPY_CONSTRAINTS=>TRUE,
7 copy_indexes=>(dbms_redefinition.cons_orig_params),
8 copy_privileges=>true,
9 copy_triggers=>true,
10 num_err