RE: TEMP space in Oracle 817

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Wed, 23 Apr 2003 11:16:52 -0800
Message-ID: <F001.00587F5C.20030423111652@fatcity.com>

Hi Gene,

You didn't mention how big your temp tablespace is. Possibly it is too small or the report is doing some ugly things in it.

This will tell you how much room you have left. Replace the 8's with the database's block size. It assumes that your TEMP tablespace is actually named TEMP.

select f.ts#, t.name, count(f.block#) "Free Blocks",

       min(f.length)*8 "Min K", max(f.length)*8 "Max K" from sys.fet$ f, sys.ts$ t
where f.ts# = t.ts#
and t.name = 'TEMP'
group by f.ts#, t.name
order by 1;

This will tell you who is using the temp tablespace.

select distinct b.sid,

       b.serial#,
       substr(b.username, 1, 8) USERNAME,
       b.process,
       substr(c.segment_name, 1, 8) SEGMENT_NAME,
       bytes BYTES,
       substr(b.osuser, 1, 7) OS_USER,
       substr(b.machine, 1, 6) M_NAME,
       substr(b.program, 1, 12) PROGRAM
  from v$access a,
       v$session b,
       dba_segments c
 where c.owner = a.owner
   and a.sid = b.sid

   and b.status = 'ACTIVE'
   and c.segment_type = 'TEMPORARY';

Running these two scripts below will clear out the temp tablespace. Our TEMP tablespace normally set to pctincrease 10 and set to permanent and dictionary.

alter tablespace temp default storage(pctincrease 0);

alter tablespace temp default storage(pctincrease 10);

Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Gurelei [SMTP:gurelei_at_yahoo.com]
>
> Hi all:
>
> How would one monitor a TEMP space in Oracle8.
> One of our reports failed complaining that it ran out
> of space in TEMP. For a regular TS I could look at
> dba_free_space to check for fragmentation, but I can't
> do it for a TEMP (afaik). What info can I get from
> Oracle to point me to a right direction? Any ideas?
>
> thanks
>
> Gene
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Wed Apr 23 2003 - 14:16:52 CDT