Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Cause A remote mapping of the statement is required but cannot be achieved because global_names should be set to TRUE for it to be achieved

Action Issue alter session set global_names = true if possible

But I don't understand why should I need to set global_names parameter...Why does remote mapping use global_names parameter? Please,can you explain me?

P.S I know that setting global_names parameter will arise global naming rules,that the database link name must be the same as the remote database name..And also it appends domain name to the database name like <DB_NAME>.<DB_DOMAIN> what else?

The answer is discussed here: http://dba010.wordpress.com/2011/01/05/oracle-errorsora/#ORA-02069

In case link doesn't work:

Error:

ORA-02069: global_names parameter must be set to TRUE for this operation

Cause:

You are trying to make DML operation on the remote database using local function.

This is the “Oracle Bug”, it should work but it doesn’t.

Example (for better understanding):

–Assume that we have two databases DB1 and DB2

–On DB1 we have function fun1

create function fun1 return number is
begin
return 1;
–On DB1 we have a database link referring to DB2 called, for simplicity, DB2.
–Check that it works.
select *
from dual@DB2
–If the output is the following, then it works.
DUMMY
-----
–Let’s create test table in DB2(connect to DB2 database)
create table tesTable(
id         number,
testColumn number
–Let’s make some DML operation, which should cause this ORA-02069 error.
insert into testable@DB2(id,testColumn)
values(1, fun1);
“ORA-02069: global_names parameter must be set to TRUE for this operation”
Now, when you already know in what situation this error occurs let’s write the solution. It has two solutions:
Solution one:
Set the global_names parameter to true, it can be done on the system level or session level(consider that session level sometimes is not available)
--On DB1
alter session set global_names=true;
Create database link on the remote database, in our case on DB2, which will refer to the database DB1(make link name the same as the database global name, because setting global_names parameter to true requires it).
–On DB2
Create database link DB1 connect to <username> identified by <password>
using ‘DB1’;
Now it should work, but I should mention that creating database link may not be preferable, 
because it is not secure (You should guess why, because if you do this you will be able to
 connect to DB1 with some user through database link…if it doesn’t matter for you then use itJ).
Solution  two:
Create temporary table on the local database.
Insert row into the temporary table.
Insert the temporary row from the temporary table to the remote database.
Delete the temporary row.
Note that this solution is slower than the first one. But it also solves the problem and is much more secure.
kupa's answer offers a great explanation and good solutions as well, however if you don't want to or are not able to follow solution one or two there, I suggest checking out solution 2 here: http://dbtricks.com/?p=263. 
This worked for me, creating a variable and assigning the value of the local function to it, then using the variable in the sql statement that referenced the remote table. 
Hope this helps somebody as it helped me! 
...in the past Oracle used .world as a default domain if domain 
part was not specified in global db name, they changed it (I 
believe in 10g R1, but I'm not sure)
I got same error trying to access remote table through database link. My usecase is that local database is in docker and it tries to build subset of original linked database using its metadata. Attempt to get columns of remote table
select *
from all_tab_cols@remotedb r
where r.table_name = 'SOME_TABLE'
produces ORA-02069. Thanks to attempt of creating local table using create table as select * ..., I was notified that some column of LONG type couldn't be transferred. I actually didn't need the * and with concrete columns of simple type
select r.table_name, r.column_name, r.column_id
from all_tab_cols@remotedb r
where r.table_name = 'SOME_TABLE'
the error disappeared.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.