Home » SQL & PL/SQL » SQL & PL/SQL » ORA-08177: in a query this dblink
ORA-08177: in a query this dblink [message #216835] Tue, 30 January 2007 11:53 Go to next message
aline
Messages: 92
Registered: February 2002
Member
Hi I'm trying to make query inside a dblink with a serializable level.
I'm getting ora-08177 all the times.
Someone could explain to me what happened there?


SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction set

SQL> insert into my_table
  2  select t.*
  3  from my_table@mylink t
  4  where mycondition;

insert into my_table
select t.*
from my_table@mylink t
where mycondition

ORA-08177: can't serialize access for this transaction
Re: ORA-08177: in a query this dblink [message #216838 is a reply to message #216835] Tue, 30 January 2007 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
08177, 00000, "can't serialize access for this transaction"
// *Cause: Encountered data changed by an operation that occurred after
// the start of this serializable transaction.
// *Action: In read/write transactions, retry the intended operation or
// transaction.

Is "into my_table" and "from my_table@mylink" the same table?
Re: ORA-08177: in a query this dblink [message #216843 is a reply to message #216838] Tue, 30 January 2007 12:11 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
of course no.
Those tables are not on the same user,not on the same database not on the same server!
Re: ORA-08177: in a query this dblink [message #216844 is a reply to message #216835] Tue, 30 January 2007 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It may be a bug, then again may be not.
What versions of Oracle to 4 decimal place for both local & remote DBs?
Re: ORA-08177: in a query this dblink [message #216845 is a reply to message #216835] Tue, 30 January 2007 12:34 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
it's the 9.2.0.7 for both.
Sometime it's run sometime no.
I have checked it to metalink and saw something similar.
But it was in the 8.1.7 and in 2003!
Re: ORA-08177: in a query this dblink [message #216848 is a reply to message #216835] Tue, 30 January 2007 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>insert into my_table
Is my_table above a GTT?

Might there be an uncommited change pending upon MY_TABLE above?
Re: ORA-08177: in a query this dblink [message #216856 is a reply to message #216835] Tue, 30 January 2007 13:12 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
No, it's not a GTT table.
The only process who insert into mytable is the process who is getting ora-01877
Re: ORA-08177: in a query this dblink [message #226177 is a reply to message #216835] Thu, 22 March 2007 13:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Stumbled upon your question while researching whether I should use serializable or read write for a transaction I'm writing. Although I haven't run a test to demo anything, the below documentation quote from the set transaction statement in the plsql user guide and reference likely applies to your situation:

Quote:

SERIALIZABLE: If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.



Re: ORA-08177: in a query this dblink [message #226178 is a reply to message #216835] Thu, 22 March 2007 14:00 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
On second thought, after reading your initial query more carefully, you are not actually executing two statements that modify the same table in the same transaction (although it appeared that way to me at first glance).

The second insert is just a repeat of the first Embarassed
Previous Topic: Table/view Report
Next Topic: how to i ncrease dbms_ouput buffer
Goto Forum:
  


Current Time: Sat Dec 03 04:12:22 CST 2016

Total time taken to generate the page: 0.05814 seconds