Re: Newbie - Tablespace defrag problem

barry_deevey_at_my-deja.com wrote:
> I have a requirement to de-fragment the tables & tablespaces within my
> database.

Now - tables are not the only thing that gets "fragmented" over time. Indexes and objects do it too.

> I export the tables, drop the tables & then run the alter tablespace
> <tspace_name> coalesce command, and then import the tables back in -
> The problem is that the 1st block of extents in the tablespace contain
> freespace (The amount of blocks used varies within each tablespace)And
> I can't seem to manage to get the import to use the 1st block of
> freespace. Am I doing something stupid, or does Oracle do this for a
> particular reason??

Oracle makes use of the rule, that the whole initial extent must fit in one extent. When you export, the default action is that Oracle recalculates the initial extent based on the allocation of the table. This is VERY bad, because you might have a lot of unused space in a table, if you use pct-increase. You should recreate all tables manually, and then run the import with ignore=yes.

Most likely, your first free block is not big enough for any tables to be allocated there.

-- 
  P. Larsen