Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12801
ORA-12801 [message #260129] Fri, 17 August 2007 09:39 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi,
i am running one plsql job. job has aborted with following error.

ORA-12801: error signaled in parallel query server P000 ORA-01452: can


Can u suggest me what i need to check

Thanks in advance
sagar
Re: ORA-12801 [message #260130 is a reply to message #260129] Fri, 17 August 2007 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
01452, 00000, "cannot CREATE UNIQUE INDEX; duplicate keys found"
Re: ORA-12801 [message #260197 is a reply to message #260130] Fri, 17 August 2007 12:44 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Thx!!

When i am trying to delete the record which is duplicate i am
Getting following error

ORA-01502: index 'CME.ERP_AVP_TRANSACTIONS_PK' or partition of such index is in unusable state


Please let me know how can delete the record now

Thanks,
Sagar
Re: ORA-12801 [message #260198 is a reply to message #260129] Fri, 17 August 2007 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
rebuild the index
Re: ORA-12801 [message #260205 is a reply to message #260198] Fri, 17 August 2007 13:01 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

this syntax is ok

alter index CME.ERP_AVP_TRANSACTIONS_PK rebuild


CME.ERP_AVP_TRANSACTIONS_PK is my index name.

Tnanks,
Sagar
Re: ORA-12801 [message #260213 is a reply to message #260205] Fri, 17 August 2007 13:31 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

when i am runing above query i am getting following error

ORA-14086: a partitioned index may not be rebuilt as a whole
Re: ORA-12801 [message #260221 is a reply to message #260213] Fri, 17 August 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search which partitions are in unusable state and rebuild them one by one.

Regards
Michel
Re: ORA-12801 [message #260236 is a reply to message #260221] Fri, 17 August 2007 14:42 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

can we delete record partition wise

some thing like

DELETE FROM ERP_AVP_TRANSACTIONS A partition (PY2008W02) WHERE ROWID > 
(SELECT min(rowid) FROM ERP_AVP_TRANSACTIONS B partition (PY2008W02)
WHERE A.MAVI_ID = B.MAVI_ID and A.TRANSACTION_DATE=B.TRANSACTION_DATE );


Thnaks,
Sagar
Re: ORA-12801 [message #260241 is a reply to message #260236] Fri, 17 August 2007 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't try it but this is a valid syntax, so I think this possible.

Regards
Michel
Re: ORA-12801 [message #260296 is a reply to message #260241] Sat, 18 August 2007 03:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
DELETE FROM ERP_AVP_TRANSACTIONS A partition (PY2008W02) WHERE ROWID >
(SELECT min(rowid) FROM ERP_AVP_TRANSACTIONS B partition (PY2008W02)
WHERE A.MAVI_ID = B.MAVI_ID and A.TRANSACTION_DATE=B.TRANSACTION_DATE );

I presume you want to delete all the records in that partition.
If that being the case use truncate instead of delete. It is way much quicker but remember you cannot rollback.
Quote:
alter table table_name truncate partition partition_name


Regards

Raj
Re: ORA-12801 [message #260298 is a reply to message #260296] Sat, 18 August 2007 04:02 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think he wants to delete duplicate records in the partition.

Regards
Michel
Previous Topic: dbms_job problem
Next Topic: Sum through a hierarchy
Goto Forum:
  


Current Time: Thu Dec 08 04:11:29 CST 2016

Total time taken to generate the page: 0.14598 seconds