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. 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 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:28 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 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_DATA Job \"SYSTEM\".\"SYS_IMPORT_FULL_01\" completed with 1 error(s) at 13:32:35 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:39 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 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. 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:35 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 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:23 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.
Friday, October 14, 2011
How To Table_exists_action Parameter of Data Pump
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. 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 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:28 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 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_DATA Job \"SYSTEM\".\"SYS_IMPORT_FULL_01\" completed with 1 error(s) at 13:32:35 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:39 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 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. 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:35 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 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:23 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.
