Am I running out of extents (fragmentation)?

From: Simon Goland <sg_at_mda.ca>
Date: 1997/02/04
Message-ID: <32F79EE3.713E@mda.ca>#1/1

Two tablespaces are giving problems to a user who cannot create more tables. I ran the following query

SELECT tablespace_name,

       file_id,
       COUNT(tablespace_name) "Free extents",
       MAX(blocks) "Maximum",
       MIN(blocks) "Minimum",
       SUM(blocks) "Total"

FROM dba_free_space
GROUP BY tablespace_name,file_id;

And the (partial) results for the tablespaces at hand are:

TABLESPACE_NAME FILE_ID Free extents Maximum Minimum Total --------------- ------- ------------ ---------- ---------- ----------
...

FEATURES              8           40      13475         10      55564
INDEX_FEATURES       10           86       1820          2      30428
SYSTEM                1            1      44593      44593      44593

...

Looking at the 'Free extents' I concluded that there is quite a bit of fragmentation. So the solution is to do an export, drop the tablespaces, recreate and import. Am I right? Is there anything else I should check prior to this operation? It is somewhat strange to me because the datafiles for these tablespaces are big - 2GB and 900MB (in the order they appear above), and I don't think there is a lot of data in yet. Maybe I should also modify some storage parameters for the tablespaces?

Thanks,

-- 
[ Simon Goland       B-)>     sg_at_mda.ca ]
[   Without action there is no change   ]

Received on Tue Feb 04 1997 - 00:00:00 CST