CTAS[Create Table As Select .. ]  를 통한 테이블 복제시 제약 조건

CTAS 를 통한 테이블 복제시 제약 조건

[ LOCAL ]

SCOTT>create table check_ctas ( a1 number , a2 number , a3 varchar2(100) default 'TG',
     a4 date default sysdate, a5 number default 444 ) ;

Table created.

SCOTT>create index xak1_check_ctas on check_ctas ( a1, a2 ) ;
Index created.

SCOTT>create index xak2_check_ctas on check_ctas ( a3, a4 ) ;
Index created.

SCOTT>create unique index xpk_check_ctas on check_ctas ( a1, a2 ,a3 ) ;
Index created.

SCOTT>alter table check_ctas add constraint xpk_check_ctas primary key
   ( a1, a2, a3 ) ;
Table altered.

SCOTT>alter table check_ctas add ( a6 number not null );
Table altered.

SCOTT>alter table check_ctas add ( a7 number null ) ;
Table altered.

SCOTT>insert into check_ctas (a1,a2,a6,a7) values ( 1,2,3,4) ;

1 row created.

SCOTT>insert into check_ctas (a1,a2,a6,a7) values ( 5,6,7,8 ) ;

1 row created.

SCOTT>commit ;

Commit complete.

SCOTT>grant select, delete on check_ctas to sys ;

Grant succeeded.

SCOTT>create public synonym check_ctas for check_ctas ;

Synonym created.

SCOTT>create or replace trigger update_trigger
    after
    insert or update on check_ctas
    for each row
    declare
    tg_number number ;
    begin
    update check_ctas set a3 = 4444;
    dbms_output.put_line('TG 4444');
   end ;
   /

Trigger created.

SCOTT>select count(*) from CHECK_CTAS ;

  COUNT(*)
----------
         2

SCOTT> select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT
              from user_tab_columns
             where table_name ='CHECK_CTAS' ;

TABLE_NAME                     COLUMN_NAM DATA_TYPE            DATA_LENGTH N DATA_DEFAULT
------------------------------ ---------- -------------------- ----------- - --------------------
CHECK_CTAS                     A1         NUMBER                        22 N
CHECK_CTAS                     A2         NUMBER                        22 N
CHECK_CTAS                     A3         VARCHAR2                     100 N 'TG'
CHECK_CTAS                     A4         DATE                           7 Y sysdate
CHECK_CTAS                     A5         NUMBER                        22 Y 444
CHECK_CTAS                     A6         NUMBER                        22 N
CHECK_CTAS                     A7         NUMBER                        22 Y

7 rows selected.

SCOTT> select table_name, index_name from user_indexes where table_name ='CHECK_CTAS' ;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
CHECK_CTAS                     XAK1_CHECK_CTAS
CHECK_CTAS                     XAK2_CHECK_CTAS
CHECK_CTAS                     XPK_CHECK_CTAS

SCOTT>select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
            from user_constraints where table_name ='CHECK_CTAS';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
CHECK_CTAS                     XPK_CHECK_CTAS                 P
CHECK_CTAS                     SYS_C0015871                   C

SCOTT> select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
             from user_cons_columns where table_name ='CHECK_CTAS'

CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
XPK_CHECK_CTAS                 CHECK_CTAS                     A1
XPK_CHECK_CTAS                 CHECK_CTAS                     A2
XPK_CHECK_CTAS                 CHECK_CTAS                     A3
SYS_C0015871                   CHECK_CTAS                     A6

SCOTT>select TABLE_NAME, GRANTEE, PRIVILEGE
            from user_Tab_privs where table_name ='CHECK_CTAS';

TABLE_NAME                     GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
CHECK_CTAS                     SYS                            DELETE
CHECK_CTAS                     SYS                            SELECT

COTT> select OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
          from all_synonyms  where table_name ='CHECK_CTAS';

OWNER                SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
-------------------- ------------------------------ ------------------------------ ------------------------------
PUBLIC               CHECK_CTAS                     SCOTT                          CHECK_CTAS

SCOTT> select TRIGGER_NAME, TRIGGER_TYPE, STATUS
            from user_triggers where TABLE_NAME='CHECK_CTAS';

TRIGGER_NAME                   TRIGGER_TYPE     STATUS
------------------------------ ---------------- --------
UPDATE_TRIGGER                 AFTER EACH ROW   ENABLED

[ REMOTE ]

SCOTT> create table copy_check_ctas as select * from check_ctas@LOCAL ;

Table created.

-- 칼럼 갯수 체크
SCOTT>select count(*) from copy_check_ctas ;

  COUNT(*)
----------
         2

-- 테이블 칼럼 체크
SCOTT>select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT
           from user_tab_columns where table_name ='COPY_CHECK_CTAS';

TABLE_NAME                     COLUMN_NAM DATA_TYPE            DATA_LENGTH N DATA_DEFAULT
------------------------------ ---------- -------------------- ----------- - --------------------
COPY_CHECK_CTAS                A1         NUMBER                        22 N
COPY_CHECK_CTAS                A2         NUMBER                        22 N
COPY_CHECK_CTAS                A3         VARCHAR2                     100 N
COPY_CHECK_CTAS                A4         DATE                           7 Y
COPY_CHECK_CTAS                A5         NUMBER                        22 Y
COPY_CHECK_CTAS                A6         NUMBER                        22 N
COPY_CHECK_CTAS                A7         NUMBER                        22 Y

7 rows selected.

==> Table / Column Name, Type, Length 및 Nullable 은 Copy 되어지나,
    Column 의 Default 값은 Copy 되지 않는다.

-- 테이블의 인덱스 체크
SCOTT>select table_name, index_name from user_indexes
            where table_name ='COPY_CHECK_CTAS';

no rows selected
==> Index 는 Copy 되지 않는다.

-- Constraint 체크
SCOTT>select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
           from user_constraints where table_name ='COPY_CHECK_CTAS';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
[ Local ]
CHECK_CTAS                     XPK_CHECK_CTAS                 P
CHECK_CTAS                     SYS_C0015871                   C
[ Remote ]
COPY_CHECK_CTAS                SYS_C005644                    C
COPY_CHECK_CTAS                SYS_C005645                    C
COPY_CHECK_CTAS                SYS_C005646                    C
COPY_CHECK_CTAS                SYS_C005647                    C

==> Constraint 는 Copy 되지 않는다. [ NOT NULL 만 COPY 된다. ]

SCOTT>select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
           from user_cons_columns where table_name ='COPY_CHECK_CTAS';

CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAM
------------------------------ ------------------------------ ----------
SYS_C005644                    COPY_CHECK_CTAS                A1
SYS_C005645                    COPY_CHECK_CTAS                A2
SYS_C005646                    COPY_CHECK_CTAS                A3
SYS_C005647                    COPY_CHECK_CTAS                A6

==> 정확하게 이야기 하면 NOT NULL 조건은 COPY 된다.
    LOCAL  - NOT NULL, Primary Constraint(칼럼3개로 구성) 가
    REMOTE - NOT NULL, 칼럼 3개에 대한 NOT NULL 만 COPY 됨 [ Primary Constraint 는 Copy 되지 않는다. ]  

-- 테이블 권한 체크
SCOTT>select TABLE_NAME, GRANTEE, PRIVILEGE
            from user_Tab_privs where table_name ='COPY_CHECK_CTAS';

no rows selected
==> 권한은 Copy 되지 않는다.

-- 시노님 체크
SCOTT>select OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
           from all_synonyms  where table_name ='COPY_CHECK_CTAS';

no rows selected
==> Public Synonym 역시 Copy 되지 않는다.

-- 트리거 체크
SCOTT>select TRIGGER_NAME, TRIGGER_TYPE, STATUS
            from user_triggers where TABLE_NAME='COPY_CHECK_CTAS';
no rows selected
==> Trigger 역시 Copy 되지 않는다.