Re: Problems with dropping datafile in tablespace

Peter <peter_at_nomorenewsspammin.ca> wrote in message news:<o1q2bvstb0fbnunks1k6rqttuvh2j65qq7_at_4ax.com>...
> On 1 May 2003 07:07:03 -0700, Mark.Powell_at_eds.com (Mark D Powell)
> wrote:
>
> >Peter <peter_at_nomorenewsspammin.ca> wrote in message news:<p7t1bv8nmj8isu16h4ucojrdm66pfecdl9_at_4ax.com>...
> >> After I did the following:
> >>
> >> alter database datafile 'c:\oracle\oradata\db\tools\T1.dbf' offline
> >> drop;
> >>
> >> select file_name, tablespace_name from dba_data_files;
> >>
> >> The datafile that I dropped was still shown as part of the
> >> tablespace. Why is that?
> >
> >Peter, the tablespace the file was attached to now has to be dropped
> >and recreated. There is no way to remove a file from a tablespace
> >once it has been added, at least for versions 6 - 8.1, without having
> >to drop the tablespace. It would have been nice if you listed the
> >Oracle version on your post.
> >
>
> I am using version 9.2
>
> >I have always thought Oracle should be able to drop files from
> >tablespace without invalidating the tablespace if the file contained
> >no objects and providing the tablespace contained at least one other
> >file, but it does not.
> >
>
> My tablespace does contain another datafile T2.dbf
> Now, both T1.dbf (dropped) and T2.dbf are still listed if I do a
> select file_name, tablespace_name from dba_data_files;
> I hope it is safe to delete T1.dbf
>
> Thanks

I suspect the tablespace status is not ONLINE.

See the SQL manual for
  Alter tablespace x offline [force]
  Drop tablespace x [including contents]

Everything should be OK as long as you did not have any objects in the tablespace. Normally you only use the alter database drop datafile option as part of a recovery process where you need to drop files and have to do it with the database not opened. Unless you are using OFS files you will physically have to remove/delete the OS file yourself after removeing it from Oracle.

HTH -- Mark D Powell -- Received on Fri May 02 2003 - 11:53:27 CDT