Import DataPump: How to Import Table Data into a Table that has Different Name ?

Import DataPump: How to Import Table Data into a Table that has Different Name ? [ID 342314.1]

Modified 20-OCT-2010     Type HOWTO     Status PUBLISHED

In this Document
Goal
Solution


Applies to:

Enterprise Manager for RDBMS – Version: 10.1.0.2 to 11.2.0.2 – Release: 10.1 to 11.2
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Oracle Server – Personal Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Oracle Server – Standard Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Information in this document applies to any platform.

Goal

Checked for relevance on 10-20-2010

How to import table data into a target table that has the same structure as the original source table, but has a different name?

Solution

For Import DataPump using Oracle 10g, you can use the REMAP_SCHEMA parameter to remap the schema name during the import (similar to the FROMUSER and TOUSER parameters in the original import utility). However, there is no parameter to remap table names. This means that Import DataPump can only import data into a table with the same name as the original table.

The examples below are based on:

A. The demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql

B. The directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

Windows:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;
Unix:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;

C. Two tables with identical structure, but different names:

CONNECT scott/tiger
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
SELECT COUNT(*) FROM emp;
  COUNT(*)
----------
        14
SELECT COUNT(*) FROM emp2;
  COUNT(*)
----------
         0

D. You have an export dumpfile of table SCOTT.EMP that was created with:

expdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=exp_emp.log tables=scott.emp

OBJECTIVE: import the data from the export dumpfile into table SCOTT.EMP2
1. If the original source table can be temporary renamed:

-- temporary rename the two tables:
CONNECT scott/tiger
RENAME emp TO emp_temp;
RENAME emp2 TO emp;

-- import data into table EMP:
impdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=imp_emp.log full=y
table_exists_action=append content=metadata_only

-- rename tables back to their original names:
CONNECT scott/tiger
RENAME emp TO emp2;
RENAME emp_temp TO emp;

2: If the original source table cannot be renamed:

-- create a temporary user:
CONNECT system/manager
CREATE USER tempuser IDENTIFIED BY tempuser DEFAULT TABLESPACE users;
ALTER USER tempuser QUOTA UNLIMITED ON users;
GRANT create session, create table TO tempuser;

-- import data into user TEMPUSER:
impdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=imp_emp.log full=y
remap_schema=scott:tempuser

-- rename the table in the TEMPUSER schema:
CONNECT tempuser/tempuser
RENAME emp TO emp2;

-- export data again, now from (renamed) table TEMPUSER.EMP2:
expdp system/manager directory=my_dir dumpfile=exp_emp2.dmp logfile=exp_emp2.log
tables=tempuser.emp2 content=data_only

-- import data into SCOTT.EMP2:
impdp system/manager directory=my_dir dumpfile=exp_emp2.dmp logfile=imp_emp2.log full=y
remap_schema=tempuser:scott table_exists_action=append

-- check results, and cleanup:
CONNECT system/manager
SELECT COUNT(*) FROM scott.emp;
  COUNT(*)
----------
        14
SELECT COUNT(*) FROM scott.emp2;
  COUNT(*)
----------
        14
DROP USER tempuser CASCADE;

Remarks:

(1) Note that within the same database, this could also be achieved with a single INSERT statement:

SQL> insert into scott.emp2 (select * from scott.emp);

(2) Note that with the original (non-DataPump) export and import utilities there was another solution if the tablename in the export dumpfile did not exist in the schema in the target database. E.g. based on previous example:

-- export from source database:
exp system/manager file=exp_emp.dmp log=exp_emp.log tables=scott.emp

-- on target database: ensure that table EMP doesn't exist:
CONNECT scott/tiger
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
DROP TABLE emp;

-- create synonym for table EMP2:
CREATE SYNONYM emp FOR emp2;

-- import data from EMP into table EMP2:
imp system/manager file=exp_emp.dmp log=imp_emp.log full=y ignore=y indexes=n constraints=n

(3) Starting with Oracle 11g, DataPump provides a new command REMAP_TABLE to import the table data into a new table name on the target database.   See the 11g Oracle Database Utilities for more on this new feature.

— import data from EMPLOYEES into table EMPS:
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps

Advertisements
Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: