Re: Tablespace with multiple datafiles to cleanup

Also, in the hot backups will fail on that tablespace if the datafiles are in recovery mode. (experience)

  One method is export the objects drop and recreate the tablespace then reimport. Make sure you get the contraints and grants.

  There are also some good tools to help with the relocation of the objects, this would entail creating a new tablespace and moving the objects. We use the Quest tool space manager.

  I could send you a copy of the script that tool uses if you would like. It's nothing fancy, just gets the sql needed to rebuild the constraints, indexes and grants, builds a table with the same name as the original plus an underscore, drops the old table then runs the generated sql. You can do every object in the tablespace as one run, however doing in it smaller chunks is a better idea :)

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 Fri Jan 26 2001 - 14:53:49 CST