Greetings. It is necessary to see all SQL expressions that are executed (executed) in all sessions. Rummaged through a bunch of information. Everywhere offer solutions that are only suitable for tracking in a particular session. Is it possible to somehow achieve the desired? I need to see a list with usernames and SQL statement text that the user executes. In advance, thank you.

  • Oracle Audit Trail ( alter system set AUDIT_TRAIL='db,extended' scope=spfile; )? - MaxU
  • @MaxU Is it possible to somehow pull out the information I need in C # code? - Denis Lolik
  • If you have a DBA role and you can reload the entire database (to enable auditing), then yes, you can - MaxU
  • @MaxU Thank you, I will pick - Denis Lolik

1 answer 1

UPDATE: you can use the new "features" (Oracle 12.1+) - Unified Audit Data Trail which combines:

  • SYS.AUD $ for the database audit trail,
  • SYS.FGA_LOG $ for fine-grained auditing,
  • DVSYS.AUDIT_TRAIL $ for Oracle Database Vault, Oracle Label Security,
  • and so on.

It also allows for conditions to be audited - examples of use

According to Oracle, they reworked the engine for Unified Audit Data Trail and now it should be faster and more productive ...

Previous answer:

Use Oracle AUDIT Trail :

Inclusion :

 SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;

After that, you need to restart the entire database ...

Enable auditing for user "TEST":

 SQL> audit all statements by TEST;

View audit:

 SQL> select * from sys.aud$ where userid='TEST';
  • Thank you, it works. One question remained: is it possible to somehow leave only those lines in this sample in which the SQL expressions are generated by the user, and not by the system? And then too many extra requests. - Denis Lolik
  • @Denis Lolik must be remembered that the inclusion of auditing database performance very strongly - Viktorov