Re: Rebuilding a tablespace

In article <3cj546$ose_at_nntp.Stanford.EDU>, myia_at_leland.Stanford.EDU (Mark AuBuchon) writes:

|>I have a tablespace which has grown & grown & grown it must be made up
|>of 17 data files.
|>
|>And, I must have 20 users with objects in it.
|>
|>And, many of the tables are fragmented such that some are getting near
|>the 121 extent limit.
|>
|>I'd like to rebuild the tablespace, but Oracle export only allows me
|>to rebuild the entire database or a single user or a single table.

Well, you need to:

1. Identify the objects which live in the tablespace. (Note that indexes, are safe to ignore because you can just run your SQL script to recreate them later).
2. Export said objects
3. alter tablespace <tablespace_name> offline
4. drop tablespace <tablespace_name> including contents cascade constraints
5. create tablespace <tablespace_name> datafile <datafile_name> size <whatever> default storage <whatever>
6. Import objects from dump file created in step 2
7. Create any indexes as required.

Regards,
Chas. Dye
charles.dye_at_syntex.com Received on Fri Dec 16 1994 - 19:12:57 CET