Search This Blog

Total Pageviews

Tuesday, 9 November 2010

Oracle Temp segment usage per session

Oracle Temp segment usage per session

set linesize 300 pagesize 300
col kill for a15
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username,  c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space from  gv$session a,gv$sort_usage b, gv$process c, dba_tablespaces d
where    a.saddr  = b.session_addr
and      a.inst_id  = b.inst_id
and      a.paddr  = c.addr
and      a.inst_id  = c.inst_id
and      b.tablespace=d.tablespace_name
group by a.username, a.osuser, ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.sql_id,c.spid,b.tablespace, a.status;


set linesize 300 pagesize 300
col kill  for a15
col sql_text for a60 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, S.username,s.sql_id,T.blocks * TBS.block_size/1024/1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text from gv$sort_usage t, gv$session s, gv$sqlarea q, dba_tablespaces tbs
where 1=1
and t.session_addr = s.saddr
and t.inst_id = s.inst_id
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
order by s.sid;


set linesize 300 pagesize 300
col kill for a15
col username for a20
col program for a20
col tablespace for a12
select * from (select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.sql_id,s.PREV_SQL_ID,u.tablespace, s.username, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,i.inst_id,i.host_name from gv$session s, gv$sort_usage u ,gv$instance i
where s.saddr=u.session_addr
and u.inst_id=i.inst_id
and s.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by mb desc) a
where rownum <20;

No comments:

Oracle DBA

anuj blog Archive