DB Activity

[farrokhp]

Hi Guys, I want to see what was the DML activities in databse during last 24 hours, can you provide me some script and/or guideline 1- how can I see how many insert / update , ... happened based on each or top 20 tables and also 2- which Stored Procedures calls and 3- what sql statments run. Probably I need couple of scripts to extract thses info, right ??? Thanks!

[dbafreak]

Try this:

Connect to SYS.

SQL>select sql_text from v$sql;

This gives all the recently executed SQL statements that are residing in the SGA. Hope it helps.

[mrvajrala]

we have two views to see the DML activity
1. v$SQLAREA
2. V$SQL_TEXT

you can get all the DML activities from v$SQL_AREA and corresponding text from v$SQL_TEXT view

Just have a glimpse on these views and you can be able to prepare your own script.

Thanks,
Madhu

[anandkl]

You can use logminer to extract info from log files to see the insert,update and delete transaction on ur database.

regards
anandkl

[farrokhp]

I checked V$sqlarea and V$Sql_text, not too helpful for me because it's huge and also I more need to see which Stored Proc calling and how many times and also insert per table, can you explain more?

[maachan]

Logminer can tell you how many rows were inserted, updated, or deleted, but does not tell you anything about stored procedures. The numbers of inserts, deletes and updates have no relation to how many statements were passed either so it may not be so helpful.

Should have turned on SQL TRACE.
Can't do it in hindsight.

[mrvajrala]

By Using the log miner you may identify the each individual transaction on the database, but It will not show you any stored procedure name in it. Infact stored procedure will also contain some sql statements ( Don't they ?? ).

So all you can get is simple SQL statements either by querying v$logminer_contents or v$sql.

Then you have to search these SQL's in the stored procedures, provided if you have a copy of them.

Well i have my database running on UNIX , and we have a directory , where i have all the packages and i search for some SQL , As and when i required in a package.

Hope U get it,
Madhu