We help IT Professionals succeed at work.

How to see update and insert statements that were executed - Oracle 9i?

Hope4U
Hope4U asked
on
Hi:

I am a newbie and I have an oracle database with a couple of hundred tables that an application connects to.  I want to know if there is an easy way that I can see what tables are updated (including inserts) when I perform certain actions through the application?

Information I would like to recover:
(i) What row(s) was inserted or updated in in each table -e.g the 'update' or 'insert' statement actually submitted.
(ii) If possible, the 'select' statements also that would of been executed.

Please I would like the easiest way to achieve the above and one with details that I could follow.

Thanks
Comment
Watch Question

Top Expert 2011

Commented:
I could be wrong but if you are doing this through an application, then you have to insert the code that displays the insert or update statements either before hitting the db or after.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
make sure the "audit_trail" parameter is set to "DB"


audit select table, update table, insert table, delete table;

then look in dba_audit_trail to see the activity
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
turning on auditing for all DML will likely generate lots of records.

To turn it off,  use NOAUDIT

noaudit select table, update table, insert table, delete table;

or set audit_trail to NONE or FALSE
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
for more information, see the SQL Reference for AUDIT/NOAUDIT

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_48a.htm#2059074
I believe the easiest way to monitor the statements your application is issuing to the database is by monitoring the SGA/Trace. (V$SQL table:  http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm)
I've done this is the past on an EBS instance and gave me crucial information as a dev.

Dan.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
v$sql will only show you statements that are currently cached in the pool.

Author

Commented:
[1]To 'sdstuber':  

I am not getting the whole statement (e.g. including the where condition) after I did these steps:

AUDIT ALL BY fireid BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY fireid BY ACCESS;
AUDIT EXECUTE PROCEDURE BY fireid BY ACCESS;

SELECT username, owner, obj_name, action_name FROM dba_audit_trail WHERE owner = 'fireid';

==>
username | owner | obj_name | action_name
fireid|fireid|user_tab|SELECT


[2] To 'v$sql':
Is there a way that I can narrow down the results to after a certain time?

[3] Thanks for your help thus far.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
1 - look in the sql_text column of dba_audit_trail

2 -no, not in a reliable way.  v$sql only contains queries that are currently in the cache

3 - you're welcome

Author

Commented:
Hi:

i am unable to get the 'sql_text' column to be present in the dba_audit_trail view.  I have read on the internet and tried this value 'audit_trail=DB_EXTENDED' in my init.ora file, buy I got the following error.

SQL> Connected to an idle instance.
SQL> ORA-01078: failure in processing system parameters
LRM-00121: 'DB_EXTENDED' is not an allowable value for 'audit_trail'
SQL> Disconnected

I think this error is because I am using 9i.

What to do, to get that column 'sql_text' to appear?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
oh sorry, I forgot you were on 9i.  that is the problem.

you need 10g for extended audit_trail information.


you could use dbms_fga to write to dba_fga_audit_trail, but it will only capture select statements.


for 9i, to capture updates and inserts, you'll either have to trace and then dig through the trace files of each session or use log miner to dig through the transaction logs.

Author

Commented:
Hi sdstuber:

Was caught up with another project, sorry for the delay.  

(i) Do the trace files have a naming format and a standard location?
(ii) Is the 'log miner' an application?  If so, could you give me the name of it or a URL to the supplier?

Thanks
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
trace files will be in the path defined by the background_dump_dest  init parameter

logminer is a tool that comes with the oracle database
http://docs.oracle.com/cd/B10501_01/server.920/a96521/logminer.htm

Author

Commented:
Thanks