This discussion is archived
3 Replies Latest reply: Dec 2, 2010 5:47 AM by 761190 RSS

i want list all query fired in my database

816026 Newbie
Currently Being Moderated
hi friends,
am facing some problems,
am having a db server and 10 client machine.
without my knowledge somebody firing some database query from client machine.
is ther any way to get who fired that qury at wht time and which query?

if there is any way tell me in detail

thanks
thomas
  • 1. Re: i want list all query fired in my database
    704135 Expert
    Currently Being Moderated
    In google you can find some many ways to ask the V$SQL view, but to control unauthorized access you can activate the BBDD audit.
  • 2. Re: i want list all query fired in my database
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,


    I don't know your Oracle Release but, for instance, in 11.1 you have many Audit features.

    You'll find enclosed a link which explain how to Audit the access on the Database, you'll find also there a way to Audit the Statements:

    http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm#insertedID0


    Hope this help.
    Best regards,
    Jean-Valentin
  • 3. Re: i want list all query fired in my database
    761190 Journeyer
    Currently Being Moderated
    to view sql _text

    SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

    System altered.

    SQL> SHUTDOWN
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP

    Then with dba users run

    BEGIN
    DBMS_FGA.ADD_POLICY(object_schema => 'schema_name',
    object_name => 'table_name',
    policy_name => 'AUDIT_123',
    audit_condition => 'SYS_CONTEXT (''USERENV'',''IP_ADDRESS'')!= ''10.0.0.0''', -- here you can set which ip you want to audit or which you dont want audit
    …enable => TRUE);
    END;

    your audit information are on table dba_fga_audit_trail.

    note: information may take to much information and space on disk. because FGA audit all transactions on your system.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points