Denmark,
Every 5 seconds?? That's going to put an awful load on your database, dont'cha think? That's also going to be a very large file. We tried this a few months ago when jrun was acting suspect and we were getting a bunch of zombies. We could barely make sense of the log files. Lucky for me I had a sysadmin/perl hacker to write something that searched the log files appropriately. I don't have that code.
Are you looking for current/last executed sql statement? That's what this will give you. You may have to work it to exclude your current statement.
You can schedule it to run on your host (unix or windoze) like this
sqlplus user/pw@sid < sessql.sql >> sessql.log
set linesize 2000
set pagesize 2000
column username format a15
column s.sid format 99999
column s.serial# format 99999
column client_program format a12
column sql_text format a85
select
s.username,
s.sid,
-- s.machine,
t.sql_text
from v$session s, v$process p, v$sqltext t
where s.username is not null
and p.addr = s.paddr
and t.address = s.sql_address
order by 1,2,t.piece
/
EXIT
/
HTH
Lisa Koivu
Vikings Fan and DBA
Ft. Lauderdale, FL, USA
-----Original Message-----
From: Denmark Weatherburne [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 3:27 PM
To: Multiple recipients of list ORACLE-L
Subject: How to loop call to SQL script in PL/SQL...
Hi DBA's & Developers,
I first tried to call a batch file which calls SQL*Plus and executes an SQL
script from within Perl, but I had no success.
Perhaps I'm taking the wrong approach.
I'm sure it can be done using PL/SQL.
I want to run an SQL script repeatedly every 5 seconds throughout the day to
capture the SQL statements that are being parsed by the Oracle engine and
spool the results to a disk file.
I would appreciate your help in sending me the PL/SQL code to accomplish
this task.
I don't have mush experience using PL/SQL, but I am prepared to learn.
Thanks in advance for the help.
Regards,
Denmark Weatherburne
"Knowledge is power, but it is only usefule if it is shared!"
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denmark Weatherburne
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
