RE: Clean temporary tablespace

Or you can
1. Create another Temporary Tablespace with CREATE TEMPORARY TABLESPACE <new_temporary_tablespace>
TEMPFILE ..... SIZE ... AUTOEXTEND ON NEXT .. MAXSIZE ... 2. Issue ALTER USER TEMPORARY TABLESPACE <new_temporary_tablespace> commands 3. If running 9.2 with a Default Temporary Tablespace, ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <new_temporary_tablespace> 4. Check that there are no active sessions still using the existing Temporary Tablespace, querying V$SORT_SEGMENT and also use the Unix "fuser" command against the Temporary Tablespace Tempfiles
5. DROP TABLESPACE <current_temporary_tablespace> INCLUDING CONTENTS [add AND DATAFILES if using 9.2]
Note : Ensure that you really have no segments in the tablespace other than the temporary segment [ie, it is really
a temporary tablespace ! because the "DROP TABLESPACE" command syntax doesn't specify if it is a TEMPORARY
or PERMANENT tablespace]
6. Recreate your original Temporary Tablespace 7. Run through Steps 2 and 3 again to revert to the original Temporary Tablespace
8. Run Steps 4 and 5 to drop the <new_temporary_tablespace>

Because, bouncing or restarting the Database Instance isn't always an available option!

Hemant
At 12:05 AM 12-11-03 -0800, you wrote:
>Hi,
>
>Restartup your database if possible.
>
>If you set the datafile autoextent on then you have to recreate that
>tablespace,
>if you set it to not autoextent on for some query may fail due to not
>enough sorting space.
>
>
>Sinardy
>
>-----Original Message-----
>Sent: 12 November 2003 14:24
>To: Multiple recipients of list ORACLE-L
>
>
>Hello!
>
>How can I clean temporary tablespace? It grows up faster and faster.
>
>--
>Поцелуев Виталий Игоревич (VirVit)
>Oracle 9i DBA beginner
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: VirVit
> INET: virvit_at_prodtorg.udm.net
>
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Sinardy Xing
> INET: SinardyXing_at_bkgcomsvc.com
>
>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).