Home » RDBMS Server » Server Utilities » ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =data_ (Oracle 11gR2 , Linux )
- ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =data_ [message #527264] Mon, 17 October 2011 05:10 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Hi,

I am trying to import the dump using the impdp utility and followed the below steps .
I have disabled all the constraints and executed the below command
Code: [Select all] [Show/ hide]
impdp DIRECTORY=EXPDP_DAILY_BACKUP_DIR DUMPFILE=expdp_QA_full_11102011_214501.dmp 
logfile=imp_EXPDP_QA_full_12102011_203254.log remap_schema=prod:prod remap_tablespace=prod:prod 
schemas=prod TABLE_EXISTS_ACTION=truncate content=data_only.

but I am getting the below error like this for 1 or 2 tables . and If I import those tables seperately its getting imported successfully. i am not getting the below error always .
Code: [Select all] [Show/ hide]
ORA-31693: Table data object "PROD"."DAS_ID_GENERATOR" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.DAS_ID_GENERATOR_P) violated

ORA-31693: Table data object "PROD"."TKT_DIST_SRV_STAT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.SERVER_STATS_P) violated


Please help

[Updated on: Mon, 17 October 2011 05:22] by Moderator

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527270 is a reply to message #527264] Mon, 17 October 2011 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Try adding "EXCLUDE=CONSTRAINT" (after re-disabling the constraints).

Regards
Michel

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527296 is a reply to message #527270] Mon, 17 October 2011 06:56 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Actually the above tables are having only primary key and 'NOT NULL' columns . If I add EXCLUDE=CONSTRAINT in the impdp command , my understanding is that,the PK will be disabled and the rows will be imported into the table without any errors and while enabling the constraint it will give the error due to duplicate rows(If the truncate didn't happened on the tables even after TABLE_EXISTS_ACTION=truncate). please correct me if Im wrong.

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527297 is a reply to message #527296] Mon, 17 October 2011 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Of course!
You have duplicates so either the key is enabled and you get your mentioned errors,
either it is disabled and you will the error when you will try to enable it.
What do you expect?

Regards
Michel

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527299 is a reply to message #527297] Mon, 17 October 2011 07:26 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
The source table doesnot have the duplicate rows,thats why I have disabled the constraints in the target and truncated the tables using (TABLE_EXISTS_ACTION=truncate) and imported the data.

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527470 is a reply to message #527299] Tue, 18 October 2011 04:15 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
please help to resolve this issue.

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527480 is a reply to message #527470] Tue, 18 October 2011 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Truncate yourself the tables and retry.

Regards
Michel

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527504 is a reply to message #527480] Tue, 18 October 2011 07:04 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
That will work!!!. But why the above scenario is happening . is it a Oracle bug? or any other reason is there?

Report message to a moderator

- Re: ORA-00001: unique constraint violated error during impdp TABLE_EXISTS_ACTION=truncate content =d [message #527512 is a reply to message #527504] Tue, 18 October 2011 07:58 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Who knows what you did before?
Only a detailed trace of what has been done can allow to diagnose.
I thought that there was a mistake on our side, this is why I suggested this solution.

Regards
Michel

Report message to a moderator

Previous Topic: impdp error
Next Topic: importing full database with different database name and sid
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 28 15:30:23 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.