Sometimes it happens that we need to import table into an existing table.If we import the table in that schemas it throws error regarding the existence of the particular table.If we have to preserve the old data of table and append the new data,we can use the table_exists_action parameter of data pump.The valid key words are {SKIP | APPEND | TRUNCATE | REPLACE}.
The possible values of the following effects are :
1.) SKIP : leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.By default the value is SKIP .
2.) APPEND loads rows from the source and leaves existing rows unchanged.
3.) TRUNCATE deletes existing rows and then loads rows from the source.
Here is a DEMO of the TABLE_EXISTS_ACTION parameter :
First of all we will take the export table (say test ) which is in neer schemas.
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@noida directory=dpump tables=neer.test dumpfile=neer_test.dmp logfile=exp_neerlog.log
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 "NEER"."TEST" 5.062 KB 9 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
D:\DPUMP\NEER_TEST.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 13:23:13
Now we consider each of the valid keywords of action_exists_append parameter.
Case 1 : action_exists_append=skip (by defaults)
C:\>impdp system/xxxx@noida directory=dpump full=y dumpfile=neer_test.dmp logfile=imp_neerlog.log
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 13:32:22
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump full=y dumpfile=neer_test.dmp logfile=imp_neerlog.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:32:35
Hence, above results shows that the table is skipped .
Case 2 : table_exists_action=append
Now we delete the table test and recreate populate it values.
SQL> drop table test;
Table dropped.
SQL> create table test (id number);
Table created.
SQL> insert into test values (&Y);
Enter value for y: 111
old 1: insert into test values (&Y)
new 1: insert into test values (123)
1 row created.
SQL> /
Enter value for y: 222
old 1: insert into test values (&Y)
new 1: insert into test values (234)
1 row created.
SQL> /
Enter value for y: 333
old 1: insert into test values (&Y)
new 1: insert into test values (345)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
111
222
333
Now we will import the dump in neer schemas having table "test"
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:22:58
SQL> select * from test;
ID
----------
111
222
333
11
22
33
44
55
66
77
88
ID
----------
99
12 rows selected.
Hence we find that the imported table appends in existing table . This parameter only import the data of the tables and skips the indexes .
Case 3 : table_exists_action=truncate
we have already 12 rows in table "test" .Now we again import the dump having 9 rows.
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:26:51
SQL> select * from test;
ID
----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Case 4 : table_exists_action= replace
Now we will add few rows in table "test" to check the results.
SQL> insert into test values(1234);
1 row created.
SQL> insert into test values(12345);
1 row created.
SQL> insert into test values(34567);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
11
22
33
44
55
66
77
88
99
1234
12345
34567
12 rows selected.
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:33:42
SQL> select * from test ;
ID
----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Hence, table_exists_action=replace parameter internally drop and recreate the table .Hence all the existing metadata also get dropped and is recreated .
Note: Parameter table_exists_action=replace for a job with no metadata will not get imported .
Enjoy J J J
The possible values of the following effects are :
1.) SKIP : leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.By default the value is SKIP .
2.) APPEND loads rows from the source and leaves existing rows unchanged.
3.) TRUNCATE deletes existing rows and then loads rows from the source.
4.) REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Here is a DEMO of the TABLE_EXISTS_ACTION parameter :
First of all we will take the export table (say test ) which is in neer schemas.
C:\>expdp system/xxxx@noida directory=dpump tables=neer.test dumpfile=neer_test.dmp logfile=exp_neerlog.log
Export: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 13:21:28Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@noida directory=dpump tables=neer.test dumpfile=neer_test.dmp logfile=exp_neerlog.log
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 "NEER"."TEST" 5.062 KB 9 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
D:\DPUMP\NEER_TEST.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 13:23:13
Now we consider each of the valid keywords of action_exists_append parameter.
Case 1 : action_exists_append=skip (by defaults)
C:\>impdp system/xxxx@noida directory=dpump full=y dumpfile=neer_test.dmp logfile=imp_neerlog.log
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 13:32:22
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump full=y dumpfile=neer_test.dmp logfile=imp_neerlog.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "NEER"."TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATAJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:32:35
Hence, above results shows that the table is skipped .
Case 2 : table_exists_action=append
Now we delete the table test and recreate populate it values.
SQL> drop table test;
Table dropped.
SQL> create table test (id number);
Table created.
SQL> insert into test values (&Y);
Enter value for y: 111
old 1: insert into test values (&Y)
new 1: insert into test values (123)
1 row created.
SQL> /
Enter value for y: 222
old 1: insert into test values (&Y)
new 1: insert into test values (234)
1 row created.
SQL> /
Enter value for y: 333
old 1: insert into test values (&Y)
new 1: insert into test values (345)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
111
222
333
Now we will import the dump in neer schemas having table "test"
SQL>HOST impdp system/xxxx@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=append
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 14:22:39Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "NEER"."TEST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:22:58
SQL> select * from test;
ID
----------
111
222
333
11
22
33
44
55
66
77
88
ID
----------
99
12 rows selected.
Hence we find that the imported table appends in existing table . This parameter only import the data of the tables and skips the indexes .
Case 3 : table_exists_action=truncate
we have already 12 rows in table "test" .Now we again import the dump having 9 rows.
SQL>host impdp system/xxxx@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=truncate
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 14:26:35Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "NEER"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:26:51
SQL> select * from test;
ID
----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Case 4 : table_exists_action= replace
Now we will add few rows in table "test" to check the results.
SQL> insert into test values(1234);
1 row created.
SQL> insert into test values(12345);
1 row created.
SQL> insert into test values(34567);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
11
22
33
44
55
66
77
88
99
1234
12345
34567
12 rows selected.
SQL>host impdp system/xxxx@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=replace
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 14:33:23Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@noida directory=dpump dumpfile=NEER_TEST.dmp table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NEER"."TEST" 5.062 KB 9 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:33:42
SQL> select * from test ;
ID
----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Hence, table_exists_action=replace parameter internally drop and recreate the table .Hence all the existing metadata also get dropped and is recreated .
Note: Parameter table_exists_action=replace for a job with no metadata will not get imported .
Enjoy J J J
8 comments:
Thanks a lot. Your tips helped me to import my data dump successfully.
I have schema PDEENG on one database which i need to import into other database where the schema PDEENG exist. but the schema are important for me i dont want to drop the target schema can you please send me santex if possible
Hi Firdous
You can use the remap_schema parameter to import the data if u donot want to drop the same schemas
create a schemas say "PDEENG1" and fire the below command as
impdp username/password directory=dp dumpfile=.dmp remap_schema=PDEENG:PDEENG1 schemas=PDEENG
If you want to import the data into same "PDEENG" schema then lots of point to be consider .
1.)if u want import in same schema then the table having same name will be skip(by default) . So, here you have decide where you want to replace or append the data in the existing table. If you want to append then use the parameter table_exists_action=append and so on .
2.)You have to take care of meta- data also .
so, provide the full details and your need .
Good Luck
Enjoy :-)
Hi Neeraj,
Thank You for sharing this document. Whenever I get stuck with expdp I refer your notes.
Thanks
Shabbir
Thanks Shabbir ....
Have Good Time Ahead !!!
Enjoy :)
Hi,
Thanks for the post! Good article.
I've taken export of a table and i want to import the same in the same schema with different table name. Is it possible.?
Please suggest!
impdp \'/ as sysdba \' directory=EXPDP_DIR tables=Current_Schema_name.Table_name dumpfile=test2.dmp logfile=test2_imp2.log \
remap_table=Current_Schema_name.Table_name:New_table_name remap_schema=Current_Schema_nam:New_schema_name
Hi,
I am reading your comment found from google, which is very useful for me. i am searching some procedure and command to go smoothly for a db migration as a machine is changed soon.
I have tried to import with the full export dump file exported successfully. but i have got a lot of errors which were " object types already exists","compilation waring", "sql failing" and so on.
can you help me to do this db migration?
below is the import dump command used.
impdp system/oracle dumpfile=full.dmp directory=datapump full=y logfile=fullimp.log job_name=fullimp
your help would be really appreciated.
thanks,
sanghyun kim
Post a Comment