| Deleting a datafile [message #53724] |
Tue, 08 October 2002 02:45  |
Anu
Messages: 82
Registered: May 2000
|
Member |
|
|
Hi,
I have a tablespace which contains two datafiles. Accidently I added one more datafile to that tablespace. Later I realized that there is no need for that datafile. How i can I delete the newly created datafile. My database is running in Archive mode
Regards,
Anu
|
|
|
|
| Re: Deleting a datafile [message #53728 is a reply to message #53724] |
Tue, 08 October 2002 05:01  |
Vikas Gupta
Messages: 115
Registered: February 2002
|
Senior Member |
|
|
please note that Oracle does not provide an interface for dropping datafiles in the same way that you could drop a schema object such as a table, a view, a user, etc.
Once you make a datafile part of a tablespace, the datafile CANNOT be removed, although we can use some workarounds.
Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup.
You must export all the objects inside the affected tablespace. Gather information on the current datafiles within the tablespace by running this
query:
select file_name, tablespace_name
from dba_data_files
where tablespace_name ='<name of tablespace>';
Make sure you specify the tablespace name in capital letters.
In order to allow you to identify which objects are inside the affected tablespace for the purposes of running your export, use the following query:
select owner,segment_name,segment_type
from dba_segments
where tablespace_name='<name of tablespace>'
Now, export all the objects that you wish to keep.
Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING
CONTENTS.
Note that this PERMANENTLY removes all objects in this tablespace. Delete the
datafiles belonging to this tablespace using the operating system. Recreate the tablespace
with the datafile(s) desired, then import the objects into that tablespace.
NOTE:
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.
If you do not wish to follow any of these procedures, there are other things
that can be done besides dropping the tablespace.
- If the reason you wanted to drop the file is because you mistakenly created
the file of the wrong size, then consider using the RESIZE command.
See 'Related Documents' below.
- If you really added the datafile by mistake, and Oracle has not yet allocated
any space within this datafile, then you can use ALTER DATABASE DATAFILE
<filename> RESIZE; command to make the file smaller than 5 Oracle blocks. If
the datafile is resized to smaller than 5 oracle blocks, then it will never
be considered for extent allocation. At some later date, the tablespace can
be rebuilt to exclude the incorrect datafile.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
