Thanks to everyone for all the help.
-Original Message-
Sent: Monday, September 10, 2001 1:35 PM
To: Multiple recipients of list ORACLE-L
try this query:
SELECT s.username, s.serial#, s.sid, u.tablespace, u.contents, u.extents,
u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=
Since 8.X joining v$sort_usage and v$session. In 7.X
Metalink had an script that I enclose.
When the query is running, you can issue a query over
v$session_wait. Looking for the event 'db file
sequential read' where p1 includes all the files of
the temp tablespace.
Regards.
--- "Jenkins, Mic
Have you looked at v$sort_usage? (8i only)
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
Michael -
I've had similar problems (although the pain isn't high enough for me to get
in there and fix them yet). To do this I've set up a cron job to check
every 15 minutes. The script I run is:
set lines 100 pages 87
column username format a10
column tablespace format a6 heading 'TBSP'
col
try this query:
SELECT s.username, s.serial#, s.sid, u.tablespace, u.contents, u.extents,
u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr;
Lucy
On Mon, 10 Sep 2001, Jenkins, Michael wrote:
> Does anybody have a script that will tell you which session_ids are using
>
Does anybody have a script that will tell you which session_ids are using
temp and how much they are using? We occasionally get runaway sessions that
don't release temp and this would allow us to easily locate the offending
session.
I've already checked OraMag and used google with no significant