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