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

Question

I was looking for a couple of solutions. One of them is the following:

Cause Remote statement matching is required but cannot be reached because global_names must be set to TRUE before it can be reached

Action Error changing session set global_names = true, if possible

But I do not understand why I need to set the global_names parameter ... Why does remote mapping use the global_names parameter? Please can you explain to me?

PS I know that when setting the global_names parameter, global naming rules will appear, that the database link name should be the same as the name of the remote database. And also adds the domain name to the database name, for example <DB_NAME>. <DB_DOMAIN> what else?

Answer 1

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

If the link doesn't work:

Mistake:

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

Cause:

You are trying to do a DML operation on a remote database using a local function.

This is "Oracle Bug", it should work, but it does not.

Example (for a better understanding):

- Suppose we have two databases DB1 and DB2

-On DB1 we have fun1 function

create function fun1 return number is begin return 1; end; 

-On DB1 we have a link to a database linking to DB2, called for simplicity DB2.

-Check that it works.

 select * from dual@DB2 

-If the following conclusion, then it works.

 DUMMY ----- X 

-Allows you to create a test table in DB2 (connect to a DB2 database)

 create table tesTable( id number, testColumn number ); 

-Makes 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 that you already know in what situation this error occurs, write a solution. It has two solutions:

Decision:

• Set global_names to true, it can be done at the system level or at the session level (think that the session level is sometimes unavailable)

- to DB1

 alter session set global_names=true; 

• Create a link to the database in the remote database, in our case, DB2, which will reference the DB1 database (enter the link name in the same way as the global database name, as this requires the global_names parameter for true).

-On DB2

 Create database link DB1 connect to <username> identified by <password> using 'DB1'; 

Now it should work, but I have to mention that creating a database link may not be desirable,

because it is not safe (you have to guess why, because if you do this, you can connect to DB1 with some user via the database link ... if it is not important for you, then use itJ).

Solution two:

• Create a temporary table in the local database.
• Insert a row into the temporary table.
• Insert the temporary row from the temporary table into the remote database.
• Delete the temporary line. Please note that this solution is slower than the first. But it also solves the problem and is much safer.

Answer 2

Kupa's answer offers a great explanation and good solutions, however, if you do not want or cannot follow solution one or two there, I suggest checking solution 2 here: http://dbtricks.com/?p=263 .

This worked for me by creating a variable and assigning it the value of a local function, and then using the variable in the sql statement that references the remote table.

Hope this helps someone as it helps me!

Answer 3

Quote this thread :

 ...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)