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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
