/**************************************************************************/ 
/*                                                                        */ 
/*                         c a p t _ s q l . s q l                        */
/*                                                                        */ 
/* deamon process to capture the sql which is running against the DBMS    */
/*    and save those queries which are not duplicates (strip off the      */
/*    duplicates)                                                         */
/*                                                                        */ 
/**************************************************************************/ 
/*                     M o d i f i c a t i o n    L o g                   */ 
/*                                                                        */ 
/*   Date    Programmer       Description of changes                      */ 
/* --------  ---------------  ------------------------------------------- */ 
/* 08/23/94  R.Kupcunas       Original code                               */ 
/*                                                                        */ 
/**************************************************************************/ 
set pagesize 1000;
set linesize 132;

break on "TIME" on "OSUSER" on "USERNAME" on "SID" on "SERIAL";

spool capt_sql.rslt;

/* *************************** */
/* Look at the SQL statements: */
/* *************************** */

       select 	c.first_load_time                               "TIME",
                substr(a.osuser, 1, 9)                          "OSUSER",
                substr(a.username, 1, 9)                        "USERNAME",
                lpad(substr(to_char(a.sid), 1, 5), 5, ' ')      "SID",
                lpad(substr(to_char(a.serial#), 1, 5), 5, ' ')  "SERIAL",
                lpad(substr(to_char(b.piece), 1, 2), 2, ' '),
                b.sql_text
       from 	v$session   a,
                v$sqltext   b,
                v$sqlarea   c
       where a.sql_address     = b.address       and
             a.sql_hash_value  = b.hash_value    and
             a.username <> 'SYS'                and
             a.osuser         != ' '             and
             a.osuser   <> 'slux002'                and
             c.address         = a.sql_address   and
             b.piece           < 100
       order by 1 asc, 2 asc, 3 asc, 4 asc, 5 asc, 6 asc;

spool off;

commit;

