ku916
asked on
drop datafile in temp tablespace
I'm running Oracle 9.2 and have 5 datafiles in my temp tablespace. I would like remove (delete) 2 of the datafiles. I've read that you can only drop the tablespace with the datafile that you want to delete. What would happen if that tablespace is temp??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rechecked in 9.2.2 and the previous command works ALTER DATABASE TEMPFILE '<tempfile name with path>' DROP;
Minor addition : use dba_temp_files
SELECT FILE_NAME FROM DBA_TEMP_FILES;- to fetch all the existent tempfiles in Your system.
Minor addition : use dba_temp_files
SELECT FILE_NAME FROM DBA_TEMP_FILES;- to fetch all the existent tempfiles in Your system.
ASKER
OK! After running those sql stmt. I don't see the temp datafiles anymore from either sql plus or OEM, but they are still in my OS. So is it safe to delete the files from OS?
just want to make sure it ok!
just want to make sure it ok!
Yes, you need to manually drop them at OS level after dropping the datafiles in the database.
If you use
ALTER DATABASE TEMPFILE '<name>' DROP INCLUDING DATAFILES;
It should remove the files at the OS level as well.
In addition, you do not need to specify the file name to drop. If you use the FILE# from V$TEMPFILE can be used instead.
ALTER DATABASE TEMPFILE '<name>' DROP INCLUDING DATAFILES;
It should remove the files at the OS level as well.
In addition, you do not need to specify the file name to drop. If you use the FILE# from V$TEMPFILE can be used instead.
I have the same problem of an unwanted datafile in a temporary tablespace. I've been looking at the subject on MetaLink and there is a relevant document (Doc Id: 111316.1), which has the following to say on the subject:
"The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace. If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile. "
This being the case if the datafile is deleted at OS level Oracle still 'knows' about it and has information about it in the control file, it just doesn't use it because it is offline, is there any situation where this would cause problems in the future? (Recovery etc).
Kev
"The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace. If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile. "
This being the case if the datafile is deleted at OS level Oracle still 'knows' about it and has information about it in the control file, it just doesn't use it because it is offline, is there any situation where this would cause problems in the future? (Recovery etc).
Kev
ALTER DATABASE DATAFILE '<tempfile name with path>' OFFLINE DROP;