Re: Removing datafiles from tablespaces

>
> Removing datafiles from tablespaces
>
> donigian_dave_at_bah.com (David Donigian)
> Thu, 16 Nov 1995 21:05:14 +0100
> Booz, Allen & Hamilton
>
> Newsgroups:
> comp.databases.oracle
>
> Folks,
 [snip]
> So what I'd like to do is shrink/remove some of the datafiles from the
> "USERS" tablespace to free up space. How do I do this?

Unfortunately, there is no way to remove data files from a tablespace. You will have to drop and re-create the tablespace after exporting its contents.

> Do I need to export all the schemas (sans indexes), redo the
> tablespaces/datafiles, import, then recreate the indexes in the new
> tablespace? Is there an easier way? Perhaps more importantly,
> will this work?

I would do the following:

1. Export everything that's in the USERS tablespace. (I hope you have enough disk space for the export file!)
2. Drop the USERS tablespace and re-create it with the desired new data file(s).
3. Create your new tablespace for indices.
4. As SYSTEM or another user with ALTER DATABASE privilege, do ALTER DATABASE BACKUP CONTROLFILE TO filename;
5. Import using the export file you created in step 1. Use the INDEXES=N option.
6. Import again, with the INDEXFILE=filename option. This will create a SQL script containing all of the CREATE INDEX commands necessary to rebuild your indices. You can edit the file to specify a different tablespace for the indices.
7. Run the edited index file.

Good luck!

Paul Baumgartel, Principal Consultant              paulb_at_pcnet.com 
Adept Computer Associates, Inc.                                    
********************************************************************