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.
Thanks for any input,
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)
This is from Oracle documents
Click Here to Expand Forum to Full Width