RE: drop temp tablespace

From: Guang Mei <gmei_at_incyte.com>
Date: Tue, 17 Feb 2004 17:06:47 -0500
Message-ID: <NJEDKDKJDGAKAEKKNEEJGEKLCNAA.gmei@incyte.com>

Are you sure all users have been switched to the new temp ts? "the used size is still increasing" probably means there are still something going on the old temp ts. I will just bounce the db (if possible) and then drop the old temp.

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 4: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 - 16:06:47 CST