使用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 user01/password02 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 user01/password02 directory=expdp_dir1 dumpfile=$1 logfile=impdp_js_conf_bak_${today}.log \
TABLES=JS_CONF REMAP_TABLE=JS_CONF:JS_CONF_BAK
. . imported "ORCL"."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检查版本。
分类：数据库 | 标签： |