DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS的一點測試

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>