Re: Am I running out of extents (fragmentation)?

Simon Goland wrote:
>
> 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?

  YOu can ALTER TABLESPACE tbs COALESCE if you are using appropriate release. Otherwise, export is another way.

  However, you should ensure the long field can fit to the buffer at one time. Otherwise, you cannot import the data back. Moreover, use COMPRESS=Y for export and import. Of course, you should adjust the storage parameter to ensure the fragmentation will not happen again. It is up to the frequency of data growth.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841