Data Pump(import & export)
data pump는 import와 export를 빠르게 할 수있는 장점이 있는 유틸리티다.
하지만, 버전이 맞지 않으면 사용이 안될 수 있는 단점이 있다.
물리적인 디렉토리 생성
[oracle@oracle19c ~]$ mkdir data_pump
[oracle@oracle19c data_pump]$ pwd
/home/oracle/data_pump
논리적인 디렉토리 생성
CREATE directory pump_dir as '/home/oracle/data_pump';
논리적 디렉터리에 대한 객체 권한 부여
GRANT read, write on directory pump_dir to hr;
DBA가 hr에게 부여한 권한 확인
SELECT grantee, owner, table_name, privilege, type FROM dba_tab_privs WHERE grantee='HR';
GRANTEE OWNER TABLE_NAME PRIVILEGE TYPE
---------- -------------------- --------------- -------------------- ------------------------
HR SYS DBMS_STATS EXECUTE PACKAGE
HR SYS PUMP_DIR READ DIRECTORY
HR SYS PUMP_DIR WRITE DIRECTORY
hr 계정의 모든 객체를 export
SYS@ORA19C>! expdp system/oracle schemas=hr directory=pump_dir dumpfile=hr_schema
## 실수로 drop table하기
DROP TABLE hr.employees cascade constraints purge;
tables 파라미터로 지웠던 table import하기
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_schema.dmp tables=hr.employees
복구완료
SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
##실수로 유저 삭제
DROP USER hr cascade;
schemas 파라미터에 값을 넣어 지웠던 유저 import
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_schema.dmp schemas=hr
> data_pump를 사용하면 유저에 대한 메타정보, 테이블 정보가 다 있어서 바로 복구 가능
유저 살아난 것 확인
SELECT * FROM dba_users WHERE username='HR';
# 특정한 유저의 테이블만 export
SYS@ORA19C>! expdp system/oracle schemas=hr directory=pump_dir dumpfile=hr_table.dmp schemas=hr include=table
> 이 테이블에 관련된 인덱스, 제약조건, 통계 다 받아짐
## 실수로 table의 모든 제약조건까지 전부 삭제
DROP TABLE hr.employees CASCADE CONSTRAINTS PURGE;
tables파라미터를 이용해서 지워진 table import
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp tables=hr.employees
테이블 살아난 것 확인
SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
만약에 테이블 값을 안 넣고 메타데이터만 넣고싶으면 content=metadata_only 옵션 사용
! impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp tables=hr.employees content=metadata_only
데이터는 안 들어가고 테이블만 생성
SYS@ORA19C>SELECT count(*) FROM hr.employees;
COUNT(*)
----------
0
## 실수로 TRUNCATE table하기
TRUNCATE TABLE hr.employees;
content=data_only 옵션을 통해서 데이터만 부어넣기
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp tables=hr.employees content=data_only
데이터 살아남
SELECT count(*) FROM hr.employees;
COUNT(*)
----------
107
hr유저가 갖고있는 모든 테이블들을 james 유저에게 모두 import해야 할 때가 있다.
소유자를 바꿔야 하고, hr이 갖고있는 테이블들은 example을 default로 사용하니까
james의 defualt의 테이블스페이스인 users로 바꿔줘야한다.
data pump는 소유자도 바꿀 수 있고, 테이블 스페이스도 바꿀 수 있다.
## hr유저가 갖고있는 모든 테이블을 다른 유저에게 import하기
새로운 유저 생성
CREATE USER jane identified by oracle default tablespace users temporary tablespace temp quota unlimited on users;
로그인 권한 주기
GRANT create session TO jane;
remap_schema 파라미터를 이용해서 새로운 유저에게 import
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp remap_schema='HR':'JAMES'
새로운 유저가 가진 table목록 확인하기
SELECT table_name, tablespace_name FROM dba_tables WHERE owner='JANE';
TABLE_NAME TABLESPACE_NAME
--------------- ------------------------------
COUNTRIES
EXCEPTIONS USERS
REGIONS USERS
LOCATIONS USERS
JOB_HISTORY USERS
DEPARTMENTS USERS
EMPLOYEES USERS
JOBS USERS
sql file 생성
SYS@ORA19C>! impdp system/oracle directory=pump_dir dumpfile=hr_table.dmp tables=hr.employees sqlfile=employees.sql
> dump안의 내용을 sql파일로 뽑아낼 때는 sqlfile 파라미터를 사용하면 돼
tablespace export받기
SYS@ORA19C>! expdp system/oracle directory=pump_dir dumpfile=example.dmp tablespaces=example
## tablespace 지우기
DROP TABLESPACE example INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINT;
tablespace 구조 생성하기
CREATE TABLESPACE example datafile '/u01/app/oracle/oradata/ORA19C/example01.dbf ' size 10M autoextend on;
> 테이블 스페이스를 생성하는 정보는 갖고있지 않아. export dump에는 테이블스페이스 안에 있는 객체들에 대한 메타정보만 있음. 그래서 import하기 전에 구조를 생성해야 해.
tablespace 내용 import하기
SYS@ORA19C> ! impdp system/oracle directory=pump_dir dumpfile=example.dmp tablespaces=example
테이블 생성 확인
SELECT a.file#, b.name tbs_name, a.name file_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
12:01:34 3 WHERE a.ts# = b.ts#;
FILE# TBS_NAME FILE_NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ --------------------------------------------- ---------- ------------------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 4200524
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 4200524
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 4200524
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 4200524
5 EXAMPLE /u01/app/oracle/oradata/ORA19C/example01.dbf ONLINE 4308251
2 TEST /u01/app/oracle/oradata/ORA19C/test_tbs.dbf ONLINE 4239127