执行导入前,先导出scott的schema作为测试数据:
[oracle@localhost ~]$ expdp scott/oracle directory=expdp_dir dumpfile=scott_schema.dmp logfile=scott_schema.log
IMPDP的CONTENT参数:
CONNECT参数用来控制数据的导入方式,共有三个值:
ALL : 导入所有的对象和数据,默认的就是ALL
DATA_ONLE : 只导入数据
METEDATA_ONLY:只导入对象定义
REMAP_SCHEMA参数用来重定义所属的schema,可以为多个shema对象重定义:
REMAP_SCHEMA=A:X,B:Y
REMAP_TABLESPACE参数用来重定义所属的表空间,语法格式与REMAP_SCHEMA的相似
REMAP_TABLESPACE=M:X,N:Y
将scott用户导出的模式导入到xtt用户下,并先导入对象定义,再导入数据,重定义表空间到EXP_TEST
导入对象定义:
[oracle@localhost ~]$ impdp xtt/oracle directory=expdp_dir dumpfile=scott_schema.dmp nologfile=y content=metadata_only remap_schema=scott:xtt remap_tablespace=users:exp_test
.
.
.
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
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "XTT"."SYS_IMPORT_FULL_01" successfully completed at 08:06:11
查看一下数据对象:
[oracle@localhost ~]$ sqlplus xtt/oracle
查看表空间:
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT EXP_TEST
EMP EXP_TEST
BONUS EXP_TEST
SALGRADE EXP_TEST
EXPDP_TEST EXP_TEST
SYS_EXPORT_TABLE_01 EXP_TEST
SYS_EXPORT_TABLE_02 EXP_TEST
SYS_EXPORT_TABLE_03 EXP_TEST
查看一下数据:
SQL> select count(*) from dept;
COUNT(*)
----------
0
只导入数据:
[oracle@localhost ~]$ impdp xtt/oracle directory=expdp_dir dumpfile=scott_schema.d
mp nologfile=y content=data_only remap_schema=scott:xtt
.
.
.
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "XTT"."EXPDP_TEST" 138.2 MB 3670016 rows
. . imported "XTT"."SYS_EXPORT_TABLE_01" 58.32 KB 251 rows
. . imported "XTT"."SYS_EXPORT_TABLE_02" 50.53 KB 251 rows
. . imported "XTT"."SYS_EXPORT_TABLE_03" 51.10 KB 253 rows
. . imported "XTT"."DEPT" 5.656 KB 4 rows
. . imported "XTT"."EMP" 7.820 KB 14 rows
. . imported "XTT"."SALGRADE" 5.585 KB 5 rows
. . imported "XTT"."BONUS" 0 KB 0 rows
Job "XTT"."SYS_IMPORT_FULL_01" successfully completed at 08:11:11
再次查看数据:
[oracle@localhost ~]$ sqlplus xtt/oracle
SQL> select count(*) from dept;
COUNT(*)
----------
4
TABLE_EXISTS_ACTION参数可以控制导入数据时对已经存在的表对象的操作:
SKIP:表示跳过该表,继续下一个对象的处理,该参数如果在content=data_only模式下无效,会自动置为append
APPEND:向现有表中添加数据
TRUNCATE:truncate当前表,然后向该表中添加数据
REPLACE:删除该表并重建对象,然后向该表中添加数据
继续前面的导入,如果表存在的话只需truncate操作后再添加数据:
[oracle@localhost ~]$ impdp xtt/oracle directory=expdp_dir dumpfile=scott_schema.d
mp nologfile=y remap_schema=scott:xtt table_exists_action=truncate
.
.
ORA-39153: Table "XTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate.
.
.
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "XTT"."EXPDP_TEST" 138.2 MB 3670016 rows
. . imported "XTT"."SYS_EXPORT_TABLE_01" 58.32 KB 251 rows
. . imported "XTT"."SYS_EXPORT_TABLE_02" 50.53 KB 251 rows
. . imported "XTT"."SYS_EXPORT_TABLE_03" 51.10 KB 253 rows
. . imported "XTT"."EMP" 7.820 KB 14 rows
. . imported "XTT"."SALGRADE" 5.585 KB 5 rows
. . imported "XTT"."BONUS" 0 KB 0 rows
network_link可以控制远程将数据导入到本地数据库:
SQL> create public database link expdp_link connect to scott identified by oracle using 'expdp_test';
Database link created.
需要授予本地用户imp_full_database权限:
SQL> grant imp_full_database to scott;
Grant succeeded.
将远端的scott模式利用expdp_link连接导入到本地的xtt用户下:
[oracle@localhost ~]$ impdp scott/oracle network_link=expdp_link2 nologfile=y remap_schema=scott:xtt
.
.
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
.
.
. . imported "XTT"."DG_TEST" 17 rows
. . imported "XTT"."TEST" 8 rows
. . imported "XTT"."STD_TABLE" 0 rows
. . imported "XTT"."STD_TABLE_1" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
.
.
.
SQL> select count(*) from dg_test;
COUNT(*)
----------
17
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1132936/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-1132936/