9i进入了DBMS_REDEFINITION,10g补充了COPY_TABLE_DEPENDENTS过程解决了约束的命名问题,非常方便了。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM USER_OBJECTS A;
Table created.
SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);
Table altered.
SQL> ALTER TABLE T ADD CHECK (ID > 0);
Table altered.
SQL> CREATE INDEX IND_T_NAME ON T (OBJECT_NAME);
Index created.
SQL> CREATE OR REPLACE TRIGGER TRI_T
2 BEFORE INSERT ON T
3 FOR EACH ROW
4 BEGIN
5 NULL;
6 END;
7 /
Trigger created.
SQL>
SQL> CREATE TABLE T_INTER
2 PARTITION BY HASH (ID)
3 PARTITIONS 4
4 AS SELECT ROWNUM ID, A.*
5 FROM USER_OBJECTS A
6 WHERE 1 = 2;
Table created.
SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_INTER')
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
25
SQL> select count(*) from t_inter;
COUNT(*)
----------
25
SQL> VAR V_NUM NUMBER
SQL> BEGIN
2 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER, 'T', 'T_INTER',
3 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, :V_NUM, TRUE);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> print v_num
V_NUM
----------
0
SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
T IND_T_NAME
T PK_T
T_INTER TMP$$_IND_T_NAME0
T_INTER TMP$$_PK_T0
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
T PK_T P
T SYS_C004042 C
T_INTER TMP$$_SYS_C0040420 C
T_INTER TMP$$_PK_T0 P
SQL> SELECT TABLE_NAME, TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME TRIGGER_NAME
------------------------------ ------------------------------
T_INTER TMP$$_TRI_T0
T TRI_T
SQL> exec dbms_redefinition.sync_interim_table('XYS','T','T_INTER');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_INTER')
PL/SQL procedure successfully completed.
SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
T IND_T_NAME
T PK_T
T_INTER TMP$$_IND_T_NAME0
T_INTER TMP$$_PK_T0
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
T SYS_C004042 C
T PK_T P
T_INTER TMP$$_PK_T0 P
T_INTER TMP$$_SYS_C0040420 C
SQL> SELECT TABLE_NAME, TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME IN ('T', 'T_INTER');
TABLE_NAME TRIGGER_NAME
------------------------------ ------------------------------
T TRI_T
T_INTER TMP$$_TRI_T0
SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T SYS_P21
T SYS_P22
T SYS_P23
T SYS_P24
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1813046/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1813046/