Oracle RDS Tracing ORA errors

Hi Everyone, 

One of the developer came and asked to find the sql which is causing the ORA error in his custom application. He was accessing the URL and he was getting 'ORA-00942' error in the screen.

  If this was on on premise DB, i would have run below statement to enable system wide event for the error ORA-00942. This statement will capture the 942 errors. 

  SQL> alter system set events '942 trace name errorstack level 1'; 

  System altered. 

  SQL> 

In RDS Oracle You have to use below to turn on the events

SQL> SET SERVEROUTPUT ON SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(942,3); Setting system event 942 with: alter system set events '942 errorstack (3)' PL/SQL procedure successfully completed.
I have asked the developer to reproduce the problem. And to turn off I have used below commands.

SQL> SET SERVEROUTPUT ON SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(942); Unsetting system event 942 with: alter system set events '942 off' PL/SQL procedure successfully completed.

Typical revoke would be below on non RDS

 alter system set events '942 trace name errorstack off';

You can catch the trace file on GUI log section, or in cloud watch logs if you have exported.

On Logs and Events tab, narrow down with 'trace/HOTALPA_ora' - HOTALPA is DB name and sort by timestamp.

then open the file.

Happy Sharing. !!! As usual leave your comments.