78.3. expdp/impdp

78.3. expdp/impdp
上一頁	第 78 章 CLI	下一頁

創建 dump 目錄與用戶

create directory mydump as '/u01/oracle';

grant read, write on directory mydump to dumpuser
grant read,write on directory backup to finance;

78.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;

78.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

78.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;

例 78.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檔案

例 78.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

78.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

上一頁	上一級	下一頁
78.2. exp/imp	起始頁	78.4. RMAN