3 Replies Latest reply: Dec 2, 2010 7:47 AM by 761190 RSS

    i want list all query fired in my database

    816026
      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
          Dbb
          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
            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
              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.