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 -----------------------------------------------------------------