ORA-02070: database does not support in this context

oracle dblink
Vivek picture Vivek · Jan 4, 2013 · Viewed 20.7k times · Source

I have a query like 

INSERT INTO sid_rem@dev_db
(sid)
select sid from v$session

Now, when i execute this query i get ORA-02070: database does not support in this context

This error happens only when I insert data from v$session into some remote db. Its working fine for any other table.

Anyone know why this issue and any workaround for this?

Answer

Florin Ghita picture Florin Ghita · Jan 4, 2013

Works using gv$session instead of v$session:

INSERT INTO sid_rem@dev_db(sid)
select sid from gv$session;

gv$ views are global views, that is, they are not restricted to one node(instance), but see the entire database(RAC). v$ views are subviews of gv$.

Searching on the internet I found this has something to do with distributed transactions.

Thread on ora-code.com

Related questions

How to SELECT in Oracle using a DBLINK located in a different schema?

We have an Oracle DBMS (11g) and the following configuration: A DB user "MYUSER" Two schemas "MYUSER" and "SCHEMA_B" User "MYUSER" can access "SCHEMA_B" and has READ permissions on its tables A public DB link "DB_LINK" located …

Read More
How to create a DB link between two oracle instances

How to create a DB link between two Oracle instances. Let's say A and B are two instances. I want to access the data in instance B from the instance A.

Read More
How to check if a database link is valid in Oracle?

I have a main database with only setup data at the headquarter and several databases at different branches.I created a database link for each branch server. In some case I would like to query all the valid links (as …

Read More