DB Activity
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DB Activity

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question

    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!
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Mar 2002
    Posts
    171
    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.


  3. #3
    Join Date
    Feb 2001
    Posts
    290
    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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    You can use logminer to extract info from log files to see the insert,update and delete transaction on ur database.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Nov 2000
    Posts
    416
    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?
    An ounce of prevention is worth a pound of cure

  6. #6
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    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.

  7. #7
    Join Date
    Feb 2001
    Posts
    290
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width