3 Replies Latest reply on Dec 2, 2010 1:47 PM by 761190

    i want list all query fired in my database

      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

        • 1. Re: i want list all query fired in my database
          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

            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:


            Hope this help.
            Best regards,
            • 3. Re: i want list all query fired in my database
              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

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

              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.