RE: What is on Temp tablespace....

From: Elliott, Patrick <Patrick.Elliott_at_bestbuy.com>
Date: Thu, 18 May 2000 12:26:55 -0500
Message-Id: <10501.105989@fatcity.com>

I looked up the definition of v$sort_usage and got the following. You may be able to code a query like this in 7.3.4, but only if the x$ktsso table exists.

select username USER, ktssoses SESSION_ADDR, ktssosno SESSION_NUM, prev_sql_addr SQLADDR,

       prev_hash_value SQLHASH, ktssotsn TABLESPACE,
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') CONTENTS,
       ktssofno SEGFILE#, ktssobno SEGBLK#, ktssoexts EXTENTS, ktssoblks

BLOCKS, ktssorfno SEGRFNO#
  from x$ktsso, v$session
 where ktssoses = v$session.saddr
   and ktssosno = v$session.serial#
   and inst_id = USERENV('Instance')

Please let me know if this works.

> -----Original Message-----
> From: mleith_at_bradmark.co.uk [SMTP:mleith_at_bradmark.co.uk]
> Sent: Thursday, May 18, 2000 9:30 AM
> To: Patrick.Elliott_at_bestbuy.com; ORACLE-L_at_fatcity.com
> Subject: RE: What is on Temp tablespace....
>
> Is there anyway to return who is using a certain number of extents under
> 7.3.4? As V$sort_usage just isn't there.
>
> I would like like to return, username, machine name, number of extents
> used, process. I'm sure this cant be as hard as it seems, but nobody I
> have talked to has a solution.
>
> Any of you gurus care to comment?
>
> TIA
>
> Mark.
>
> -----Original Message-----
> From: MIME :Patrick.Elliott_at_bestbuy.com Sent: 16 May 2000 23:56
> To: ORACLE-L_at_fatcity.com
> Subject: RE: What is on Temp tablespace....
>
> The temporary tablespace remains full until shutdown or startup. The
> extents are not allocated or deallocated as they used to be in older
> versions, they are just reassigned to temporary segments as needed. You
> can
> query v$sort_usage joining it to v$session to determine who is actually
> using the space.
>
> select s.osuser,
> s.process,
> s.username,
> s.serial#,
> sum(u.blocks)*vp.value/1024 sort_size
> from v$session s,
> v$sort_usage u, v$parameter vp
> where s.saddr = u.session_addr
> and vp.name = 'db_block_size'
> and s.osuser like '&1'
> group by s.osuser, s.process, s.username, s.serial#, vp.value
> /
>
> > -----Original Message-----
> > From: Aldi Barco [SMTP:ipal_at_HOTMAIL.COM]
> > Sent: Tuesday, May 16, 2000 11:59 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: What is on Temp tablespace....
> > > Hi list,
> > > There has been 3.8 GB used space on Temp tablespace for a day long.
> > How to know what is currently Temp tablespace contain ?
> > Tia.
> > > Aldi
> > oracle DBA
> > >
> ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> > > -- > Author: Aldi Barco
> > INET: ipal_at_hotmail.com
> > > 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.com (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). You may
> > also send the HELP command for other information (like subscribing).
> -- Author: Elliott, Patrick
> INET: Patrick.Elliott_at_bestbuy.com
>
> 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.com (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). You may
> also send the HELP command for other information (like subscribing).
Received on Thu May 18 2000 - 12:26:55 CDT