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..
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.
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..
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.
Your way of removing that datafile is TOTALLY WRONG!
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..
Originally posted by nagarjuna
[mmm...v$datafile is a controlfile view...
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...
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.