Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12838: cannot read/modify an object after modifying it in parallel (Oracle 9i)
ORA-12838: cannot read/modify an object after modifying it in parallel [message #333084] Thu, 10 July 2008 08:38 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SQL> CREATE TABLE t AS SELECT * FROM emp where empid in (1,4)
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
9   B                                   60000
SQL> select * from t;

SQL> insert  into t select * from emp where empid=9;
EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
9   B                                   60000

Elapsed: 00:00:00.01

SQL> rollback;

SQL> insert  /*+ APPEND */ into t select * from emp where empid=6;
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel



What does it mean? And why I am not being able to insert while using hint?

Regards,
Oli
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333087 is a reply to message #333084] Thu, 10 July 2008 08:43 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I believe Oracle is enforcing this to guarantee consistency of the
database. I realise you are probably using the APPEND hint for performance
reasons but is it really necessary?

Otherwise you could perhaps look at move complex arrangements involving
temporary tables for the two inserts and then a single insert into the
final table. Perhaps even place the first query into a temporary table and
then for the second query add "union all select * from temp_table " to the
end to combine the two results. Some times you have to work with what you
are given - I doubt it 's an Oracle bug, there 's probably a real
complication with performing two appended inserts on a table without
commiting the first lot of data (perhaps a sudden inability to rollback the
transaction).
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333089 is a reply to message #333087] Thu, 10 July 2008 08:49 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

SQL> CREATE TABLE t AS SELECT * FROM emp where empid in (1,4)
SQL> select * from t;

** 
EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
SQL> select * from t;

SQL> insert  into t select * from emp where empid=9;
EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
9   B                                   60000

Elapsed: 00:00:00.01

SQL> rollback;

SQL> insert  /*+ APPEND */ into t select * from emp where empid=6;
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel



**corrected

Is it a bug?? why I am not being able to append(insert) while using hint?

Regards,
Oli


[Updated on: Thu, 10 July 2008 08:52]

Report message to a moderator

Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333090 is a reply to message #333089] Thu, 10 July 2008 08:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Altough you are more then welcome to ask questions here of course, I think you are getting to the point where you should try to get the answer to your questions first by checking the docs and googling for it.
If you want to become a good developer, you need to learn how to find these answers.
If you read the docs and want to discuss some details, it would be fun to do it here; if you searched and cannot find, we're glad to help, but remind that the idea of OraFAQ is to help people help themselves.
If this sounds harsh, it is because I chose the wrong wording; it is meant as good advice.
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333091 is a reply to message #333084] Thu, 10 July 2008 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>And why I am not being able to insert while using hint?
Reality check, please.
You successfully completed the INSERT!

Consider issuing
COMMIT;
before doing the SELECT.
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333094 is a reply to message #333090] Thu, 10 July 2008 09:00 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for your suggesion Frank..
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333101 is a reply to message #333089] Thu, 10 July 2008 09:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From the Docs

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.

An Append hint counts as a direct load.
All you need to do is to commit the transaction, and you'll be able to see the data just fine.

Franks advice is good, and well meant - things that you learn for yourself stick with you much more than things that people tell you.
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333118 is a reply to message #333091] Thu, 10 July 2008 09:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
anacedent wrote on Thu, 10 July 2008 08:58
>And why I am not being able to insert while using hint?
Reality check, please.
You successfully completed the INSERT!

Consider issuing
COMMIT;
before doing the SELECT.



I did put a ROLL BACK. Can you please check that? I did not wanted to commit before executing last insert statement
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333121 is a reply to message #333084] Thu, 10 July 2008 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
> I did not wanted to commit before executing last insert statement
& that is why you get the error.
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333122 is a reply to message #333118] Thu, 10 July 2008 09:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to read what @Anacedent wrote more carefully.

SQL> insert  /*+ APPEND */ into t select * from emp where empid=6;
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Look at this code.
You do an insert - everything it fine.
You do a select - an error is raised.
Try doing:
insert  /*+ APPEND */ into t select * from emp where empid=6;
commit;
select * from t;
Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333127 is a reply to message #333122] Thu, 10 July 2008 09:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
That means If we appending using hints it must be commited to get the display? But why?? Need to go through docs!!

Thanks to you all for throwing light on this issue.


SQL> CREATE TABLE t AS SELECT * FROM emp where empid in (1,4);

Table created.

Elapsed: 00:00:00.00
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000

Elapsed: 00:00:00.01
SQL> insert  into t select * from emp where empid=9;

1 row created.

Elapsed: 00:00:00.00
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
9   B                                   60000

Elapsed: 00:00:00.00
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
9   B                                   60000

Elapsed: 00:00:00.01
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000

Elapsed: 00:00:00.00


SQL> insert   into t select * from emp where empid=8;

1 row created.

Elapsed: 00:00:00.00

/* No coomit here */
SQL> select * from t;

EMP NAME                                  SAL
--- ------------------------------ ----------
1   X                                   30000
4   L                                   80000
8   M                                   60000

Elapsed: 00:00:00.00







[Updated on: Thu, 10 July 2008 10:11]

Report message to a moderator

Re: ORA-12838: cannot read/modify an object after modifying it in parallel [message #333149 is a reply to message #333127] Thu, 10 July 2008 10:36 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Need to go through docs!!

Sure it is fully documented. Wink

Regards
Michel
Previous Topic: how to fetch last inserted record from oracle table?
Next Topic: How to build Consolidate Results from multiple rows
Goto Forum:
  


Current Time: Tue Dec 06 10:21:43 CST 2016

Total time taken to generate the page: 0.11133 seconds