| ORA-12838: cannot read/modify an object after modifying it in parallel [message #333084] |
Thu, 10 July 2008 08:38  |
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  |
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 |
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 |
Frank
Messages: 7901
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 #333101 is a reply to message #333089] |
Thu, 10 July 2008 09:06 |
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 #333122 is a reply to message #333118] |
Thu, 10 July 2008 09:44 |
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
