Re: Rebuilding a tablespace

In article <3crovt$6na_at_kronos.fmi.fi> Mikko.Lahti_at_fmi.fi (Mikko Lahti) writes:
>From: Mikko.Lahti_at_fmi.fi (Mikko Lahti)
>Subject: Re: Rebuilding a tablespace
>Date: 16 Dec 1994 10:09:01 GMT
 

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

........

>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.

Be very careful and test, test, test!

We use import/export to transfer database between developers and customer. We have encountered a Known Bug when statistics are associtated with a table (Analyze). Have also encountered a bug when triggers are associated with some tables.

Both bugs keep the data from being created cleanly. Current methods have us dropping statistcs and triggers, doing the export and reapplying the triggers and statistics. Makes for a lenghty process. I fully expect to uncover more problems as this project is still in development. Will keep list posted!

Sam Janney - janney_at_dsr.com
Digital Systems Research, Inc.
San Diego, CA

>Mikko Lahti
>Finnish Meteorological Institute
>Helsinki, Finland
Received on Mon Dec 19 1994 - 23:41:22 CET