Re: Clean temporary tablespace

I have been using this method in all my 8.0 and 8.1 databases. Unfortunately, if it is a TEMPORARY Tablespace with a TEMPFILE, [as I have begun using in 9.2] this command fails. Then, the only option to either resize the TEMPFILE or resize the TEMPORARY Segment is to drop and recreate the TEMPORARY Tablespace as I have outlined in another email.

Hemant

At 03:54 PM 13-11-03 -0800, you wrote:
>8.1.6.3
>SQL> select status,contents,extent_management,allocation_type from
>dba_tablespaces where tablespace_name='TEMP';
>
> Extent
>STATUS CONTENTS Management ALLOCATIO
>--------- --------- ---------- ---------
>ONLINE TEMPORARY DICTIONARY USER
>
>SQL> alter tablespace TEMP default storage(pctincrease 0);
>
>Tablespace altered.
>
>SQL>
>
>This will force the SMON to wake up and coalesce those segments which
>have been
>used but not freed. This will work if you are using DICT.Managed temp
>tablespaces.
>I have been using it regularly with results.
>
>HTH
>GovindanK
>
>On Tue, 11 Nov 2003 22:24:24 -0800, "VirVit" <virvit_at_prodtorg.udm.net>
>said:
> > Hello!
> >
> > How can I clean temporary tablespace? It grows up faster and faster.
> >
> > --
> > Поцелуев Виталий Игоревич (VirVit)
> > Oracle 9i DBA beginner
>
>--
>http://www.fastmail.fm - Sent 0.000002 seconds ago
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: GovindanK
> INET: gkatteri_at_fastmail.fm
>
>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).

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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).