使用資料泵工具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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle expdp資料泵遠端匯出Oracle
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- 【資料泵】EXPDP匯出表結構(真實案例)
- 資料泵匯出匯入
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- 使用impdp,expdp資料泵進入海量資料遷移
- mongodb使用自帶命令工具匯出匯入資料MongoDB
- Oracle資料泵的匯入和匯出Oracle
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- 細緻入微：如何使用資料泵匯出表的部分列資料
- oracle按照表條件expdp匯出資料Oracle
- 資料泵匯出匯入物化檢視（ORA-39083）
- 資料泵expdp匯出遇到ORA-01555和ORA-22924問題的分析和處理
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- 使用Exp和Expdp匯出資料的效能對比與最佳化
- expdp在匯出時對資料大小進行評估
- 【ASK_ORACLE】重灌Oracle資料泵（Datapump）工具的方法Oracle
- 資料泵datapump（expdp/impdp）的parfile用法，避免跳脫字元字元
- 通過dblink，資料泵expdp遠端跨版本導庫
- 小景的Dba之路--如何匯出0記錄表以及資料泵的使用
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- 資料泵重建使用者
- [重慶思莊每日技術分享]-資料泵匯出資料包39006是什麼原因
- 淺析圖資料庫 Nebula Graph 資料匯入工具——Spark Writer資料庫Spark
- 達夢資料庫dexp邏輯匯出工具使用介紹資料庫
- 運維中資料泵匯出常用的細節小功能兩例運維
- mysql匯出資料MySql
- GeoRapter工具將shapefile資料匯入到Oracle空間資料庫中APTOracle資料庫
- sqoop資料匯入匯出OOP
- Oracle 資料匯入匯出Oracle
- Oracle資料匯入匯出Oracle
- phpMyAdmin匯入/匯出資料PHP
- Dcat admin使用Laravel Excel匯出資料LaravelExcel
- 資料庫升級之-資料泵資料庫
- 殺停資料泵
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- 分享運維中資料泵匯出常用的細節小功能兩例運維