impdp导入重命名表问题请教！

ylw6006

场景如下：
1：是要expdp导出数据库1某个用户的一些表；
2：将导出的dump文件，导入数据库2，两个数据库名的用户名一致；
3：数据库2已经存在步骤1中导出的这些表
4：希望impdp导入实现rename表，（非append操作）例如数据库1表名为emp，对应数据库2表面emp_temp

求解答！

Maclean Liu(刘相兵

11g 可以直接使用REMAP_TABLE:

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.


REMAP_TABLEDefault: There is no default

Purpose
Allows you to rename tables during an import operation.

Syntax and Description
You can use either of the following syntaxes (see the Usage Notes below):
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablenameOR
REMAP_TABLE=[schema.]old_tablename[:partition]:new_tablenameYou can use the
REMAP_TABLE
parameter to rename entire tables or to rename table partitions if the table is being departitioned. (See
"PARTITION_OPTIONS".)
You can also use it to override the automatic naming of table partitions that were exported.

Usage Notes
Be aware that with the first syntax, if you specify
REMAP_TABLE=A.B:C, Import assumes that
A
is a schema name,
B
is the old table name, and
C
is the new table name. To use the first syntax to rename a partition that is being promoted to a nonpartitioned table, you must specify a schema name.
To use the second syntax to rename a partition being promoted to a nonpartitioned table, you only need to qualify it with the old table name. No schema name is required.

Restrictions

• Only objects created by the Import will be remapped. In particular, preexisting tables will not be remapped.
• The
  REMAP_TABLE
  parameter will not work if the table being remapped has named constraints in the same schema and the constraints need to be created when the table is created.

Example
The following is an example of using the
REMAP_TABLE
parameter to rename the
employees
table to a new name of
emps:
> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmpTABLES=hr.employees REMAP_TABLE=hr.employees:emps


[oracle@vrh2 ~]$ expdp userid=maclean/maclean directory=tmp dumpfile=mac.dmp tables=mac                     

Export: Release 11.2.0.3.0 - Production on Wed Mar 14 01:56:02 2012

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  userid=maclean/******** directory=tmp dumpfile=mac.dmp tables=mac
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MACLEAN"."MAC"                             5.023 KB       1 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /tmp/mac.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 01:56:18


[oracle@vrh2 ~]$ impdp userid=maclean/maclean remap_table=maclean.mac:maclean.mac1 directory=tmp dumpfile=mac.dmp

Import: Release 11.2.0.3.0 - Production on Wed Mar 14 01:58:06 2012

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "MACLEAN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MACLEAN"."SYS_IMPORT_FULL_01":  userid=maclean/******** remap_table=maclean.mac:maclean.mac1 directory=tmp dumpfile=mac.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MACLEAN"."MACLEAN.MAC1"                    5.023 KB       1 rows
Job "MACLEAN"."SYS_IMPORT_FULL_01" successfully completed at 01:58:09

Maclean Liu(刘相兵

10g 的话没有REMAP_TABLE可用，你可以这样做：

1.先使用　remap_schema 将这张表导入到别的schema下，
2. 将这张表rename
3. 将这张表在 目标schema下 Create table as select ，或者将rename好的表导出，再倒入到目标schema。

ylw6006

多谢！我的环境是10g的，方案2也是我能想到的唯一方案！