[DATAPUMP] SCHEMAS 옵션 사용시 public synonym도 이관여부?

배치로 주기적으로 돌아가는 datapump 작업이 있는데 작업결과를 보면 개발자분이 테이블이 생성된거 같지않다고 문의를 줄때가 있었습니다.

딕셔너리 뷰를 확인해보면 분명 생성이 되었는데, 안보인다고 하니 확인 결과 public synonym 문제였습니다.

해당 배치 datapump는 스키마 옵션을 사용해서 해당 스키마의 모든 오브젝트를 이관해주는 배치였지만,

public synonym 은 해당 스키마 내의 오브젝트가 아니기 때문에 이관되지 않았습니다.

(이관 후 추가로 create or replace 문으로 public synonym 문을 만들어 주는 배치가 하나 더 있었는데 그거 문제였습니다..)

즉, public synonym 은 object도 아니고 해당 스키마 소유도 아니기 때문에 포함 되지가 않는것이죠.

이관하려면 full=y옵션을 주고 include=PUBLIC_SYNONYM 옵션을 추가해주어야 합니다. 즉 full=y 옵션에서만 가능합니다. 스키마 옵션에서 사용하면 에러가 발생합니다.

그냥 시노님은 쿼리로 만드는게 편하네요 .오래걸리지도 않구요.

 

아래는 제가 테스트 했던 시나리오입니다. 간단합니다.

 

1.테이블과 publuc synonym 생성

SQL> create table emp_test2 as select * from emp;

SQL> create public synonym emp_test2 for ysbae.emp_test2;

 

2.expdp 로 ysbae 스키마 이관

$ expdp \'/ as sysdba\' schemas=YSBAE directory=TEST_DUMP dumpfile=ysbae.dmp logfile=ysbae.log

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=YSBAE directory=TEST_DUMP dumpfile=ysbae.dmp lo                                       gfile=ysbae.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "YSBAE"."DS_TEST2"                          44.77 MB  999999 rows
. . exported "YSBAE"."DS_TEST"                           38.13 MB  999999 rows
. . exported "YSBAE"."EMP_TEST"                            8.5 KB      42 rows
. . exported "YSBAE"."EMP"                               7.742 KB      14 rows
. . exported "YSBAE"."EMP_TEST2"                         7.742 KB      14 rows
. . exported "YSBAE"."AA"                                    0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /ora_work/test_dump/ysbae.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 15 10:32:44 2023 elapsed 0 00:01:25

 

3.테이블과 public synonym 삭제

SQL> drop table emp_test2;

SQL> drop public synonym emp_test2;

 

4.impdp 수행

$ impdp  \'/ as sysdba\' schemas=YSBAE  directory=TEST_DUMP dumpfile=ysbae.dmp logfile=ysbae_imp.log

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=YSBAE directory=TEST_DUMP dumpfile=ysbae.dmp logfile=ysbae_imp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"YSBAE" already exists

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
ORA-39151: Table "YSBAE"."AA" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "YSBAE"."DS_TEST2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "YSBAE"."EMP_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "YSBAE"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "YSBAE"."DS_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "YSBAE"."EMP_TEST2"                         7.742 KB      14 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"YSBAE"."YSBAE_VI_EMP2" already exists

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 7 error(s) at Mon Feb 13 17:20:49 2023 elapsed 0 00:00:11

 

4.확인

SQL> select owner,object_name,object_type from dba_objects where object_name='EMP_TEST2';

OWNER    OBJECT_NAME      OBJECT_TYPE             
------------------------- ----------------------- 
YSBAE    EMP_TEST2        TABLE

public synonym은 복구되지 않음.

 

+) 추가로 schema 옵션과 include=PUBLIC_SYNONYM옵션을 같이 쓰면 아래 에러 발생함

ORA-39038: Object path "PUBLIC_SYNONYM" is not supported for SCHEMA jobs.