Re: temporary space issue
From: ddf <oratune_at_msn.com>
Date: Tue, 8 Feb 2011 08:00:20 -0800 (PST)
Message-ID: <58775e58-b019-4786-a5ce-ea96e5f9377f_at_n36g2000pre.googlegroups.com>
On Feb 8, 9:50 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 08 Feb 2011 06:16:32 -0800, dba cjb wrote:
> > Is there a way I can get details of users/query/temp useage at time of
> > crash so that I can say eg Mr x ran a select from tab which used 1.5gig
> > & was trying to grab more space
>
> select s.username,
> ss.sid,
> s.tablespace,
> round(sum(s.blocks*t.block_size)/1048576,2) MB
> from v$sort_usage s,dba_tablespaces t,v$session ss
> where s.tablespace=t.tablespace_name
> and s.session_addr=ss.saddr
> group by s.username,ss.sid, s.tablespace
> order by 4 desc
>
> --http://mgogala.byethost5.com
Date: Tue, 8 Feb 2011 08:00:20 -0800 (PST)
Message-ID: <58775e58-b019-4786-a5ce-ea96e5f9377f_at_n36g2000pre.googlegroups.com>
On Feb 8, 9:50 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 08 Feb 2011 06:16:32 -0800, dba cjb wrote:
> > Is there a way I can get details of users/query/temp useage at time of
> > crash so that I can say eg Mr x ran a select from tab which used 1.5gig
> > & was trying to grab more space
>
> select s.username,
> ss.sid,
> s.tablespace,
> round(sum(s.blocks*t.block_size)/1048576,2) MB
> from v$sort_usage s,dba_tablespaces t,v$session ss
> where s.tablespace=t.tablespace_name
> and s.session_addr=ss.saddr
> group by s.username,ss.sid, s.tablespace
> order by 4 desc
>
> --http://mgogala.byethost5.com
He's using 10.2.0.x so V$TEMPSEG_USAGE is a better view to query:
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
David Fitzjarrell Received on Tue Feb 08 2011 - 10:00:20 CST