Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Unhappy Unanswered: Finding out db hits in Oracle.

    Hi experts,

    I have a Java program which hits (select/update operations) various tables in my Oracle db(11g). I want to find out, at run time , which all tables are being hit and the number of hits. I want to know if there is SQL query which I can execute while the Java program is being, run to get this information. Assume there could be some system tables which would keep track of this. Or should I have write a trigger for this ?

    Please advise.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Or should I have write a trigger for this ?
    Trigger is one solution.
    Or AUDIT is another
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    8

    First try.

    Thanks for the info.

    I ran the following query on to retrieve all the possible information.

    select 'standard audit', sessionid,
    proxy_sessionid, statementid, entryid, extended_timestamp, global_uid,
    username, client_id, null, os_username, userhost, os_process, terminal,
    instance_number, owner, obj_name, null, new_owner,
    new_name, action, action_name, audit_option, transactionid, returncode,
    scn, comment_text, sql_bind, sql_text,
    obj_privilege, sys_privilege, admin_option, grantee, priv_used,
    ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite,
    logoff_dlock, session_cpu
    from
    dba_audit_trail;

    Found that sql_text, which could have given the table info, gives null value.

    Should I need to enable any AUDIT setting for this info to be captured ?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Should I need to enable any AUDIT setting for this info to be captured ?
    Yes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    You could also:

    1) Trace the session -- or --
    2) Generate ASH report for that session (easy to do from the EM db console)
    3) Or using EM dbconsole, simply monitor all activity for that session.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2009
    Posts
    8

    Smile Got it working.

    Here are the steps to enable AUDIT for a user in Oracle DB.
    1. Check the AUDIT settings
    SQL> SHOW PARAMETER AUDIT

    NAME TYPE VALUE
    ------------------------------------ ----------- --------------------------
    audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
    \DB10G\ADUMP
    audit_sys_operations boolean FALSE
    audit_trail string NONE

    2. Change it to capture all operations
    SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

    3. Restart server

    4. Enable audit for all operations for user dbhitstest

    AUDIT ALL BY dbhitstest BY ACCESS;
    AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY dbhitstest BY ACCESS;
    AUDIT EXECUTE PROCEDURE BY dbhitstest BY ACCESS;

    Now you can run the programs which hots the db dbhitstest.

    Query the audit_trail view.

    select sessionid,
    statementid, entryid, extended_timestamp, global_uid,
    username, client_id, os_username, userhost, os_process, terminal,
    instance_number, owner, obj_name, null, new_owner,
    new_name, action, action_name, audit_option, transactionid, returncode,
    scn, comment_text, sql_bind, sql_text
    from
    dba_audit_trail where TIMESTAMP > '26-MAY-11';
    and username='dbhitstest';

    Ref: ORACLE-BASE - Auditing in Oracle 10g Release 2
    Last edited by Roshith; 05-30-11 at 10:07.

Tags for this Thread

Posting Permissions

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