[오라클] 다른 데이터베이스로 impdp 사용해 데이터 밀어넣는 방법

orcl 데이터베이스의 hr4 유저에서 employees, departments 테이블을 expdp로 unload
orcl02 데이터베이스의 test01 스키마에게 impdp로 밀어넣기 

[oracle@enp0s3 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl02
The Oracle base remains unchanged with value /u01/app/oracle

-- 오라클에서 디렉토리는 논리 오브젝트이다. ORCL 데이터베이스에서 EXPORT시 사용했던 물리 디렉토리 경로를 ORCL2에서 새로운 논리 디렉토리로 생성해준다 

SQL> create directory hr4_imp_dir as '/u01/app/oracle/admin/orcl/dpdump/test_dir_1';

Directory created.

-- TEST01 유저에게 필요한 디렉토리 오브젝트 READ, WRITE권한 부여하고 확인 
-- IMPORT 받기 위한 디렉토리
-- TEST_DIR                            /u01/app/oracle/oradata/

SQL> grant read, write on directory test_dir to test01;

Grant succeeded.

-- EXPORT 시 사용한 디렉토리. IMPDP DUMPFILE 파라미터에 사용됨 

SQL> grant read,write on directory HR4_IMP_DIR to test01;

Grant succeeded.

SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs where grantee='TEST01';

-- 서로 테이블의 테이블스페이스가 다를 경우 impdp remap_tablespace 파라미터를 사용해 <old>:<new> 형식으로 지정해주어야 한다 
-- ORCL02 DB의 TEST01유저의 DEFAULT TABLESPACE 조회 (오라클에서 기본 지정하는 USERS말고 다른 곳으로 지정하는 것이 좋다) 

SQL> select username,default_tablespace from dba_users where username='TEST01';

USERNAME
--------------------------------------------------------------------------------
DEFAULT_TABLESPACE
------------------------------
TEST01
USERS

 

SQL> col DIRECTORY_NAME for a15
SQL> col DIRECTORY_PATH for a45
SQL> select directory_name, directory_path from all_directories where directory_name in ('TEST_DIR', 'HR4_IMP_DIR');

DIRECTORY_NAME  DIRECTORY_PATH
--------------- ---------------------------------------------
HR4_IMP_DIR     /u01/app/oracle/admin/orcl/dpdump/test_dir_1
TEST_DIR        /u01/app/oracle/oradata

---

-- PARFILE 생성 

[oracle@enp0s3 ~]$ vi imp_test01_emp_dept_20220217.par

 

directory=test_dir
dumpfile=hr4_imp_dir:exp_hr4_emp_dept_20220217_03.dmp
logfile=imp_test01_emp_dept_20220217_01.log
remap_schema=hr4:test01
remap_tablespace=hr4_tbs_01:users

 

[oracle@enp0s3 ~]$ impdp test01 parfile=imp_test01_emp_dept_20220217.par

Import: Release 19.0.0.0.0 - Production on Thu Feb 17 11:24: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
Master table "TEST01"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST01"."SYS_IMPORT_FULL_01":  test01/pw parfile=imp_test01_emp_dept_20220217.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST01"."EMPLOYEES"                        13.53 KB      55 rows
. . imported "TEST01"."DEPARTMENTS"                      7.125 KB      27 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST01"."SYS_IMPORT_FULL_01" successfully completed at Thu Feb 17 11:25:17 2022 elapsed 0 00:00:18

SQL> select * from test01.employees;
SQL> select * from test01.departments;