Tracking SQL queries and rows operated on for documentation/audit

Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes?

For example, in deleting duplicate rows from a table I use the following SQL which gives me a sequence of SQL that I can refer back to later in case of issues with the deletion.

select 'delete from table1 where rowid=''' || rowid || ''' and column1='''
|| column1 || ''' and column2=''' || column2 || '''' from table1 a where
rowid > (select min(rowid) from table1 b where a.column1 = b.column1 and
a.column2 = a.column2);

However this takes longer to build then just issuing the queries outright. I work across a lot of different tables which I don't own so adding triggers is unfortunately not an option.

Oracle provides in-built auditing tools - consider using them instead of hand-rolling out these things.

Oracle Base provides some basic info as to how to get started on Auditing:


Auditing can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }


The following list provides a description of each setting:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.


To enable auditing to database audit trail enable auditing to db

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

System altered.


Shutdown & restart the db

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.


Now to audit SELECTS, INSERTS, UPDATES, DELETES by user cube do this:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY cube BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY cube BY ACCESS;


The audited logs can be brought up by querying DBA_AUDIT_TRAIL

Further reading:

• Auditing

• Fine grained auditing

• Configuring and administering auditing

StackExchange Database Administrators Q#10008, answer score: 3