Oracle 18.104.22.168/ 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.
The coalescense of the free space will happen either when you (as dba) tell it to or when SYSMON gets around to it. Depending on your system, it may not. However, when you allocate new objects in a tablespaces, DBWR or SYSMON is supposed to use contiguous free segments were possible, sort of coalescing.
If you imported too soon after your export, you probably did not give SYSMON enough time.
Have you though of using locally managed tablespaces with fixed initial and nexts? That should prevent the freespace fragmentation you are concerned about.
I immediately coalesced the tablespace manually after droping the objects from the TS.
We have Oracle Financials Apps. So, it comes with pre-defined storage values, which is difficult to change as it is designed by Oracle. They are all dictionary managed tablespaces. I don't know whether I can change them to LMT now.