I have set up a new database, and prepare for a migration with export/import.
I set up the tablespaces following the SAFE(Simple Algorithm for Fragmentation Elimination) recommendations, so I use PCTINCREASE 0.
Now I see that after import the tables have specified storage clause, and in another hand I know that with a PCTINCREASE 0 at tablespace level, SMON will not COALESCE the free space.
Question 1. If there are pctincrease other than 0 at table level when it's 0 at tablespace level, smon will coalesce the free space?
The system have more than 500 tables, so I wonder if
1. it's worth to truncate all tables, edit all table definition and import just the rows, or
2. I have to change the pctincrease at tablespace level or
3. do nothing, SMON work anyways because pctincrease is not 0 at table level.
Why pctincrease ZERO SMON doesnt need to coalesce:
Because any free extent can be reused
If PCTINCREASE were 1 you get different size of extents therefore you get freespace fragmentation thatīs why SMON has to coalesce
Why locally managed tablespace doesnt have to coalesce:
Because the size of extents size are uniform or multiples of each other (so can be reused).
If you really want SMON to coalesce your tablespace create tablespace with pctincrease 1 and when you create table set pctincrease to 0, you still get uniform extents and SMON will coalesce (SMON works at tablespace level)