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

Thread: locally managed tablespaces w/uniform extents

  1. #1
    Join Date
    Dec 2000
    Posts
    15

    Question

    I created a locally managed tablespace with a uniform extent size of 1M. I moved several tables into this tablespace that had many different extent sizes. I thought that moving them into a tablespace with uniform extent sizes would alleviate the need to modify the storage parameters to 1M extents during the move. However, when I query dba tables I see that the initial and next extent sizes are not all 1M. Can anyone explain?

    thanks,
    lacey

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    lacey,

    how did you move tables to locally managed tablespaces ???
    You have to convert dictionary managed tabspace to locally managed tablespace using DBMS_SPACE_ADMIN package/import, removing table level storage clauses.

  3. #3
    If you create a new table in a tablespace locally and uniform you can specify initial anyway; but, if you say 5M initial it won't create 1 extent of 5M instead it will create 5 extents 1M each one.
    That's the way it works.
    I hope I'm clear.
    BTW. Moving a table carries the initial.
    (See in dba_segments)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  4. #4
    Join Date
    Dec 2000
    Posts
    15
    thanks!

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    have a look

    [url]http://www.oracle.com/oramag/oracle/00-nov/o60o8i.html[/url]

  6. #6
    Join Date
    Jun 2000
    Posts
    55
    Isn't SMON wake up every 5 minutes and coalesce free space? Please point me out.

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I guess only if the PCTINCREASE not set to Zero. I meant for Dictionary managed tablespaces as I assumed you were talking about 'em. GOOD point pando, as others might confuse with this posting under this subject.

    [Edited by sreddy on 03-01-2001 at 04:35 PM]
    Reddy,Sam

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    when Oracle cant find a large enough extent it will coalesce the tablespace
    you dont coalesce in locally managed tablespaces though since there is no need at all

  9. #9
    Join Date
    Feb 2001
    Posts
    17
    HI,

    Regarding coleascing space, it is right that SMON wakes up every 5 minutes, to coalesce the free space provided the tablespace's pctfree setting is not zero.This is done regardless of whether the new continogous space is needed or not.

    But in a situation where Oracle tries to allocate an extent and is not able to find an extent of such size, it will still automatically coalesce the space, regardless of the pctfree setting for the tablespace.

    This was mentioned in one of white papers on 'Stop Defragmenting and Start Living' by Oracle Corp.I found this white paper quite interesting and is availabe on Oracle site.

    Ramki

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