Free Space Frag: Two questions.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Free Space Frag: Two questions.

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Unhappy

    Hi,

    Oracle 8.1.7.3/ 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.


    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi JRPM,

    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.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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