본문 바로가기
DBMS/ORACLE

[oracle] data pump 작업 시 trigger 주의

by 드바 2024. 4. 2.

DBMS: oracle 19.3

 

- 테이블 및 트리거 생성된 상태에서 data pump 사용한 import 작업 시 트리거 작동한다
- 작업 전 트리거 비활성 후 작업 진행 등 검토해야함
- TABLE_EXISTS_ACTION 옵션 
 기본값: SKIP
 CONTENT=DATA_ONLY 지정 시 기본값: APPEND

 

[목차여기]

 

테스트 오브젝트 생성

tab1 테이블에 IDU 발생 시 tri_tab1 작동하여 tab1_log 테이블에 데이터 insert

SQL> CREATE TABLE SK.TAB1 (C1 number(10)) ;

Table created.

SQL> CREATE TABLE SK.TAB1_LOG (DT DATE, CMD VARCHAR(10), C1 number(10)) ;

Table created.

SQL> CREATE OR REPLACE TRIGGER SK.TRI_TAB1
AFTER 
INSERT OR UPDATE OR DELETE ON SK.TAB1
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO SK.TAB1_LOG VALUES (SYSDATE,'INSERT',:NEW.C1) ;
ELSIF UPDATING THEN
INSERT INTO SK.TAB1_LOG VALUES (SYSDATE,'UPDATE',:OLD.C1);
ELSIF DELETING THEN
INSERT INTO SK.TAB1_LOG VALUES (SYSDATE,'DELETE',:OLD.C1);
END IF;
END;
/

Trigger created.


SQL> SELECT trigger_name, trigger_type, table_name, status 
FROM DBA_TRIGGERS 
WHERE owner = 'SK'
;
TRIGGER_NAME		       TRIGGER_TYPE	TABLE_NAME		       STATUS
------------------------------ ---------------- ------------------------------ --------
TRI_TAB1		       AFTER EACH ROW	TAB1			       ENABLED

SQL> SELECT object_name, object_type, status 
FROM DBA_objects
WHERE owner = 'SK'
;

OBJECT_NAME		       OBJECT_TYPE	       STATUS
------------------------------ ----------------------- -------
TRI_TAB1		       TRIGGER		       VALID
TAB1			       TABLE		       VALID
TAB1_LOG		       TABLE		       VALID

-- tab1 테이블에 1000건 insert 하면 tab1_log 테이블에도 1000건 insert 됨
SQL> INSERT INTO sk.tab1 SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000
;

1000 rows created.

SQL> commit ;

Commit complete.

-- 데이터 확인
SQL> SELECT count(*) FROM sk.tab1 ;

  COUNT(*)
----------
      1000

SQL> SELECT count(*) FROM sk.tab1_log ;

  COUNT(*)
----------
      1000

-- export
[oracle@svr1 pump]$ expdp system/oracle directory=EXP_DIR dumpfile=sk.dmp schemas=sk

Export: Release 19.0.0.0.0 - Production on Tue Apr 2 21:40:57 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=EXP_DIR dumpfile=sk.dmp schemas=sk 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SK"."TAB1"                                 12.79 KB    1000 rows
. . exported "SK"."TAB1_LOG"                             28.28 KB    1000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/pump/sk.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 2 21:41:14 2024 elapsed 0 00:00:16

 

반응형

import 테스트

tab1 테이블 table_exists_action=truncate

truncate 옵션 사용하여 tab1 테이블 import 수행 시 트리거 작동하여 tab1_log 테이블 데이터가 2000건으로 됩니다

[oracle@svr1 pump]$ impdp system/oracle directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1 table_exists_action=TRUNCATE

Import: Release 19.0.0.0.0 - Production on Tue Apr 2 21:45:54 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1 table_exists_action=TRUNCATE 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SK"."TAB1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB1"                                 12.79 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 2 21:45:58 2024 elapsed 0 00:00:03

[oracle@svr1 pump]$ 


SQL> SELECT count(*) FROM sk.tab1 ;

  COUNT(*)
----------
      1000

SQL> SELECT count(*) FROM sk.tab1_log ;

  COUNT(*)
----------
      2000

SQL>

 

tab1 테이블 table_exists_action=replace

replace 옵션 사용하여 tab1 테이블 import 수행 시 트리거도 재생성기 때문에 tab1_log 데이터는 변함없습니다

[oracle@svr1 pump]$ impdp system/oracle directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1 table_exists_action=replace

Import: Release 19.0.0.0.0 - Production on Tue Apr 2 21:47:06 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1 table_exists_action=replace 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB1"                                 12.79 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 2 21:47:15 2024 elapsed 0 00:00:08


SQL> SELECT count(*) FROM sk.tab1 ;

  COUNT(*)
----------
      1000

SQL> SELECT count(*) FROM sk.tab1_log ;

  COUNT(*)
----------
      2000

SQL>

 

tab1, tab1_log 테이블 table_exists_action=truncate

tab1, tab1_log 테이블 모두 truncate 옵션 사용하여 import 수행 시 트리거 작동하여 tab1_log 테이블 2000건 입니다

[oracle@svr1 pump]$ impdp system/oracle directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1,sk.tab1_log table_exists_action=TRUNCATE

Import: Release 19.0.0.0.0 - Production on Tue Apr 2 21:48:57 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1,sk.tab1_log table_exists_action=TRUNCATE 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "SK"."TAB1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SK"."TAB1_LOG" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB1"                                 12.79 KB    1000 rows
. . imported "SK"."TAB1_LOG"                             28.28 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 2 21:49:00 2024 elapsed 0 00:00:03

[oracle@svr1 pump]$ 



SQL> SELECT count(*) FROM sk.tab1 ;

  COUNT(*)
----------
      1000

SQL> SELECT count(*) FROM sk.tab1_log ;

  COUNT(*)
----------
      2000

 

tab1, tab1_log 테이블 content=data_only table_exists_action=truncate

data_only 사용 시에도 트리거 작동함

[oracle@svr1 pump]$ impdp system/oracle directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1,sk.tab1_log content=data_only table_exists_action=TRUNCATE

Import: Release 19.0.0.0.0 - Production on Tue Apr 2 21:51:50 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=EXP_DIR dumpfile=sk.dmp tables=sk.tab1,sk.tab1_log content=data_only table_exists_action=TRUNCATE 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB1"                                 12.79 KB    1000 rows
. . imported "SK"."TAB1_LOG"                             28.28 KB    1000 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 2 21:51:52 2024 elapsed 0 00:00:02

[oracle@svr1 pump]$ 

SQL> SELECT count(*) FROM sk.tab1 ;

  COUNT(*)
----------
      1000

SQL> SELECT count(*) FROM sk.tab1_log ;

  COUNT(*)
----------
      2000

SQL> SELECT trigger_name, trigger_type, table_name, status 
FROM DBA_TRIGGERS 
WHERE owner = 'SK'
;

TRIGGER_NAME		       TRIGGER_TYPE	TABLE_NAME		       STATUS
------------------------------ ---------------- ------------------------------ --------
TRI_TAB1		       AFTER EACH ROW	TAB1			       ENABLED

SQL> SELECT object_name, object_type, status 
FROM DBA_objects
WHERE owner = 'SK'
;

OBJECT_NAME		       OBJECT_TYPE	       STATUS
------------------------------ ----------------------- -------
TRI_TAB1		       TRIGGER		       VALID
TAB1_LOG		       TABLE		       VALID
TAB1			       TABLE		       VALID

'DBMS > ORACLE' 카테고리의 다른 글

[oracle] windows 오라클 설치 시 setup.exe 무반응 해결방법  (0) 2024.05.07
[oracle]alter user ... replace 구문 (ORA-28221)  (0) 2024.04.30
[oracle] index unusable DML 테스트  (0) 2024.03.17
[oracle] 아카이브 진행 중인 로그 삭제 시 에러, ora-16014, ora-19508, ora-16038  (0) 2024.03.15
[oracle] 스케줄러(dbms_scheduler) 실행 시간대(time zone)  (0) 2024.02.22

관련글

댓글

티스토리툴바