1準備工作

SYS@ENMOEDU> create or replace directory d_home as '/home/oracle'; #建立目錄

SYS@ENMOEDU> grant read,write on directory d_home to hr; #把剛才建立的目錄的讀寫許可權賦給hr

Grant succeeded.

2.匯出表中某些行

[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=liuwenhe.dmp tables=employees query="'where employee_id=201'";

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:07:33 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=d_home dumpfile=liuwenhe.dmp tables=employees query='where employee_id=201'
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 9.328 KB 1 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/liuwenhe.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 22:08:02

[oracle@ENMOEDU ~]$ ls

liuwenhe.dmp export.log #沒有指定生成log,那麼會生成個預設的log，下次會覆蓋；

也可以： [oracle@ENMOEDU ~]$ vi liuwenhe

userid=hr/hr
directory=d_home
dumpfile=liuwenhe
logfile=liuwenhe.log
tables=employees
query='where employee_id=203'

[oracle@ENMOEDU ~]$ expdp parfile=liuwenhe 也可以成

2.匯出表；

[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=hezi.dmp tables=employees;

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:26:52 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=d_home dumpfile=hezi.dmp tables=employees
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/hezi.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 22:26:59

3.匯出schema #可以理解為是使用者所建立的所有資料物件

[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=hr.dump schemas=hr;

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:29:46 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=d_home dumpfile=hr.dump schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
. . exported "HR"."TEST" 5.867 KB 2 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr.dump
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:30:32

4.注意要使用DBA角色的使用者匯出，否則只匯出本schema的物件。

[oracle@ENMOEDU ~]$ expdp system/oracle directory=d_home dumpfile=hezi1.dmp logfile=hezi1.log tablespaces=system

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:47:17 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=d_home dumpfile=hezi1.dmp logfile=hezi1.log tablespaces=system
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.328 KB 2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.882 KB 28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.835 KB 19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.484 KB 3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.289 KB 2 rows
. . exported "OUTLN"."OL$" 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_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/hezi1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 22:47:37

5.匯入表前提是有匯出的表

[oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hezi.dmp remap_schema=hr:hr; #remap_schema=第一個hr是這個表原來屬於者，第二個hr是倒進去後屬於者，

Import: Release 11.2.0.3.0 - Production on Fri Aug 8 23:13:52 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=d_home dumpfile=hezi.dmp remap_schema=hr:hr
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 23:13:54

6.匯入schema

SYS@ENMOEDU> drop user hr cascade; #先把hr的schema 刪掉
User dropped.

SYS@ENMOEDU> create user hr identified by hr;
User created.
SYS@ENMOEDU> grant connect,resource to hr;
Grant succeeded.

oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hr.dump schemas=hr remap_schema=hr:hr #remap_schemas 這個引數可寫可不寫，它就是想指定後來匯入的使用者的名字，如果沒有指定，則會重新建立一個和原來匯出時的名字一樣的使用者，如果你有冒號後面的使用者，則它會報錯提示不能重建，

Import: Release 11.2.0.3.0 - Production on Sat Aug 9 11:53:12 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=d_home dumpfile=hr.dump schemas=hr remap_schema=hr:hr
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . imported "HR"."JOBS" 6.992 KB 19 rows
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows
. . imported "HR"."REGIONS" 5.476 KB 4 rows
. . imported "HR"."TEST" 5.867 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:53:25

7.匯入表空間。

[oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hezi1.dmp tablespaces=system;

expdp impdp Data Pump（資料泵）使用解析

相關文章