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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
