Martin Bálint

Personal blog of IT professional

Oracle PL/SQL developer
PHP developer
Linux administrator

Jul 12 2013

Log failed statements on oracle database

If your client is getting errors while executing statements on Oracle database, and you want to see which statements are failing, try this:

Check that audit is enabled:

SQL> show parameter audit;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracle/admin/ORADB1/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
 Set audit_trail to DB_EXTENDED so you can see SQL statements, restart is required.
SQL> alter system set audit_trail=db_extended scope=spfile;
System altered.

SQL> shutdown immediate;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Now, set audit options

audit select table by access whenever not successful;
audit insert table by access whenever not successful;
audit update table by access whenever not successful;
audit delete table by access whenever not successful;
audit execute procedure by access whenever not successful;

You should then see failed statements using

select timestamp, os_username, username, owner, obj_name, action_name, returncode, sql_text, sql_bind from dba_audit_trail order by timestamp;

 

Leave a reply

Your email address will not be published. Required fields are marked *

*