SAFE vs COALESCE

[m_d_marian]

Hi guys,

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,
marian

[Sweetie]

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