Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

12.3. expdp/impdp

創建 dump 目錄與用戶

create directory mydump as '/u01/oracle';
		
grant read, write on directory mydump to dumpuser
grant read,write on directory backup to finance;		
		

12.3.1. expdp

導出用戶的數據

expdp dumpuser/password directory=mydump dumpfile=user.dmp
			

導出指定表數據

expdp dumpuser/password directory=mydump dumpfile=table.dmp tables=test1,test2
			

按用戶導

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=backup;			
			

並行進程進程數

expdp scott/tiger@orcl directory=backup dumpfile=scott3.dmp parallel=40 job_name=scott3			
			

指定查詢條件

expdp scott/tiger@orcl directory=backup dumpfile=expdp.dmp Tables=emp query='WHERE age=20';			
			

按表空間導

expdp system/manager DIRECTORY=backup DUMPFILE=tablespace.dmp TABLESPACES=temp,example;			
			

導整個資料庫

expdp system/manager DIRECTORY=backup DUMPFILE=full.dmp FULL=y;			
			

			

			
			

12.3.2. impdp

導入該用戶數據

impdp dumpuser/password directory=mydump dumpfile=user.dmp
			

導出表數據

impdp dumpuser/password directory=mydump dumpfile=table.dmp
			
$ impdp  \'/ as sysdba\' directory=backup dumpfile=user.dmp logfile=user.log tablespaces=dave,bl table_exists_action=replace;			
			

導到指定Schemas下

impdp scott/tiger DIRECTORY=backup DUMPFILE=expdp.dmp SCHEMAS=scott;
			

改變表的所有者

impdp system/manager DIRECTORY=backup DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
			

導入表空間

impdp system/manager DIRECTORY=backup DUMPFILE=tablespace.dmp TABLESPACES=example;
			

導入資料庫

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;			
			

追加數據

impdp system/manager DIRECTORY=backup DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION			
			

12.3.3. 數據泵演示

創建目錄

mkdir  /opt/oracle/backup			
			

創建用戶

create user backup identified by passw0rd;
GRANT resource,connect,CREATE SESSION,CREATE TABLE,SELECT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,INSERT ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE TO backup;


create directory backup as '/opt/oracle/backup';
grant read,write on directory backup to backup;		
			

例 12.1. expdp

				
$ expdp backup/passw0rd dumpfile=backup.dmp directory=backup

Export: Release 11.2.0.1.0 - Production on Tue Dec 1 16:19:56 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BACKUP"."SYS_EXPORT_SCHEMA_01":  backup/******** dumpfile=backup.dmp directory=backup 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/COMMENT
Master table "BACKUP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BACKUP.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/backup/backup.dmp
Job "BACKUP"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:20:28
				
				

運行完成後會在/opt/oracle/backup/目錄生成backup.dmp檔案


例 12.2. impdp

首先將dmp檔案複製到恢復目錄中,確認oracle用戶有權限訪問該檔案。

mv backup.dmp /opt/oracle/backup/
chown oracle:oinstall /opt/oracle/backup/backup.dmp
				

如果不知道目錄可以使用下面語句查看

				
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='BACKUP';

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
------------------------------
BACKUP
/opt/oracle/backup
				
				
				
$ impdp backup/passw0rd directory=backup dumpfile=backup.dmp

Import: Release 11.2.0.1.0 - Production on Tue Dec 1 16:32:34 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "BACKUP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BACKUP"."SYS_IMPORT_FULL_01":  backup/******** directory=backup dumpfile=backup.dmp 
Job "BACKUP"."SYS_IMPORT_FULL_01" successfully completed at 16:32:36
				
				

12.3.4. 查看dmp檔案的表空間

impdp \'/ as sysdba\' dumpfile=backup:expdp.dmp sqlfile=backup:expdp.sql

grep 'TABLESPACE' /opt/oracle/backup/expdp.sql | awk '{print $2}' | sort -u
TABLESPACE
"TS_DATA_DEF"
"TS_DATA_IDX"
UNLIMITED