[racdba] Re: Tempspace usage by Instance
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'racdba@xxxxxxxxxxxxx'" <racdba@xxxxxxxxxxxxx>
- Date: Thu, 21 Jul 2005 09:23:19 -0700
KG,
I have this script for a single instance - modify as required:
$ cat who_is_sorting.sql
set pages 100
column tablespace format a7 heading "Tblspce"
column extents heading "Exts" format 999
column mb_used heading "MBUsd" format 99999
column tot_extents heading "TotExt" format 99999
column segtype heading "Type" format a9
column username heading "DB/OS User" format a19
column spid heading "OS pid" format 999999
column SIDSERAIL heading "SID SERAL" format a10
select /*+ RULE */ t1.tablespace, extents,
sum( t1.blocks * to_number( t3.value ) ) / 1024 /1024 mb_used
, sum( t1.extents ) tot_extents, t1.segtype,
t2.username || '/' || t2.osuser username, t2.SID ||','||
t2.serial# SIDSERAIL,
t4.spid
from v$sort_usage t1, v$session t2, v$parameter t3, v$process t4
where t1.SESSION_ADDR = t2.SADDR and t3.name = 'db_block_size'
and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
-- and t2.status = 'ACTIVE'
and t1.extents > 1
group by t1.tablespace, t2.username, t2.osuser, t2.machine, t2.schemaname,
t2.program, t2.SID ||','|| t2.serial#, t4.spid, t1.segtype,
segfile#, extents
;
John
-----Original Message-----
From: racdba-bounce@xxxxxxxxxxxxx [mailto:racdba-bounce@xxxxxxxxxxxxx] On
Behalf Of K Gopalakrishnan
Sent: Thursday, July 21, 2005 5:00 AM
To: racdba@xxxxxxxxxxxxx
Subject: [racdba] Tempspace usage by Instance
Guys:
I am trying to findout the tempspace usage by Instance level. I think I can
get it by joining gv$ sort_segment and sort_usage. Anyone have a ready made
script to tell what % of temp space is CONSUMED by each instance..
Thanks
--
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
Other related posts:
