首页 > 代码库 > 使用数据泵导入并重命名表名
使用数据泵导入并重命名表名
--查询数据泵使用的目录
SQL> select * from dba_directories;
--导出数据库使用的导出目录
EXPDP_DUMP
/u02/expdp_dump
--导出scott下表T_A的命令
expdp system/oracle directory=expdp_dump dumpfile=expdp_T_A.dmp logfile=expdp_T_A.log tables=scott.T_A
--导出操作记录
[oracle@test196 ~]$ expdp system/oracle directory=expdp_dump dumpfile=expdp_T_A.dmp logfile=expdp_T_A.log tables= scott.T_A
Export: Release 11.2.0.3.0 - Production on Thu May 4 09:30:13 2017
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=expdp_dump dumpfile=expdp_T_A.dmp logfile=expdp_T_A.log tables= scott.T_A
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 243 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"." T_A " 165.6 MB 12661 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u02/expdp_dump/expdp_T_A.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 09:31:00
[oracle@test196 ~]$ cd /u02/expdp_dump/
[oracle@test196 expdp_dump]$ ls -l|grep JOB
-rw-r----- 1 oracle oinstall 173830144 May 4 09:31 expdp_T_A.dmp
-rw-r--r-- 1 oracle oinstall 1283 May 4 09:31 expdp_T_A.log
[oracle@test196 expdp_dump]$
--导出文件传输到要导入的数据库
[oracle@test196 expdp_dump]$scp expdp_T_A.dmp oracle@192.168.160.191: /oracle/expdp/
The authenticity of host ‘192.168.160.191 (192.168.160.191)‘ can‘t be established.
RSA key fingerprint is 25:eb:f3:8d:a8:ce:02:67:d1:f7:2a:3c:99:c4:71:d0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.160.191‘ (RSA) to the list of known hosts.
oracle@192.168.160.191‘s password:
expdp_T_A.dmp 100% 166MB 11.1MB/s 00:15
[oracle@test196 expdp_dump]$
--查询数据泵使用的目录
SQL> select * from dba_directories;
导入使用的目录
192.168.160.191
DUMP
/oracle/expdp/
--导入scott下的命令（表名保持不变）
impdp system/oracle directory=dump dumpfile=expdp_T_A.dmp logfile= t_a.log TABLES=scott.T_A
--导入操作记录
[oracle@test01 ~]$ cd /oracle/expdp/
[oracle@test01 expdp]$ ls -lh|grep JOB
-rw-r----- 1 oracle oinstall 166M May 4 09:41 expdp_T_A.dmp
[oracle@test01 expdp]$
[oracle@test01 expdp]$ impdp system/oracle directory=dump dumpfile=expdp_T_A.dmp logfile= t_a.log TABLES=scott.T_A
Import: Release 12.2.0.1.0 - Production on Thu May 4 09:53:05 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=dump dumpfile=expdp_T_A.dmp logfile=t_a.log TABLES=scott.T_A
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T_A" 165.6 MB 12661 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Thu May 4 09:53:41 2017 elapsed 0 00:00:35
[oracle@test01 expdp]$
导入完成后可以在导入的数据库scott下查询到表T_A
--导入scott下的命令（表重命名）
impdp system/oracle directory=dump dumpfile=expdp_T_A.dmp logfile= t_a.log REMAP_TABLE=scott.T_A:EMPS
--导入操作记录
[oracle@test01 expdp]$ impdp system/oracle directory=dump dumpfile=expdp_T_A.dmp logfile= t_a.log REMAP_TABLE=scott.T_A:EMPS
Import: Release 12.2.0.1.0 - Production on Thu May 4 09:50:52 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump dumpfile=expdp_T_A.dmp logfile=t_case.log REMAP_TABLE=jobbackup.T_A:EMPS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JOBBACKUP"."EMPS" 165.6 MB 12661 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu May 4 09:51:34 2017 elapsed 0 00:00:37
导入完成后可以在导入的数据库scott下查询到表EMPS
使用数据泵导入并重命名表名