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

Question

I've searched a couple of solutions. One of them is the following:

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?

Answer 1

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;
end;

–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
-----
X

–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:

1. 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;

1. 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:

1. Create temporary table on the local database.
2. Insert row into the temporary table.
3. Insert the temporary row from the temporary table to the remote database.
4. 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.

Answer 2

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!