Re: Tablespace with multiple datafiles to cleanup

Forget it: you cannot remove unwanted files from a tablespace. The best you can do is an alter database datafile blah resize to 2K or something ridiculously small. You can certainly offline drop the files, but that doesn't get rid of them from the tablespace. If, after having done your offline drop command, you were to select * from v$datafile, you'd see your two files sitting there with a status of 'recovery'. If you query v$tablespace to get the relevant tablespace number, and then re-query v$datafile where ts#=the number, you'll see that Oracle still thinks the file is part of the tablespace, albeit in recovery mode.

Since you can query from whatever is left of the tablespace, perhaps it doesn't matter -but I certainly wouldn't want a whole bunch of files permanently in 'recover' status. Will make database recovery *very* interesting.

Regards
HJR "James Williams" <willjamu_at_mindspring.com> wrote in message news:3a6f9479.104950491_at_nntp.mindspring.com...
> I have a tablespace with several datafiles. Two datafiles are empty.
>
> I would like to do the following:
>
> alter tablespace xxx offline drop;
>
> Question:? is that all I need?
>
>
Received on Wed Jan 24 2001 - 21:14:52 CST