[오라클] parfile 파라미터 사용 실습 예제

par파일 사용해서 export, import 실습

 

expdp, impdp 명령 시 PARFILE 파라미터는 현재 유저의 경로를 나타낸다. 아래와 같은경우 ~(/home/oracle)이 파라미터 파일의 실제 경로가 된다. 

-- parfile 내용 파일명 expdp_hr_20220216.par
schemas=hr4
dumpfile=exp_hr_schema_20220216_02.dmp
directory=dpump_dir1
exclude=user:"='HR4'"
logfile=exp_hr_schema_20220216_02.log

[oracle@enp0s3 ~]$ expdp hr4 parfile=expdp_hr_20220216.par

Export: Release 19.0.0.0.0 - Production on Wed Feb 16 14:14:55 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HR4"."SYS_EXPORT_SCHEMA_01":  hr4/pw parfile=expdp_hr_20220216.par
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/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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "HR4"."EMPLOYEES"                           17.09 KB     107 rows
. . exported "HR4"."LOCATIONS"                           8.437 KB      23 rows
. . exported "HR4"."JOB_HISTORY"                         7.195 KB      10 rows
. . exported "HR4"."JOBS"                                7.109 KB      19 rows
. . exported "HR4"."DEPARTMENTS"                         7.125 KB      27 rows
. . exported "HR4"."COUNTRIES"                           6.375 KB      25 rows
. . exported "HR4"."REGIONS"                             5.546 KB       4 rows
Master table "HR4"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR4.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl/dpdump/test_dir_1/exp_hr_schema_20220216_02.dmp
Job "HR4"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 16 14:15:57 2022 elapsed 0 00:00:59


-- impdp 
-- parfile 파일명 impdp_hr_20220216.par 내용
directory=dpump_dir2
dumpfile=dpump_dir1:exp_hr_schema_20220216_02.dmp
remap_schema=hr4:hr5
logfile=imp_hr_schema_20220216_02.log
table_exists_action=skip

-- impdp 커맨드 
[oracle@enp0s3 ~]$ impdp hr5 parfile=impdp_hr_20220216.par

Import: Release 19.0.0.0.0 - Production on Wed Feb 16 14:23:59 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR5"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR5"."SYS_IMPORT_FULL_01":  hr5/pw parfile=impdp_hr_20220216.par
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
Table "HR5"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."LOCATIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."JOBS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."JOB_HISTORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Table "HR5"."REGIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "HR5"."SYS_IMPORT_FULL_01" successfully completed at Wed Feb 16 14:24:06 2022 elapsed 0 00:00:03