Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-13
13
Medium Priority
?
767 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 29

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 74

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 74

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

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 74

Expert Comment

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

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 74

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 74

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 74

Accepted Solution

by:
sdstuber earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

927 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