RE: drop temp tablespace

  • From: Kevin Lange <klange@xxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 17 Feb 2004 15:41:07 -0600

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@xxxxxxxxxxx]
Sent: Tuesday, February 17, 2004 3:35 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx>
>Reply-To: oracle-l@xxxxxxxxxxxxx
>To: <oracle-l@xxxxxxxxxxxxx>
>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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David Boyd
>Sent: Tuesday, February 17, 2004 12:08 PM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: