Solved

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

Posted on 2012-03-13
13
734 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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

747 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

12 Experts available now in Live!

Get 1:1 Help Now