Hi Alan,

I note some of your statements are not upto the mark.

1. You can drop tablespace or datafile when there are objects in it.
2. You can drop any datafile in the tablespace irrespective of its
number to its tablespace.
3. Extents will be unallocated in the datafile when objects are deleted
in the schema.
4. You can drop all datafiles belongs to the the tablepace, still
keeping tablespace alive but you cannot create any objects in it and
when you restart database it will complain, the datafiles belongs to the
tablespace require media recovery.

Regards
Suresh
-----Original Message-----
From: Alan Lee(cnoug)
[mailto:oracledba-ezmlmshield-x95112047.[Email address protected]
Sent: Wednesday, March 26, 2008 8:55 PM
To: LazyDBA Discussion
Subject: Re: Deleting a datafile from a tablespace.

Dear Sheryl Philip :
1.you can use the DROP DATAFILE clauses of the ALTER TABLESPACE
command to drop a single datafile . The datafile must be empty. (A
datafile is considered to be empty when no extents remain allocated from
it.) , also there are some restrictions for dropping datafiles :

The database must be open.
If a datafile is not empty, it cannot be dropped.
If you must remove a datafile that is not empty and that cannot be made
empty by dropping schema objects, you must drop the tablespace that
contains the datafile.
You cannot drop the first or only datafile in a tablespace.
This means that DROP DATAFILE cannot be used with a bigfile tablespace.
You cannot drop datafiles in a read-only tablespace.
You cannot drop datafiles in the SYSTEM tablespace.
If a datafile in a locally managed tablespace is offline, it cannot be
dropped.
2. you can use the under query to find out which object were in the
datafile , then you can use aleter xxx move to move them :

select *
from (select owner, segment_name, segment_type, block_id
from dba_extents
where file_id =
(select file_id from dba_data_files where file_name =
:FILE) --your datafile name
order by block_id desc)

3. that's a good idea to use exp/imp tool to do this .
4. hehe , of course not , if the datafile is not empty , you can not
resize it to 0k, that's a restriction of resizing a datafile , It is not
always possible to decrease the size of a file to a specific value. It
could be that the file contains data beyond the specified decreased
size, in which case the database will return an error.
2008-03-26
Alan Lee(cnoug)


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page