Re: Fragmented tablespaces?

jwright_at_phish.nether.net () wrote:

>After recently getting a few eror messages concerning non-contigouos
>extents on one of my tablespaces, I did a :
 

>select * from dba_free_space where tablespace_name = 'FASYS'
 

>Which told me :
 

>[SNIP]
>TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
>------------------------------ ---------- ---------- ---------- ----------
>FASYS 11 8577 51200 25
>FASYS 11 7202 51200 25
>FASYS 11 10127 51200 25
>FASYS 11 10302 51200 25
>FASYS 11 10052 51200 25
>FASYS 11 20177 51200 25
>FASYS 11 10252 51200 25
>FASYS 11 6952 51200 25
>FASYS 11 7177 51200 25
>FASYS 11 6927 51200 25
>FASYS 11 10327 51200 25
 
>So I have a lot of 25 block clusters in my tablespace. My question is,
>how do I remedy this fragmented tablespace? I know exp/imp s the usual
>way to recover from fragmentation, but when I run exp on my Unix box, it
>gives me the choices of either :
 

>1) Entire Database, 2) Users, 3) Tablename
 

>But no option to export the tablespace itself.
 

>What are people doing when they ahve severly fragmented tablespaces like I
>do in this situation?
 

>Any help is greatly appreciated, or Oracle reference document numbers, or
>what manuals to peruse to solve my problem.
 

>Thanks.
 

>-Joshua Wright

Joshua,

You might want to try to set the PCTINCREASE parameter of your default storage clause of the tablespace to something other than 0 (fx. 1). ALTER TABLESPACE fasys DEFAULT STORAGE ( PCTINCREASE 1 ) This will cause adjacent blocks to coalize. I know that this will not solve all of the problem, but it will hopefully minimize the fragmentation.

Morten Tangaa
Den Blå Avis A/S
dba-edb_at_inet.uni-c.dk Received on Mon Jun 10 1996 - 00:00:00 CEST