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