Oracle-L: ORA-14403 error detected during bulk updates

From: Ranganath K <Ranganath_K01_at_infosys.com>
Date: Fri, 17 Oct 2003 00:44:24 -0800
Message-ID: <F001.005D36D5.20031017004424@fatcity.com>

Hi there,

	We are running Oracle 9.0.2.3 on Sun Solaris 8.
	We have two fact tables - fct1 and fct2 and two PL SQL stored
procedures - P1 and P2
	fct1 - range partitioned on fct1_field1 and hash partitioned on
fct1_field2
	fct2 - range partitioned on fct2_field1 and hash partitioned on
fct2_field2
	Program P1 is updating fct1.
	Program P2 is selecting from fct1 and updating fct2.
	Both P1 and P2 are processing different sets of records.
	P1 - history records and P2 - current records
 
	In program P2 we are having multiple bulk updates (approx. 30)
on fct2 (using fct1). 	Earlier we had a 	cursor in this program
for updating around 20 fields but opening 	and closing of cursor
was giving bad performance so we changed it to individual 	bulk

updates.

	Table statistics - 
	fct1 - 300 million records and 
	fct2 - around 200 million records
	P2 has to update approximately 0.8 million records everyday.

	Is there a possibility of oracle throwing a DML partition lock
error? 
	Or is it that oracle throws DML lock error only if same set of
records are updated 	and selected by P1 and P2 respectively?

	We have such a scenario in one of the programs and are getting

the error ORA-14403:

14403, 00000, "cursor invalidation detected after getting DML partition lock"

// *Cause: cursor invalidation was detected after acquiring a partition lock

// during an INSERT, UPDATE, DELETE statement. This
error is never

// returned to user, because is caught in opiexe() and
the DML

// statement is retried.

// *Action: nothing to be done, error should never be returned to user

		Program P1 is updating some set of records in the table
T1. Program P2 is 	running in parallel and using a different set of
records from table T1 for some 	processing. Is there a possibility of

oracle throwing a DML partition lock error?

Any help in this regard is very much appreciated.

Thanks and Regards,

Ranganath
www.mailfiler.com [RK-K521CR3]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ranganath K
  INET: Ranganath_K01_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 17 2003 - 03:44:24 CDT