Hi Experts,
I am trying to execute below script in two ATP instances.
drop table XX_PDT;
CREATE TABLE XX_PDT(NAME VARCHAR2(240),NUMBER1 NUMBER);
INSERT ALL INTO XX_PDT VALUES('A',1)
INTO XX_PDT VALUES('B',1)
INTO XX_PDT VALUES('C',1)
INTO XX_PDT VALUES('A',1)
INTO XX_PDT VALUES('B',1)
INTO XX_PDT VALUES('C',1)
SELECT * FROM DUAL;
COMMIT;
UPDATE XX_PDT SET NUMBER1=3 WHERE NAME='B';
SELECT * FROM XX_PDT;
ATP Instance 1(Dev): SELECT query runs fine and gets the updated and uncommitted table data since I am in same session.
ATP Instance 2(UAT): SELECT query errors out with below error
ORA-12838: cannot read/modify an object after modifying it in parallel
12838. 00000 - "cannot read/modify an object after modifying it in parallel"
*Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.
*Action: Rewrite the transaction, or break it up into two transactions
one containing the initial modification and the second containing the
parallel modification operation.
I know that I have to COMMIT after the DML but our use case does not allow us to put a commit just after the DML.
Please help me to identify the reason for different behaviour across instances and best coding practices. If the above script errored out on both instances then it would be fine but its working in DEV but not in UAT.
Thanks,
Nataraj