0

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.

1 Answer 1

0

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.
1
  • 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.
    – lightwing
    Jul 18, 2022 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.