Re: Tablespace with multiple datafiles to cleanup

<steveee_ca_at_my-deja.com> wrote in message news:94ukmp$q74$1_at_nnrp1.deja.com...
> Hi Howard,
>
> Any thoughts on why this is the behaviour of Oracle? As with all things,
> once you know the behaviour you can govern your actions accordingly so
> this situation can be avoided but still...it seems like a silly
> oversight for the reasons you've explained no?

Yup. I've been asking for this feature for about 2 years now (it would help undo some of the damge I do in my demo's!). When you resize a file downwards in size, it can certainly work out if any data is hanging off beyond the requested size, and warn you vigorously if so. So I can't see that it would be too difficult for it to work out that the file you are requesting to drop is 'clean' of any real data, and thus allow the removal to take place.

Wishlist stuff, though, is ultimately pointless (unless it involves Java and the Web, in which case Oracle can't seem to give you *enough* wishes these days!).

Let's wait to see the feature set for Oracle17i, eh?

Regards
HJR
>
> In any event, knowing this will help me act accordingly in future..
> Thanks Howard.
>
> Steve
>
>
>
>
>
> In article <3a6f9a00_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > 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?
> > >
> > >
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sat Jan 27 2001 - 16:56:18 CST