Hi,

Oracle 8.1.7.3/ NT 4.0

I want to remove free space fragmentation for few of table spaces.

We have Oracle Apps where each tablespace has a particular schema objects only.

I followed the steps mentioned below.

1. Export the schema with COMPRESS=N.
2. Drop all the objects belonging to that schema.
3. Coalesce the tablespace.
4. Import back in the same schema with INDEX=Y, CONSTRAINTS=Y, FULL=N, GRANTS=Y

After the import, when I query the dba_free_space for that tablespace, it shows multiple rows for the same datafile. I thought, all the free space will be coalesced once I drop all the objects and coalesce the tablespace.

Q1: What should be the cause of it?
Q2: How to stop free space fragmentation? We cannot adopt the concept of having init and next extent as same for this tablespace because this TS has diff. nature of objects which cannot have same init and next extent size.

Thanks in Adv.