![]() ![]() I don’t know about such a feature, but I made some “digging” and succeeded to come out with a solution.įirst I looked at the statements that Data Pump did behind the scenes (by querying V$SQL), and one of the statements I found was this: We can also see from the log which rows were rejected, or more precisely, what are the primary key values of the rejected rows.īut what if we want to know the values of the other columns in the rejected rows? ![]() We see that 4 rows were imported and that 2 rows were rejected due to unique constraint violation. Job "OREN"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:38:06 imported "OREN"."PRESIDENTS" 5.945 KB 4 out of 6 rowsĢ row(s) were rejected with the following error: Starting "OREN"."SYS_IMPORT_TABLE_01": oren/******** tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp2.log data_options=SKIP_CONSTRAINT_ERRORS Now let’s execute the import with the SKIP_CONSTRAINT_ERRORS option: impdp oren/oren tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp2.log data_options=SKIP_CONSTRAINT_ERRORS Job "OREN"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 16:33:35Īs expected, if we check the contents of the table we see that no records have been added from the dump file: select * from presidents Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT ORA-00001: unique constraint (OREN.SYS_C0023857) violated ORA-31693: Table data object "OREN"."PRESIDENTS" failed to load/unload and is being skipped due to error: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append ORA-39152: Table "OREN"."PRESIDENTS" exists. Processing object type TABLE_EXPORT/TABLE/TABLE Starting "OREN"."SYS_IMPORT_TABLE_01": oren/******** tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp1.log Master table "OREN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded All rights reserved.Ĭonnected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production If we try to execute the following import, without the SKIP_CONSTRAINT_ERRORS option, we will get an error and the whole operation will be rolled back: impdp oren/oren tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp1.log Obviously, the records with ID 7 and 8 cannot be added to the table, because a primary key constraint is defined on the ID column, and the table already contains records with these ID values. The dump file contains the following data: ID FIRST_NAME LAST_NAME We have a dump file that contains an export of the presidents table (from somewhere else or from some previous point in time), and we’d like to append the data from the dump file to the table. The table presidents was created by the following statement: create table presidents (Īnd is currently containing the following data: ID FIRST_NAME LAST_NAME If SKIP_CONSTRAINT_ERRORS is not set, then the default behavior is to roll back the entire load of the data object on which constraint violations are encountered. It logs any rows that cause constraint violations, but does not stop, and does load the other rows. Since version 11.1, Data Pump Import supports the SKIP_CONSTRAINT_ERRORS option, which specifies that you want the import operation to proceed even if (non-deferred) constraint violations are encountered. ĭrwxrwxrwx 3 oracle dba 256 Jan 05 12:59. Or, You can see all the privileges for all directories with the following script : select *fromĭirectory privileges: READ and WRITE. ORA-31640: unable to open dump file "C:\app\admin/admin/orcluat/dpdump/expfULLDB.DMP" for read ![]() With the Partitioning, OLAP, Data Mining and Real Application Testing options All rights reserved.Ĭonnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production I was trying to import dump file to my test oracle database using the following import line: Import: Release 11.2.0.4.0 - Production on Wed jan 10 10:52:34 2018Ĭopyright (c) 1982, 2011, Oracle and/or its affiliates. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |