Query Over Oracle DB Link Requiring global_name = true

Question

I'm running the following queries over a private dblink (Oracle Database 19c):

delete from myschema.mytable@mydblink where SERVER = (select host_name from gv$instance);
insert into myschema.mytable@mydblink
select * from
( 
 select * from
 (
 SELECT 
 (select instance_name from gv$instance) as DB,
 (select host_name from gv$instance) as SERVER,
 to_char(SESSION_KEY) as SESSIONKEY,
 INPUT_TYPE as INPUTTYPE,
 STATUS,
 TO_CHAR(START_TIME,'DD-MON-YY HH24:MI:SS') as STARTTIME,
 TO_CHAR(END_TIME,'DD-MON-YY HH24:MI:SS') as ENDTIME,
-- time_taken_display as TIMETAKEN,
-- output_bytes_display as OUTPUTBYTES,
-- output_bytes_per_sec_display as OUTPUTBPS,
 1 as ORDERDUMMY
 FROM V$RMAN_BACKUP_JOB_DETAILS where end_time > sysdate - 3
 union
 select
 (select instance_name from gv$instance) as DB,
 (select host_name from gv$instance) as SERVER,
 'has' as SESSIONKEY,
 'no' as INPUTTYPE,
 'recent' as STATUS,
 'results' as STARTTIME,
 null as ENDTIME,
-- null as TIMETAKEN,
-- null as OUTPUTBYTES,
-- null as OUTPUTBPS,
 2 as ORDERDUMMY
 from dual
 )
 order by orderdummy fetch first 1 row with ties
);
commit;

They work just fine to delete, insert, and commit the data like I want it to. The problem arises when I uncomment any one of the three column pairs. For example, if I uncomment the two TIMETAKEN lines (target table adjusted to match column needs), the query errors with:

Error report - SQL Error: ORA-02069: global_names parameter must be set to TRUE for this operation 02069. 00000 - "global_names parameter must be set to TRUE for this operation" *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

Setting global_names = true does not help because the dblink name doesn't match the db name. I'd like to understand why the query works fine until I try to add any one of these three columns to the query...

*time_taken_display
*output_bytes_display
*output_bytes_per_sec_display

For what it's worth, I tried removing the end_time column and leaving time_taken_display in, and it errored out the same way, indicating time_taken_display being the culprit.

As usual, the Oracle documentation I found on global_names wasn't much help. Thanks in advance.

Answer 1

Ora-02069 When Using a Local Function While Updating a Remote Table (Doc ID 342320.1)

You need a valid support contract associated with your Oracle account to be able to view the above.

Here is an unofficial version:

http://lintzyuan.blogspot.com/2014/10/procedure-remote-database-dml-ora-2069.html

Because of a limitation, it is not possible to use a local function when doing a dml operation on a remote table .When this is attempted, the ora-2069 is raised.

You can check the definition of V$RMAN_BACKUP_JOB_DETAILS in V$FIXED_TABLE_DEFINITON. The columns you listed are function calls:

...
sys.dbms_rcvman.num2displaysize(output_bytes) output_bytes_display,
...
sys.dbms_rcvman.num2displaysize(output_bytes_per_sec) output_bytes_per_sec_display,
...
sys.dbms_rcvman.sec2displaytime(elapsed_seconds) time_taken_display,
...

Solution from above link:

These are possible workaround to avoid the ora-2069 error in the described scenario

1. Use global_names=true. This can be done on a session basis: "alter session set global_names=true". If you are having problems getting this to work, it is no doubt a configuration problem. Please open a separate TAR for this if you can't figure it out.
2. Put the function to be used at the remote site.
3. Put a wrapper function at the remote site which calls the actual function over a database link back to the local site.
4. Include in the "from" the "dual" table. You'll have a Cartesian product (with dual) and the functions will be applied in the calling side, hence some performance issues can be raised.

• lightwing: Thanks for that info! Looks like we want V$FIXED_VIEW_DEFINITION, though. At least in 19c, V$FIXED_TABLE_DEFINITON doesn't seem to be a valid view.