1

We have a DB link between 2 Oracle 11.2 databases.

On the production system, we noticed that when an UPDATE statement of a remote table fails in an PL/SQL procedure (started as a scheduled job), the error message is lost.

Example code:

UPDATE remote_table@our_db_link SET ...
exception
when others then
  senderror('Error: '|| sqlerrm || chr(10) || dbms_utility.format_error_backtrace || chr(10) || sys.DBMS_UTILITY.format_call_stack);

The senderror procedure saves the supplied text to a log file.

There we see:

Error: ORA-01536: space quota exceeded for tablespace 'TS1'
ORA-02063: preceding line from OUR_DB_LINK
ORA-06512: at "X_TEST.X_PACKAGE", line 64

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x89b0bca0       114  package body X_TEST.X_PACKAGE
0x7d5b27e8         1  anonymous block

This is what we get on the test system. On the production, the root cause is missing, so the text is:

Error: ORA-02063: preceding line from OUR_DB_LINK
ORA-06512: at "X_TEST.X_PACKAGE", line 64

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x89b0bca0       114  package body X_TEST.X_PACKAGE
0x7d5b27e8         1  anonymous block

The DB version is 11.2.0.4.

Any idea what could cause this?

5
  • Is it possible that, on the production system, the user does not have EXECUTE permission on the DBMS_UTILITY package?
    – MJH
    Dec 15 '16 at 21:53
  • @MJH No, because then the result of format_error_backtrace and format_call_stack would not be present in the log file. Dec 16 '16 at 10:54
  • Could you compare the code between test and prod for senderror procedure?
    – Raj
    Dec 20 '16 at 20:24
  • @DavidBalažic, do you have any updates for this issue? I faced with the same behavior but my understanding is that remote SQL query contains references to objects (especially packages) at origin side.
    – drnk
    Sep 12 '17 at 14:21
  • @drnk Sadly, no. Sep 13 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.