Datafile has been removed already , But I still can find its information in V$datafil

Quote:

---

Originally posted by soniaarora
The physical
datafile must then be removed using an operating system command (Oracle NEVER
physically removes any datafiles). Depending on which platform you try this
on, you may not be able to physically delete the datafile until Oracle is
completely shut down. (For example, on Windows NT, you may have to shutdown
Oracle AND stop the associated service before the operating system will allow
you to delete the file - in some cases, file locks are still held by Oracle.)

---

nope, you dont need to shutdown the database in NT to remove a datafile that has been logically removed from Oracle. You just query any of datafile related datadictionary views ater removing the datafile from Oracle using sql syntex. Your lock gets released and you can remove the file physically...

Steps on NT:

alter database drop <; --logical deletion of datafile
select * from dba_data_files; --this releases locks on datafile
remove the file physically..
This works...
-nagarjuna

Quote:

---

Originally posted by soniaarora
Pando,
This is exactly what I meant. It is not that after u offline a datafile, u HAVE to drop the tablespace. It is only that this is the workaround available to remove the datafile which is not required in the tablespace.

---

nope.. We can use a workaround to remove the datafile from a tablespace.

Steps:

1. alter database drop datafile <>;
2. alter database backup controlfile to trace;
3. edit the controlfile creation script generated by the above command. Remove the entry of the datafile that we have dropped in step 1.
4. Now, start up the database in nomount state and recreate the control file.
5. Do the recovey if needed using current logfiles.
6. Open the database.

This works fine. But, we need some experiance to do this. Be, careful doing this..

-nagarjuna

sigh, so many PROFESSIONALs discussing such a MEANINGLESS topic!
It is so clear that you have to rebuild the tablespace after your offline the datafile.
Offline drop the datafile only allows you to open the database temporary so that you can export/move the tables in the tablespace and drop and recreate it later.
I think the metalink note has said it clearly.

nagarjuna:
Your way of removing that datafile is TOTALLY WRONG!

I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary

Quote:

---

Originally posted by pando
I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary

---

mmm...v$datafile is a controlfile view...

chao_ping > I agree that, this is a meaningless topic.. I posted that msg just to add spice to our DBA life. We get bored with daily routine works.. These kinda experiments gives some fun.. nothing much.. I do agree that my reply was meaningless..

Quote:

---

Originally posted by nagarjuna
[mmm...v$datafile is a controlfile view...
[/B]

---

Oracle use v$datafile (control flie) mostly in ALTER DATABASE OPEN(MOUNT) process.
In most others situations oracle will use sys.ts$ and sys.file$ tables.
but records about datafile still present in dictioany tables and don't present in controlfile!!!
Very interesting situation...

THIS IS WRONG WAY.

[Edited by Shestakov on 09-07-2002 at 12:27 PM]

Pando is right, since you can`t control how data is writen to the files, you don`t know the contets of the file you are "droping".
A table may span over more than one datafile, for example, so there is no logic in droping a datafile, not in my eyes, anyway.

I am extremely sorry for that post. That was not the right way to do. That was really a stupidity. Some problem with my basics I guess..

soniaarora

you cannot remove a datafile from a tablespace, even the note you pasted said so

read it again and properly!

thank you