怎么在线对表进行分区（How to perform online redefinition）(四)
ors=>:v_num,
11 copy_statistics=>true,
12 ignore_errors=>false,
13 copy_mvlog=>true);
14* end;
SQL> r
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_errors=>:v_num,
11 copy_statistics=>true,
12 ignore_errors=>false,
13 copy_mvlog=>true);
14* end;
PL/SQL procedure successfully completed.
SQL>
查看结果信息如下：
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> c /AMY/AMY_TEMP
1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='AMY_TEMP'
SQL> R
1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='AMY_TEMP'
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
RHYS TMP$$_PRIMARY_KEY_ID0 P AMY_TEMP
SQL> SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='AMY_TEMP';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ----------
RHYS TMP$$_PRIMARY_KEY_ID0 AMY_TEMP ID 1
SQL>
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
PRIMARY_KEY_ID NORMAL AMY
SQL> c /AMY/AMY_TEMP
1* select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP'
SQL> R
1* select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP'
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
I_SNAP$_AMY_TEMP NORMAL AMY_TEMP
TMP$$_PRIMARY_KEY_ID0 NORMAL AMY_TEMP
SQL>
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
执行finish_table完成表的重定义操作。
SQL> r
1 begin
2 dbms_redefinition.finish_redef_table(
3 uname=>'RHYS',
4 orig_table=>'AMY',
5 INT_TABLE=>'AMY_TEMP'
6 );
7* END;
PL/SQL procedure successfully completed.
SQL>
查看结果：
SQL> SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='AMY_TEMP';
PAR
---
NO
SQL> select PARTITIONED from user_tables where table_name='AMY';
PAR
---
YES
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY_TEMP';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- --------------------------------------------------
TMP$$_PRIMARY_KEY_ID0 NORMAL AMY_TEMP
SQL> select index_name,index_type,table_name from user_indexes where table_name='AMY';