--expdp/impdp浅析
1.建立目录对象 create or replace directory ext as '/home/oracle/exp';
ho mkdir /home/oracle/exp
grant read,write on directory ext to scott;
2.expdp
1)full: expdp \'/ as sysdba\' full=y directory=ext dumpfile=full.dmp
2)user: expdp scott/tiger directory=ext directory=ext dumpfile=scott.dmp
expdp system/oracle schemas=scott directory=ext dumpfile=scott.dmp
3)table: expdp scott/tiger tables=emp,dept directory=ext dumpfile=emp.dmp
4)rows: expdp scott/tiger tables=emp query=\'where deptno=10\' directory=ext dumpfile=emp10.dmp
参数content:
content=all --> DDL&DML
content=data_only --> DML
content=metadata_only -->DDL
3.impdp
1)full: impdp \'/ as sysdba\' directory=ext dumpfile=full.dmp full=y table_exists_action=append/skip/replace/truncate
2)user: impdp scott/tiger directory=ext dumpfile=scott.dmp table_exists_action=append/skip/replace/truncate
impdp system/oracle directory=ext dumpfile=scott.dmp remap_schema=scott:s1
不用导出的情况:impdp system/oracle network_link=PROD1 schemas=scott remap_schema=scott:s1
3)table: impdp scott/tiger directory=ext dumpfile=scott.dmp tables=emp table_exists_action=append/skip/replace/truncate
1.建立目录对象 create or replace directory ext as '/home/oracle/exp';
ho mkdir /home/oracle/exp
grant read,write on directory ext to scott;
2.expdp
1)full: expdp \'/ as sysdba\' full=y directory=ext dumpfile=full.dmp
2)user: expdp scott/tiger directory=ext directory=ext dumpfile=scott.dmp
expdp system/oracle schemas=scott directory=ext dumpfile=scott.dmp
3)table: expdp scott/tiger tables=emp,dept directory=ext dumpfile=emp.dmp
4)rows: expdp scott/tiger tables=emp query=\'where deptno=10\' directory=ext dumpfile=emp10.dmp
参数content:
content=all --> DDL&DML
content=data_only --> DML
content=metadata_only -->DDL
3.impdp
1)full: impdp \'/ as sysdba\' directory=ext dumpfile=full.dmp full=y table_exists_action=append/skip/replace/truncate
2)user: impdp scott/tiger directory=ext dumpfile=scott.dmp table_exists_action=append/skip/replace/truncate
impdp system/oracle directory=ext dumpfile=scott.dmp remap_schema=scott:s1
不用导出的情况:impdp system/oracle network_link=PROD1 schemas=scott remap_schema=scott:s1
3)table: impdp scott/tiger directory=ext dumpfile=scott.dmp tables=emp table_exists_action=append/skip/replace/truncate
--实验
1.先导出PROD1下的scott,再导入到PROD2为scott1
SYS@PROD1> create or replace directory ext as '/home/oracle/ext'; --在PROD1/PROD2分别创建目录
Directory created.
SYS@PROD2> create or replace directory ext as '/home/oracle/ext';
Directory created.
SYS@PROD1> ho mkdir /home/oracle/ext
SYS@PROD1> grant read,write on directory ext to scott; --授予读写目录权限
Grant succeeded.
[oracle@ocm1 ~]$ expdp system/oracle schemas=scott directory=ext dumpfile=scott.dmp --导出scott
Export: Release 11.2.0.3.0 - Production on Sat Dec 10 15:07:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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_SCHEMA_01": system/******** schemas=scott directory=ext dumpfile=scott.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.187 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEXT" 711.9 KB 2844 rows
. . exported "SCOTT"."DEMO" 5.015 KB 1 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."E1" 8.562 KB 14 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/ext/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:07:48
[oracle@ocm1 ~]$ impdp system/oracle@prod2 directory=ext dumpfile=scott.dmp remap_schema=scott:scott1
--导出到prod2的scott1中
Import: Release 11.2.0.3.0 - Production on Sat Dec 10 15:13:47 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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/********@prod2 directory=ext dumpfile=scott.dmp remap_schema=scott:scott1
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT1"."ALL_OBJECTS_UNLOAD" failed to create with error:
ORA-06564: object TMP does not exist
Failing sql is:
CREATE TABLE "SCOTT1"."ALL_OBJECTS_UNLOAD" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(30 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT1"."TEXT" 711.9 KB 2844 rows
. . imported "SCOTT1"."DEMO" 5.015 KB 1 rows
. . imported "SCOTT1"."DEPT" 5.929 KB 4 rows
. . imported "SCOTT1"."E1" 8.562 KB 14 rows
. . imported "SCOTT1"."EMP" 8.562 KB 14 rows
. . imported "SCOTT1"."SALGRADE" 5.859 KB 5 rows
. . imported "SCOTT1"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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/TRIGGER
ORA-39082: Object type TRIGGER:"SCOTT1"."DEMO_BIFER" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT1"."DEMO_BIFER" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SCOTT1"."ALL_OBJECTS_UNLOAD" creation failed
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 15:13:54
2.不通过导出,直接将prod1的scott导入到prod2的s1
SYS@prod2> create public database link p1 connect to scott identified by tiger using 'prod1';
Database link created.
[oracle@ocm1 ext]$ export ORACLE_SID=PROD2
[oracle@ocm1 ext]$ impdp \'/ as sysdba \' network_link=p1 schemas=scott remap_schema=scott:s1
Import: Release 11.2.0.3.0 - Production on Sat Dec 10 15:40:05 2016
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 "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" network_link=p1 schemas=scott remap_schema=scott:s1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.187 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"S1"."ALL_OBJECTS_UNLOAD" failed to create with error:
ORA-06564: object TMP does not exist
Failing sql is:
CREATE TABLE "S1"."ALL_OBJECTS_UNLOAD" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(30 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECO
. . imported "S1"."TEXT" 2844 rows
. . imported "S1"."DEMO" 1 rows
. . imported "S1"."DEPT" 4 rows
. . imported "S1"."E1" 14 rows
. . imported "S1"."EMP" 14 rows
. . imported "S1"."SALGRADE" 5 rows
. . imported "S1"."BONUS" 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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/TRIGGER
ORA-39082: Object type TRIGGER:"S1"."DEMO_BIFER" created with compilation warnings
ORA-39082: Object type TRIGGER:"S1"."DEMO_BIFER" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"S1"."ALL_OBJECTS_UNLOAD" creation failed
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at 15:40:33
[oracle@ocm1 ext]$ sqlplus s1/tiger@prod2
SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 10 15:41:28 2016
Copyright (c) 1982, 2011, Oracle. 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
S1@prod2> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEMO TABLE
DEPT TABLE
E1 TABLE
EMP TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
TEXT TABLE
8 rows selected.