Lee,
Did you come up with a workable solution for this?
Preliminary testing of the v$session_longops view gave me some weird data
returned in the in the start_time column:
SQL> edit
Wrote file afiedt.buf
1 select l.sid,
2 l.serial#,
3 s.username,
4 l.start_time,
5 l.time_remaining,
6 l.elapsed_seconds
7 from v$session_longops l,
8 v$session s
9* where l.sid = s.sid
SQL> /
SID SERIAL# USERNAME START_TIM
TIME_REMAINING ELAPSED_SECONDS
---------- ---------- ------------------------------ --------- -------------
- ---------------
12 49527 SYS 17-OCT-01
0 9
13 62085 SYS
0 435240985
13 62085 SYS 16-OCT-01
0 12
14 2710 SYS 16-OCT-01
0 19
17 408 SYS 15-MAR-92
0 302578324
17 408 SYS
0 435241415
6 rows selected.
Now, there are no users on this system, and if I were to put this in to
practice (I'm thinking of doing a UDC for this) I would put in a where
clause discounting the SYS user, and maybe add in a join with v$sqltext to
get the offending statements.. Do you get a proper data return for users
other than SYS? I haven't got a database to check with users on against
ATM..
Looks like I've got the longest running database in the world :P
Cheers
Mark
-----Original Message-----
Lee - lerobe
Sent: Wednesday, October 17, 2001 09:25
To: Multiple recipients of list ORACLE-L
Thanks to everyone for their responses. Certainly given me something to work
on.
Lee
-----Original Message-----
Sent: 16 October 2001 18:45
To: Multiple recipients of list ORACLE-L
You could fool around with:
-- currently running sql.sql
select sql_text, sid, s.serial#, last_call_et, hash_value, sa.buffer_gets,
sa.executions, s.module, p.spid
from v$session s, v$sqlarea sa, v$process p
where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and s.status = 'ACTIVE'
and s.paddr = p.addr
and s.audsid != 0
and sql_text not like 'select sql_text%'
and sql_text not like 'begin%tmsrv%'
order by 4 desc;
or, another of Paul's greatest hits,
-- waiting sql.sql
select event, sql_text, s.sid, serial#, s.last_call_et;
from v$session s,v$session_wait w, v$sqlarea sa
where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and w.sid = s.sid
and w.event != 'Null event' and
w.event != 'rdbms ipc message' and
w.event != 'pipe get' and
w.event != 'virtual circuit status' and
w.event not like '%timer%' and
w.event not like 'SQL*Net message %'
and s.audsid != 0
order by last_call_et
Note: last_call_et is estimated only, but generally reliable, and is
expressed in seconds. The output of these look great in Benthic's Golden,
but YMMV in sqlplus et.al.
HTH,
Paul
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 16, 2001 12:45 PM
Aha, this view we have. Time to start digging about.
Thanks again Kirti
Regards
Lee
-----Original Message-----
Sent: 16 October 2001 16:35
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
BTW, view v$session_longops is also available on some select versions of
8.0.5.x
- Kirti
> -----Original Message-----
> From: Deshpande, Kirti
> Sent: Tuesday, October 16, 2001 10:30 AM
> To: '[EMAIL PROTECTED]'
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: Capturing long running SQL
>
> If running 8i, you may want to check the view v$session_longops.
> May be there is some useful info.
>
> Regards,
>
> - Kirti Deshpande
> Verizon Information Services
> http://www.superpages.com
>
> -----Original Message-----
> From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, October 16, 2001 10:16 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Capturing long running SQL
>
> Erm.... thanks for the reply BUT, I know how to use dbms_job etc.
> what I
> dont know is what SQL to use to get sql that has been running for
> longer
> than an hour.
>
> Thanks for the reply anyway.
>
> Lee
>
>
>
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Vallee
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
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).
