RE: What is on Temp tablespace....

From: <mleith_at_bradmark.co.uk>
Date: Thu, 18 May 2000 15:29:45 +0100
Message-Id: <10501.105961@fatcity.com>

Is there anyway to return who is using a certain number of extents under=20= 7=2E3=2E4? As V$sort_usage just isn't there=2E

I would like like to return, username, machine name, number of extents used,=20= process=2E I'm sure this cant be as hard as it seems, but nobody I have talked=20= to has a solution=2E=20=

Any of you gurus care to comment?

TIA=20= Mark=2E

-----Original Message-----

From:	MIME :Patrick=2EElliott_at_bestbuy=2Ecom=20=
Sent:	16 May 2000 23:56
To:	ORACLE-L_at_fatcity=2Ecom
Subject:	RE: What is on Temp tablespace=2E=2E=2E=2E

The temporary tablespace remains full until shutdown or startup=2E The extents are not allocated or deallocated as they used to be in older versions, they are just reassigned to temporary segments as needed=2E You can query v$sort_usage joining it to v$session to determine who is actually using the space=2E

select s=2Eosuser,
       s=2Eprocess,
       s=2Eusername,
       s=2Eserial#,
       sum(u=2Eblocks)*vp=2Evalue/1024 sort_size
from   v$session      s,
       v$sort_usage   u,        v$parameter    vp

where s=2Esaddr =3D u=2Esession_addr
  and vp=2Ename =3D 'db_block_size'
  and s=2Eosuser like '&1'
group by s=2Eosuser, s=2Eprocess, s=2Eusername, s=2Eserial#, vp=2Evalue /

> -----Original Message-----
> From: Aldi Barco [SMTP:ipal_at_HOTMAIL=2ECOM]
> Sent: Tuesday, May 16, 2000 11:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: What is on Temp tablespace=2E=2E=2E=2E
> > Hi list,
> > There has been 3=2E8 GB used space on Temp tablespace for a day long=2E
> How to know what is currently Temp tablespace contain ?
> Tia=2E
> > Aldi
> oracle DBA
> > ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www=2Ehotmail=2Ecom
> > -- > Author: Aldi Barco
> INET: ipal_at_hotmail=2Ecom
> > 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: ListGuru_at_fatcity=2Ecom (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)=2E You may
> also send the HELP command for other information (like subscribing)=2E
-- Author: Elliott, Patrick
  INET: Patrick=2EElliott_at_bestbuy=2Ecom

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: ListGuru_at_fatcity=2Ecom (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)=2E You may also send the HELP command for other information (like subscribing)=2E Received on Thu May 18 2000 - 09:29:45 CDT