Data Pump

■ Data Pump 
1. 고속 데이터 / 메타 데이터 이동(export/import) 을 위한 유틸리티

-> DB의 데이터 및 메타 데이터를 효과적으로 이동하고 관리하는 유틸리티

-> DB의 백업, 복원, 데이터 이전, 데이터 로딩 등

2. dbms_datapump
3. 명령어 : expdp, impdp

 

---

#) mkdir 생성
[oracle1@oracle ~]$ pwd
/home/oracle1
[oracle1@oracle ~]$ mkdir data_pump
[oracle1@oracle ~]$ cd data_pump/
[oracle1@oracle data_pump]$ pwd
/home/oracle1/data_pump

#) 딕셔너리 생성

create directory pump_dir as '/home/oracle1/data_pump';
SELECT * FROM dba_directories WHERE directory_name = 'PUMP_DIR';

# 논리적 디렉터리를 읽고 쓰는 권한 부여

grant read, write on directory pump_dir to hr;

#) 권한 확인

select * from dba_tab_privs where grantee ='HR';

# 논리적 디렉터리 삭제

drop directory pump_dir;

# hr 유저의 특정 테이블 export
SQL> !
[oracle1@oracle ~]$ expdp system/oracle directory=PUMP_DIR dumpfile=hr_emp_dept_job.dmp tables=hr.employees,hr.departments,hr.job_history

# export한 물리적 위치 확인

[oracle@oracle ~]$ ls /home/oracle/data_pump/

#) hr.job_history 확인

select count(*) from hr.job_history;

# hr.job_history 삭제 

drop table hr.job_history purge;
select count(*) from hr.job_history;

#) hr.job_history 복구하기
SQL> !
[oracle1@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_jop.dmp tables=hr.job_history

#) import 잘되었는지 확인
[oracle1@oracle ~]$ exit

select count(*) from hr.job_history;

#) turncate 하기

truncate table hr.job_history;
select count(*) from hr.job_history;

#) import 하기, data만 넣기
SQL> !
[oracle1@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_jop.dmp tables=hr.job_history content=data_only

#) 확인하기
select count(*) from hr.job_history;

select count(*) from hr.job_history;

#) drop 하기

drop table hr.job_history purge;

#) 테이블만 생성
SQL> !
[oracle1@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=metadata_only

 

#) data는 없고, 뼈대만 생성

select count(*) from hr.job_history;

desc hr.job_history

#) data 넣기
SQL> !
[oracle1@oracle ~]$ impdp system/1234 directory=pump_dir dumpfile=hr_emp_dept_job.dmp tables=hr.job_history content=data_only

#) data 확인

select count(*) from hr.job_history;

#) hr 계정으로 확인

conn hr/hr
show user
select * from tab;

#) 휴지통 비우기

purge recyclebin;

 

select * from tab;

 

# sysdba 접속

conn / as sysdba
show user

# 스키마 레벨
[oracle1@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_schema.dmp schemas=hr exclude=table:\"\=\'EMP_DETAILS_VIEW\'\"

#) 여러 테이블을 작성했을 때
[oracle1@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_e_j_schema.dmp schemas=hr exclude=table:\"\in \(\'EMP_DETAILS_VIEW\',\'JOBS\'\)\"

#) include 하기!
[oracle1@oracle ~]$ expdp system/oracle schemas=hr directory=pump_dir dumpfile=hr_table.dmp include=table

#) user 생성 후 권한 부여 

create user henry identified by oracle default tablespace users quota 10m on users;
grant create session to henry;

 

select table_name, tablespace_name
from dba_tables
where owner = 'HR';

#) import
SQL> !
[oracle1@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp remap_schema='hr':'henry' remap_tablespace='EXAMPLE':'USERS'

#) henry 접속후 확인해보기

conn henry/oracle

select table_name, tablespace_name from user_tables;

#) data만 받아야 할때

select * from hr.employees where depratment_id = 50 and job_id ='ST_MAN';

=> 이 쿼리문을 export해야 한다면?
SQL> !
[oracle1@oracle ~]$ expdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp tables=hr.employees query=hr.employees:\"where department_id \= 50 and job_id \=\'ST_MAN\'\"

#) 샘플테이블 생성 

conn / as sysdba
show user

create table hr.emp_50_stman as select * from hr.employees where 1=2;
select * from hr.emp_50_stman;

#) data import 하기
SQL> !
[oracle1@oracle ~]$ impdp system/oracle directory=pump_dir dumpfile=hr_50_st_man.dmp remap_table=employees:emp_50_stman content=data_only

#) 확인해보기 

select count(*) from hr.emp_50_stman;

# ora11g 데이터베이스에 있는 henry 유저가 소유한 테이블을 xe henry 유저로 import 하기
[oracle1@oracle ~]$ sqlplus / as sysdba

select name from v$database;

select table_name, tablespace_name from dba_tables where owner = 'HENRY';

 

# XE henry 유저 생성
#) user 생성 후 권한 부여 

conn / as sysdba
show user

create user henry identified by oracle default tablespace users quota unlimited on users;
grant create session to henry;

# (cmd창) OS에서 import 하기
SQL> host
C:\Users\ITWILL>impdp system/oracle network_link=ora11g_link schemas=henry exclude=grant,trigger


#) 확인해보기

conn henry/oracle
show user

select * from tab;