Solved

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

Posted on 2012-03-13
13
738 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Hope4U
13 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37715537
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37715540
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37715547
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37715564
for more information, see the SQL Reference for AUDIT/NOAUDIT

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_48a.htm#2059074
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 37728739
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37728995
v$sql will only show you statements that are currently cached in the pool.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Hope4U
ID: 37739832
[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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37740426
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
0
 

Author Comment

by:Hope4U
ID: 37742763
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?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37742952
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.
0
 

Author Comment

by:Hope4U
ID: 37855906
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37855990
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
0
 

Author Closing Comment

by:Hope4U
ID: 37856345
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
Oracle - SQL Script to test for bill dates 3 or more times in SAME WEEK? 11 45
unable to get sorting resultset 15 69
help on oracle query 5 34
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now