使用資料泵工具expdp工具匯出資料
本文中講述使用資料泵expdp工具匯出一個表中部分的資料，匯出一個表，匯出一個模式，
匯出一個表空間，還有匯出整個資料庫的操作過程。
---建立匯出匯入資料的目錄物件:
--建立目錄物件：
SQL> create or replace directory dump_home as '/home/oracle/dirhome';
Directory created.
--授權：
SQL> grant read,write on directory dump_home to scott;
Grant succeeded.
--建立測試表空間：
SQL> create tablespace myspace datafile
2 '/u01/app/oracle/oradata/PROD/myspace01.dbf' size 10M
3 autoextend on next 2M maxsize 30M;
Tablespace created.
SQL> !ls /u01/app/oracle/oradata/PROD/myspace01.dbf
/u01/app/oracle/oradata/PROD/myspace01.dbf
#已建立成功。
---利用資料泵匯出資料：
--用expdp匯出某個emp表中deptno=30的資料：
--匯出策略：expdp scott/tiger directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";
[oracle@enmo dirhome]$ expdp scott/tiger directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:52:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_home dumpfile=emp30.dmp tables=emp query='where deptno=30'
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.25 KB 6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dirhome/emp30.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 1 23:52:55 2016 elapsed 0 00:00:18
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 176
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
-rw-r--r-- 1 oracle oinstall 64 Nov 1 23:18 dt3.csv
-rw-r----- 1 oracle oinstall 139264 Nov 1 23:52 emp30.dmp
... ...
--匯出suxing使用者下的表mytest:
--匯出策略：expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
[oracle@enmo dirhome]$ expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:59:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_HOME is invalid
#遇到這種狀況說suxing使用者沒有使用目錄物件的許可權，則需要授權。
--授權給suxing使用者使用目錄物件：
SQL> grant read,write on directory dump_home to suxing;
Grant succeeded.
#繼續匯出表操作。
[oracle@enmo dirhome]$ expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:02:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SUXING"."SYS_EXPORT_TABLE_01": suxing/******** directory=dump_home dumpfile=mytest.dmp tables=mytest
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SUXING"."MYTEST" 5.859 KB 1 rows
Master table "SUXING"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SUXING.SYS_EXPORT_TABLE_01 is:
/home/oracle/dirhome/mytest.dmp
Job "SUXING"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 2 00:02:35 2016 elapsed 0 00:00:05
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 276
... ...
-rw-r--r-- 1 oracle oinstall 1852 Nov 1 22:54 load.log
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
[oracle@enmo dirhome]$
--匯出Scott schema所有的物件：
--匯出策略：expdp scott/tiger directory=dump_home dumpfile=scott.dmp schemas=scott
--或者策略：expdp scott/tiger directory=dump_home dumpfile=scott.dmp 與expdp scott/tiger directory=dump_home
[oracle@enmo dirhome]$ expdp scott/tiger directory=dump_home dumpfile=scott.dmp schemas=scott
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:10:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=dump_home dumpfile=scott.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."HISLOADER" 5.921 KB 3 rows
. . exported "SCOTT"."MYLOADER" 6.023 KB 7 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dirhome/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 2 00:10:56 2016 elapsed 0 00:00:35
[oracle@enmo dirhome]$
#匯出完成。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 560
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
--匯出MySpace表空間： （注意：匯出表空間，則要在系統管理員角色的賬戶操作）
--匯出策略：expdp system/oracle directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
[oracle@enmo dirhome]$ expdp system/oracle directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:21:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SUXING"."MYTEST" 5.859 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/dirhome/myspace.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Nov 2 00:21:26 2016 elapsed 0 00:00:05
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 664
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:21 myspace.dmp
-rw-r--r-- 1 oracle oinstall 1093 Nov 2 00:21 myspace.log
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
--匯出PROD資料庫全庫：
--匯出策略：expdp system/oracle directory=dump_home dumpfile=PROD.dmp logfile=PROD.log full=y
[oracle@enmo dirhome]$expdp system/oracle directory=dump_home dumpfile=PROD.dmp logfile=PROD.log full=y
... ...
... ...
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/dirhome/PROD.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Wed Nov 2 00:31:28 2016 elapsed 0 00:04:59
[oracle@enmo dirhome]$
#全庫匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 163028
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 165978112 Nov 2 00:31 PROD.dmp
-rw-r--r-- 1 oracle oinstall 107455 Nov 2 00:31 PROD.log
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
從上面可以發現，當匯出表空間與全庫時候，只有具有DBA角色許可權的賬戶才能做匯出操作，
同時要匯出一個log日誌檔案，當在匯入的時候使用。
匯出一個表空間，還有匯出整個資料庫的操作過程。
---建立匯出匯入資料的目錄物件:
--建立目錄物件：
SQL> create or replace directory dump_home as '/home/oracle/dirhome';
Directory created.
--授權：
SQL> grant read,write on directory dump_home to scott;
Grant succeeded.
--建立測試表空間：
SQL> create tablespace myspace datafile
2 '/u01/app/oracle/oradata/PROD/myspace01.dbf' size 10M
3 autoextend on next 2M maxsize 30M;
Tablespace created.
SQL> !ls /u01/app/oracle/oradata/PROD/myspace01.dbf
/u01/app/oracle/oradata/PROD/myspace01.dbf
#已建立成功。
---利用資料泵匯出資料：
--用expdp匯出某個emp表中deptno=30的資料：
--匯出策略：expdp scott/tiger directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";
[oracle@enmo dirhome]$ expdp scott/tiger directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:52:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_home dumpfile=emp30.dmp tables=emp query='where deptno=30'
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.25 KB 6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dirhome/emp30.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 1 23:52:55 2016 elapsed 0 00:00:18
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 176
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
-rw-r--r-- 1 oracle oinstall 64 Nov 1 23:18 dt3.csv
-rw-r----- 1 oracle oinstall 139264 Nov 1 23:52 emp30.dmp
... ...
--匯出suxing使用者下的表mytest:
--匯出策略：expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
[oracle@enmo dirhome]$ expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:59:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_HOME is invalid
#遇到這種狀況說suxing使用者沒有使用目錄物件的許可權，則需要授權。
--授權給suxing使用者使用目錄物件：
SQL> grant read,write on directory dump_home to suxing;
Grant succeeded.
#繼續匯出表操作。
[oracle@enmo dirhome]$ expdp suxing/oracle directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:02:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SUXING"."SYS_EXPORT_TABLE_01": suxing/******** directory=dump_home dumpfile=mytest.dmp tables=mytest
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SUXING"."MYTEST" 5.859 KB 1 rows
Master table "SUXING"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SUXING.SYS_EXPORT_TABLE_01 is:
/home/oracle/dirhome/mytest.dmp
Job "SUXING"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 2 00:02:35 2016 elapsed 0 00:00:05
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 276
... ...
-rw-r--r-- 1 oracle oinstall 1852 Nov 1 22:54 load.log
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
[oracle@enmo dirhome]$
--匯出Scott schema所有的物件：
--匯出策略：expdp scott/tiger directory=dump_home dumpfile=scott.dmp schemas=scott
--或者策略：expdp scott/tiger directory=dump_home dumpfile=scott.dmp 與expdp scott/tiger directory=dump_home
[oracle@enmo dirhome]$ expdp scott/tiger directory=dump_home dumpfile=scott.dmp schemas=scott
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:10:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=dump_home dumpfile=scott.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."HISLOADER" 5.921 KB 3 rows
. . exported "SCOTT"."MYLOADER" 6.023 KB 7 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dirhome/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 2 00:10:56 2016 elapsed 0 00:00:35
[oracle@enmo dirhome]$
#匯出完成。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 560
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
--匯出MySpace表空間： （注意：匯出表空間，則要在系統管理員角色的賬戶操作）
--匯出策略：expdp system/oracle directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
[oracle@enmo dirhome]$ expdp system/oracle directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:21:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SUXING"."MYTEST" 5.859 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/dirhome/myspace.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Nov 2 00:21:26 2016 elapsed 0 00:00:05
[oracle@enmo dirhome]$
#匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 664
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:21 myspace.dmp
-rw-r--r-- 1 oracle oinstall 1093 Nov 2 00:21 myspace.log
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
--匯出PROD資料庫全庫：
--匯出策略：expdp system/oracle directory=dump_home dumpfile=PROD.dmp logfile=PROD.log full=y
[oracle@enmo dirhome]$expdp system/oracle directory=dump_home dumpfile=PROD.dmp logfile=PROD.log full=y
... ...
... ...
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/dirhome/PROD.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Wed Nov 2 00:31:28 2016 elapsed 0 00:04:59
[oracle@enmo dirhome]$
#全庫匯出成功。
--檢視匯出檔案：
[oracle@enmo dirhome]$ ll
total 163028
-rw-r--r-- 1 oracle oinstall 80 Nov 1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall 95 Nov 1 22:39 dt2.csv
... ...
-rw-r----- 1 oracle oinstall 98304 Nov 2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 165978112 Nov 2 00:31 PROD.dmp
-rw-r--r-- 1 oracle oinstall 107455 Nov 2 00:31 PROD.log
-rw-r----- 1 oracle oinstall 286720 Nov 2 00:10 scott.dmp
[oracle@enmo dirhome]$
從上面可以發現，當匯出表空間與全庫時候，只有具有DBA角色許可權的賬戶才能做匯出操作，
同時要匯出一個log日誌檔案，當在匯入的時候使用。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2127481/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵 EXPDP 匯出工具的使用
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle expdp資料泵遠端匯出Oracle
- 資料泵IMPDP 匯入工具的使用
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- Oracle10g 資料泵匯出命令 expdp 使用總結Oracle
- 針對資料泵匯出 (expdp) 和匯入 (impdp)工具效能降低問題的檢查表
- 【資料泵】EXPDP匯出表結構(真實案例)
- EXPDP資料泵使用方法
- expdp 使用QUERY 匯出部分資料。
- 資料泵匯出匯入
- Oracle使用資料泵匯出匯入表Oracle
- shell，ant指令碼實現自動資料泵(exp.expdp)匯出匯入資料指令碼
- 使用資料泵匯出DDL語句
- 資料泵的匯入匯出
- 資料泵匯出匯入表
- 資料泵匯出匯入資料標準文件
- 使用資料泵impdp匯入資料
- 對比資料泵與原始匯入匯出工具（五）
- 對比資料泵與原始匯入匯出工具（四）
- 對比資料泵與原始匯入匯出工具（三）
- 對比資料泵與原始匯入匯出工具（八）
- 對比資料泵與原始匯入匯出工具（七）
- 對比資料泵與原始匯入匯出工具（六）
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- expdp遠端匯出資料
- 【expdp】10g資料泵expdp工具選項詳解及應用示例
- 詳說Oracle Vault——使用資料泵工具Oracle
- mongodb使用自帶命令工具匯出匯入資料MongoDB
- Oracle資料泵－schema匯入匯出Oracle
- expdp中使用include或者exclude匯出資料
- expdp impdp Data Pump（資料泵）使用解析
- EXPDP 和 IMPDP 資料泵的使用_1
- EXPDP 和 IMPDP 資料泵的使用_2
- 資料泵匯出資料包錯處理