Need script to see DML/DDL activities in all tables

[samdba]

Hi,

We are on Oracle 8.0.5.

I am looking for some script which shows DML,DDL activities in the database within all tables/objects in a specific time frame.

Example: Betw 2:00 PM to 3:00 PM, which are the tables in which DML transactions have been made. Also if possible, in terms of number of blocks/extents increased.

Thanks,

[marist89]

Hmm, since you are on 8.0.5, you can't use logminer.

You could set sql_trace on for the instance during that time frame. That might give you the DML you are looking for.

[ocpwannabe]

cant u use auditing for this?

[samdba]

Jeff,

sql_trace is set to true in init.ora. Now, how can I check the DML out of it?

[samdba]

There are thousands of tables and I don't want to increase any other overhead due to Auditing.

[gpsingh]

1) Enable Auditing - heavy overhead
2) Enable SQL_trace - lots and lots of trace files.
3) dump the redlog file /archive log file for the period.-- need to know internal structures.

[marist89]

Originally posted by samdba
Jeff,

sql_trace is set to true in init.ora. Now, how can I check the DML out of it?

see steps 2 and 3 of this link: http://otn.oracle.com/docs/products/...4_str.htm#8760

[samdba]

dump the redlog file /archive log file for the period.-- need to know internal structures.

- Infact, my original question is because, I want to know why lot of 100M Arch log files are getting generated. What makes these logs generated every 3-4 minutes inspite of low number of users and transactions.

[gpsingh]

Then u should firstr run utlbstat.estat to find out the
amount of redo generated,rollback generated , checkpoints done.

[samdba]

gpsingh,

I have already done that.

Is there any system table which stores block level information showing blocks inserted, updated, deleted for a particular table/index or datafile?