RE: drop temp tablespace

From: Kevin Lange <klange_at_ppoone.com>
Date: Tue, 17 Feb 2004 15:41:07 -0600
Message-ID: <ED1256BD4F253C44B1627B2D365A334F035519@ppoone1.ppoone.com>

You did create it as a temporary tablespace type , right ?? I had an issue like this a while back and it turned out that the temporary tablespace on that system was of type "Permanant".

-----Original Message-----
From: David Boyd [mailto:davidb158_at_hotmail.com] Sent: Tuesday, February 17, 2004 3:35 PM To: oracle-l_at_freelists.org
Subject: RE: drop temp tablespace

Guang,

Thanks for your response.

Initially I had to 'drop tablespace temp including contents'. Currently it returns 0 for the query of select count(*) from dba_segments where tablespace_name = 'TEMP'. I can do 'drop tablespace temp' without including

contents option. However the used size is still increasing.

>From: "Guang Mei" <gmei_at_incyte.com>
>Reply-To: oracle-l_at_freelists.org
>To: <oracle-l_at_freelists.org>
>Subject: RE: drop temp tablespace
>Date: Tue, 17 Feb 2004 12:52:27 -0500
>
>After you switch all the users to the new temp ts, make sure there is no
>objects sitting in your old temp tablespace:
>
>select count(*) from dba_segments where tablespace_name = <old_temp_ts>;
>
>if you get 0, then run
>
>alter tablespace <old_temp_ts> offline;
>
>-- replace your temp datafiles here:
>
>alter database datafile '/oracle/u2/oradata/YPD/temp01.dbf' offline;
>alter database datafile '/oracle/u2/oradata/YPD/temp02.dbf' offline;
>alter database datafile '/oracle/u2/oradata/YPD/temp03.dbf' offline;
>alter database datafile '/oracle/u2/oradata/YPD/temp04.dbf' offline;
>alter database datafile '/oracle/u2/oradata/YPD/temp05.dbf' offline;
>
>
>drop tablespace <old_temp_ts> ;
>
>
>If this does not work, you proabably have a wait a while and try it again.
>If still not working, you might have to bounce the DB.
>
>HTH.
>
>Guang
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Boyd
>Sent: Tuesday, February 17, 2004 12:08 PM
>To: oracle-l_at_freelists.org
>Subject: drop temp tablespace
>
>
>Hi List,
>
>Our TEMP tablespace took Oracle default setting that was auto extending up
>to 32 GB. Last week the tablespace had grown to 18 GB. I tried to drop
>the
>tablespace after I removed any body's temporary tablespace to a newly
>created temp tablespace with much smaller size. The dropping process is
>very slow. I started last Friday. The used size in the old temp
>tablespace
>had been decreased from 18 GB to 3.8 GB yesterday afternoon. I issued the
>drop command through TOAD job scheduler. There was one failure with the
>job
>when I checked this morning. The dropping process seemed stopped. I had
>to
>kill the job. Now the used size in the old temp tablespace is coming back
>slowly even through I issued another dropping command in SQLPLUS. I was
>wondering if any one has any way to drop the temp tablespace quickly.
>Thanks in advance.
>
>_________________________________________________________________
>Find and compare great deals on Broadband access at the MSN High-Speed
>Marketplace. http://click.atdmt.com/AVE/go/onm00200360ave/direct/01/
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------

Get fast, reliable access with MSN 9 Dial-up. Click here for Special Offer! http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Feb 17 2004 - 15:41:07 CST