RE: drop temp tablespace

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Wed, 18 Feb 2004 23:14:58 +0800
Message-Id: <5.1.1.6.0.20040218231421.00ab7d28@pop.singnet.com.sg>

If you say that DBA_SEGMENTS show 0 rows, how do you get a Used Size for the Tablespace ?

Hemant
At 04:35 PM 17-02-04 -0500, you wrote:
>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
>-----------------------------------------------------------------

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 24-Jan-04}

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 Wed Feb 18 2004 - 09:14:58 CST