Re: Rebuilding a tablespace

From: Cathryn Symons <cathryn_at_unisys.co.nz>
Date: Mon, 19 Dec 1994 01:06:27 GMT
Message-ID: <1994Dec19.010627.27602_at_unisys.co.nz>

Be careful about using DROP TABLESPACE xxx INCLUDING CONTENTS, at least under 6.0.xx. If there are a lot of objects in the tablespace, you are likely to run out of enqueue resources, hang the database and get into quite a lot of trouble.

I find it better to write a script to explicitly drop the objects in the tablespace. You can do this by checking out the types of segments in the tablespace

	select distinct segment_type 
	from dba_segments 
	where tablespace_name='WHATEVER'
Then, if they are all tables, just
	select 'DROP TABLE '||segment_name||';'
	from dba_segments
	where tablespace_name='WHATEVER';

If there are tables and indices, or other things, you'll need to work that out.

Make sure everything is successfully exported before dropping!

Mikko Lahti (Mikko.Lahti_at_fmi.fi) wrote:
: 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

-- 
================================================================================
Cathryn Symons					phone ++64 4 4984622	
Information Systems Consultant			fax   ++64 4 4984800
Unisys New Zealand Ltd				email cathryn_at_unisys.co.nz

Received on Mon Dec 19 1994 - 02:06:27 CET