Re: Rebuilding a tablespace

In article <3cj546$ose_at_nntp.Stanford.EDU>, myia_at_leland.Stanford.EDU (Mark AuBuchon) says:
>
>Hi There,
>I didn't get many responses last time out with this
>question so I'd like to give it another try:
>
>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.
>
>There MUST be a software package which I can buy (or beg) which will
>rebuild the tablespace.
>
>Help!
>

While other DBA tools can do this much easier (for example online), it is not very difficult with EXPORT either. Try this:

1. Check which users have objects in that tablespace (select distinct owner from dba_segments where tablespace_name=...)
2. EXPORT dba_user/password OWNER=(user1,user2,user3,...)
3. Drop tablespace (including contents)
4. Re-create tablespace
5. IMPORT dba_user/password FROMUSER=(user1,user2,...) TOUSER=(user1,user2,...)

It is important that dba_user has enough privileges (DBA in Oracle 6, EXP_FULL_DATABASE and IMP_FULL_DATABASE in Oracle 7) and that usernames in FROMUSER and TOUSER are in the same order.

This should work for both Oracle 6 and 7, but I am not giving any promises. Test out first.

Mikko Lahti
Finnish Meteorological Institute
Helsinki, Finland Received on Fri Dec 16 1994 - 11:09:01 CET