Re: Auditing SELECT statement contents from database side

From: John Morais <jmorais_at_rochester.rr.com>
Date: Sat, 09 Jun 2001 22:40:31 GMT
Message-ID: <zBxU6.2829$3y3.474738@typhoon.nyroc.rr.com>

They dont show you the bind variables or the variables, you can also sweeo the SGA to get all SQL executed
ie ..

/* This script selects the SQL_text being run by a particular */
/* user/SID. The script includes a time/date stamp in the spool */
/* file, and requires the username and SID as entered data */
/* Created by: John Morais */
/* */
/* Creation Date: August 25, 1998 */
/* Last Updated: September 17,1998 */

prompt Enter Username
accept username char prompt 'USERNAME: ' prompt Enter SID
accept sid number prompt 'SID: '
prompt Enter filename for report file.
prompt If no report is desired, simply hit <Enter> accept spool_file char prompt 'Report File: '

spool &spool_file

ttitle off
set termout on
clear breaks
set pause off
set linesize 132
set echo off

set head off
select 'SQL TEXT FOR USER : &username FOR '|| to_char(sysdate,'mm-dd-yyyy hh24:mi') from dual;

    select

     sql_text "SQL"
     from v$sqltext a, v$session b

    where
    sid = &sid
    and b.sql_hash_value = hash_value
    and
    b.sql_address = address
    order by piece;
spool off
ttitle off

or

/* This script selects session use information for a particular SID */
/* It returns the username, oracle ID, statistic name and value */
/* Created by: John Morais */
/* Oracle System Performance Group */
/* Creation Date: April 1998 */

ttitle off
clear breaks
set pause off
set linesize 121
set pagesize 999
set tab on

prompt This script will return the Select SQL and the Statistics prompt Enter filename for report file.
prompt If no report is desired, simply hit <Enter> accept spool_file char prompt 'Report File: ' accept db char prompt 'Database Report is Running for : ' accept user char prompt 'Enter User You Wish to View : ' btitle SQL REPORT for &db for Parsing User &user

spool &spool_file

column agets      format 9990 heading "Avg. Logical|Reads per|Exec"
column exec       format 999990 heading "Number of|SQL Execs"
column dreads     format 999990 heading "Physical|Reads"
column bgets      format 999999990 heading "Logical|Reads"
column rproc      format 999999990 heading "Rows|Processed"
column sqlt       format a64 heading "SQL STATEMENT"
column hv         noprint
column use        noprint

break on agets on exec on dreads on bgets on rproc

select username "use",buffer_gets/executions "agets", b.sql_text "sqlt" ,

       a.executions "exec",a.disk_reads "dreads", a.buffer_gets "bgets",
       a.rows_processed "rproc",a.hash_value "hv"

  from v$sqlarea a, v$sqltext b, dba_users    where
   username not in ('SYS','SYSTEM','PATROL')    and
 ( username in ('&user')
  and
  user_id = parsing_user_id )
   and
   ( executions > 0

     and
      buffer_gets/executions > 30
       and
        InStr(Upper(a.sql_text), 'SELECT') > 0 )
         and
          a.address = b.address
           and
            a.hash_value = b.hash_value
      order by
      1,
      2,
      b.address,
      b.hash_value,
      b.piece
      ;

spool off
set tab off
clear breaks
set pause off
ttitle off

"TurkBear" <noone_at_nowhere.com> wrote in message news:v57tht8m8d5ppghc1g4se2391eg0d20kfn_at_4ax.com...

> Oracle has tools ( DBA Studio for one) that let you examine the last Sql
> executed by a user -
> Many 3rd party products ( like spotlight from quest software) let you

 examine a

> entire session's SQL..
>
> "Igor Oussoltsev" <usolcew_at_yahoo.com> wrote:
>
> >Good evening , friends
> >Can anybody help me?
> >I want to know answer for simple question. Is there any opportunities to
> >find out contents of SELECT's, performed by Oracle RDBMS users?
> >As far as I undestand, triggers won't help me, and AUDIT too.
> >Any ideas?
> >Thanks anyway,
> >Igor Oussoltsev,
> >Kaliningrad
> >
>

Received on Sat Jun 09 2001 - 17:40:31 CDT