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?
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.
v$session is essentially a view of gv$session but limited to your current instance, which ought to be the same in a non-RAC environment. The limitation is done with a where inst_id = USERENV('Instance') clause, as in the thread you linked to, and it is that which causes the ORA-02070.
Late answer but might still be useful. I've found this error occurs when trying to select from system views across a database link where the system view contains columns of type LONG. If the query can be reworded to avoid the LONG columns these joins will work fine.
Example:
SELECT dc_prod.*
FROM dba_constraints@prod_link dc_prod
INNER JOIN dba_constraints dc_dev
ON (dc_dev.CONSTRAINT_NAME = dc_prod.CONSTRAINT_NAME)
will fail with an ORA-02070 due to accessing the LONG column SEARCH_CONDITION, but
SELECT dc_prod.*
FROM (SELECT OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
-- SEARCH_CONDITION,
R_OWNER,
R_CONSTRAINT_NAME,
DELETE_RULE,
STATUS,
DEFERRABLE,
DEFERRED,
VALIDATED,
GENERATED,
BAD,
RELY,
LAST_CHANGE,
INDEX_OWNER,
INDEX_NAME,
INVALID,
VIEW_RELATED
FROM dba_constraints@prod_link) dc_prod
INNER JOIN (SELECT OWNER,
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
-- SEARCH_CONDITION,
R_OWNER,
R_CONSTRAINT_NAME,
DELETE_RULE,
STATUS,
DEFERRABLE,
DEFERRED,
VALIDATED,
GENERATED,
BAD,
RELY,
LAST_CHANGE,
INDEX_OWNER,
INDEX_NAME,
INVALID,
VIEW_RELATED
FROM dba_constraints) dc_dev
ON (dc_dev.CONSTRAINT_NAME = dc_prod.CONSTRAINT_NAME)
works fine because the LONG column SEARCH_CONDITION from DBA_CONSTRAINTS is not accessed.
Share and enjoy.
I don't know why this is happening, it's probably in the documentation somewhere but my Oracle-Docs-Fu seems to have deserted me today.
One possible work-around is to use a global temporary table
SQL> create table tmp_ben ( sid number );
Table created.
SQL> connect schema/pw@db2
Connected.
SQL> create table tmp_ben ( sid number );
Table created.
SQL> insert into tmp_ben@db1 select sid from v$session;
insert into tmp_ben@db1 select sid from v$session
*
ERROR at line 1:
ORA-02070: database does not support in this context
SQL> create global temporary table tmp_ben_test ( sid number );
Table created.
SQL> insert into tmp_ben_test select sid from v$session;
73 rows created.
SQL> insert into tmp_ben@db1 select * from tmp_ben_test;
73 rows created.
