DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SAFE vs COALESCE

  1. #1
    Join Date
    Feb 2001
    Posts
    66
    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

  2. #2
    Join Date
    Oct 2000
    Posts
    139
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width