How to change the table name during import?


In 11g you can do this by using REMAP_TABLE


Following is the information from Oracle 11g documentation.

REMAP_TABLE

Default: There is no default

Purpose:

Allows you to rename tables during an import operation performed with the transportable method.

Syntax and Description

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

You can use the REMAP_TABLE parameter to rename entire tables.
You can also use it to override the automatic naming of table partitions that were exported using the transportable method. When partitioned tables are exported using the transportable method, each partition and subpartition is promoted to its own table and by default the table is named by combining the table and partition name (that is, tablename_partitionname). You can use REMAP_TABLE to specify a name other than the default.

Restrictions

•Only objects created by the Import will be remapped. In particular, preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND.

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.dmp
TABLES=hr.employees REMAP_TABLE=hr.employees:emps

4 comments:

  1. Is there any possibility for earlier versions? (9i for example)

    ReplyDelete
  2. is there any option for same with imp.exe

    ReplyDelete
  3. i have done this by exporting table and rename table name in dump file and import it ....!!!!

    ReplyDelete