Solved

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

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

 
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
 

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

777 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