使用impdp数据泵导入并重命名表名

2018-08-28

使用数据泵导入并重命名表名

1、select * from dba_directories;

2、expdp system/oracle directory=expdp_dump dumpfile=expdp_T_A.dmp logfile=expdp_T_A.log tables=scott.T_A

3、impdp system/oracle directory=dump dumpfile=expdp_T_A.dmp logfile= t_a.log REMAP_TABLE=scott.T_A:EMPS

impdp talk99_core/looyu2talk99 directory=expdp_dir1 dumpfile=$1 logfile=impdp_js_conf_bak_${today}.log \
INCLUDE=TABLE:"IN('JS_CONF')" REMAP_TABLE=JS_CONF:JS_CONF_BAK

4、remap_schema=old_schema_name:new_schema_name
5、remap_tablespace=users:test_tbsp
6、remap_table= A1:B1, A2:B1
7、impdp  指定表并改名

tables=scott.a REMAP_TABLE=scott.a:b 

impdp talk99_core/looyu2talk99 directory=expdp_dir1 dumpfile=$1 logfile=impdp_js_conf_bak_${today}.log \
TABLES=JS_CONF REMAP_TABLE=JS_CONF:JS_CONF_BAK

. . imported "TALK99_CORE"."JS_CONF_BAK"                 52.75 MB   77114 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"TALK99_CORE"."JS_CONF_INDEX" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"TALK99_CORE"."JS_CONF_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"TALK99_CORE"."JS_CONF_INDEX" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TALK99_CORE"."SYS_IMPORT_TABLE_01" completed with 3 error(s) at Tue Aug 28 11:30:53 2018 elapsed 0 00:01:16

8、实例:
select * from dba_directories;
create or replace directory expdp_dir1 as '/data1/backup';
grant read,write on directory expdp_dir1 to user01;
grant read,write on directory expdp_dir1 to public;
expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1
expdp tables=order dumpfile=contact.dmp DIRECTORY=expdp_dir1 parallel=2 log=act.log COMPRESSION=all
注意在ORACLE 10g下 COMPRESSION只有METADATA_ONLY和NONE两个选项,ORACLE 11g下才有DATA_ONLY选项。
expdp version检查版本。

 

分类:数据库 | 标签: |

相关日志

评论被关闭!