Tracking SQL queries and rows operated on for documentation/audit

Question

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.

Answer 1

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

Answer 2

While auditing is the most comprehensive solution, it may be overkill in some situations. For those times you might consider just logging your session. In SQLPlus for example you could change your login script to turn spooling on like this.

set define on
variable vspoolname Varchar2(1000);
variable dbname     Varchar2(30);
begin
  :vspoolname := to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS') || '__';  
  :dbname := UPPER(TRIM(sys_context('USERENV', 'DB_NAME')));
  :vspoolname := :vspoolname || :dbname || '__' || user;
end;
/
column mc new_value vspoolname noprint
select :vspoolname mc from dual;
spool &vspoolname..txt append
set define off

Then you would simply select the data before you delete it so that it would all be in the spool file.

SELECT * FROM table1 WHERE ...;
DELETE FROM table1 WHERE ...;

Then turn spooling off.

spool off;