RE: How to drop datafile from tablespace and how to relocate the

My understanding is that bringing a datafile offline and dropping it is for emergency situations only. Can't you just create a new temporary tablespace, assign it to users and after a while, when the old tablespace is no longer in use, remove it? This should not interfere with 24x7.

Dr. Nikolay Kumanov

MIS Manager, Zeitungsgruppe Bulgarien GmbH 47, Tsarigradsko chaussee, Sofia 1504, Bulgaria phone: +(359-2)4339-643, fax: +(359-2)946-1286 mailto:nkumanov_at_zgb.bg

"Show me a completely smooth operation and I'll show you someone who's
covering mistakes. Real boats rock." - Frank Herbert, "Chapterhouse: Dune"

-----Original Message-----
From: Chuan Zhang [mailto:chuan_at_asiaonline.net] Sent: Friday, October 27, 2000 9:00 AM
To: Multiple recipients of list ORACLE-L Subject: How to drop datafile from tablespace and how to relocate the online temporary tablespace.

Hi, All,

   I remove the datafile from tablespace by

"Alter database datafile 'file_name' offline drop"

I checked the file status was in "Recover" mode. Then I use unix "rm" to remove that file physically.

It works and everything hasn't been affected. But I cannot remove that file information from v$datafile or dba_data_files at all, even I shutdown and start that DB again.

The above is done in our test environment on Solaris 2.7 and Oracle 8.1.5.

If this is successful, I will use it to move the TEMPORARY tablespace to another location in our production. Because our production is 24*7, I cannot use the general way to relocate it. Received on Fri Oct 27 2000 - 04:09:59 CDT