Reputation: 4642
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?
Upvotes: 4
Views: 28250
Reputation: 50017
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.
Upvotes: 2
Reputation: 17643
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.
Upvotes: 8
Reputation: 52853
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.
Upvotes: 1