Skip to Main Content
  • Questions
  • Database Link Issues: Adding schema name raises ORA-02002: error while writing to audit trail ORA-00942

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Larry.

Asked: January 15, 2020 - 7:30 pm UTC

Last updated: April 02, 2020 - 3:26 am UTC

Version: 19c (19.0.0.0.0)

Viewed 1000+ times

You Asked

I have a database link that connects to a databases that is running 11g (11.2.0.4.0).

The database it is created in was recently upgraded to 19c. After the upgrade, I can no longer access remote tables using fully qualified table names.

Prior to upgrade:
 select * from schema.table@link;

and
select * from table@link;


both worked fine.

After the upgrade:
select * from table@link;

works

select * from schema.table@link;
gives the following error:
ORA-02002: error while writing to audit trail
ORA-00942: table or view does not exist
02002. 0000 - "error while writing to audit trail"
*Cause:
*Action:


Has anyone seen this? My DBA assures me that the tablespaces all have plenty of room.

The database that was upgraded is an oracle RDS instance.

Thanks.

and Chris said...

It sounds like you're hitting the known bug:

Bug 30464250 - Select Statement Fails With ORA-2002 ORA-942 With Audit Select Any Table

This affects 12.2, 19.3, & 19.5. There are no patches at the moment.

The workaround is currently:

Either of the following may workaround the error.

- Do not qualify the table name with schema name.
- Disable auditing by using following statement:
NOAUDIT SELECT ANY TABLE by <username>;

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Workaround almost worked

A reader, April 01, 2020 - 5:46 pm UTC

The workaround at the bottom did not work as directed. However, Setting NOAUDIT SELECT ANY TABLE for the whole database worked. When I set it for just my user. I still get the error.
Connor McDonald
April 02, 2020 - 3:26 am UTC

Thanks for the feedback

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database