Re: Problems with dropping datafile in tablespace

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 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.

HTH -- Mark D Powell -- Received on Thu May 01 2003 - 09:07:03 CDT