Geek Answers Handbook

ORA-02070: the database is not supported in this context

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 occurs only when I insert data from v $ session into some remote db. His work is great for any other table.

Does anyone know why this question and any workaround for this?

+4
source share
3 answers

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 limited to one node (instance), but they see the entire database (RAC). v $ view is subviews from gv $.

Searching the Internet I found this to be related to distributed transactions.

Thread on ora-code.com

+7
source

I don't know why this is happening, perhaps in the documentation, but my Oracle-Docs-Fu seems to have left me today.

One possible way to work 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.
+1
source

Late answer, but may be helpful. I found that this error occurs when I try to select from the system views using the database link, where the system view contains LONG columns. If the query can be modified to avoid LONG columns, these connections 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 end with ORA-02070 due to access to 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 SEARCH_CONDITION column SEARCH_CONDITION of DBA_CONSTRAINTS is not available.

Share and enjoy.

0
source

More articles:


All Articles