Reorganizing tables and tablespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Reorganizing tables and tablespaces

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    Folks!,
    We have different types of tables and currently all of them are in one tablespace(user).
    Broadly the tables can be catogarized in following catagories:



    avg_row_len*num_rows*1.2(from dba_tables)
    1.high insert and high updates 7mb
    2.high inserts and deletes/moderate updates 2mb
    3.high inserts/low updates or look up tables 64k

    So should I create these three catogores of tables with different inital extents ?

    Do I need to have these three catogories in separate tablespaces to avoid any fragmentation?
    or I can have them in one tablespace and just create the tables with diff initial extents?

    Thanks!



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Hi

    I think if you want to put in one tablespace alter tablespace´s parameter

    minimum extent 128K

    this will esure all extents are 128K thus avoiding fragmentation, the deallocated extents will be reused

    or

    have different inital extents but multiples of smallest extent size and coalesce every once a while, once the extent issue is done just adjust pctfree and pctused parameters for respective table with high inserts/updates/deletes.



  3. #3
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    thanks pando!
    currently all the tables have different storage and block utilization parameters.I wanted to to bring some uniformity (if necessary!)
    so for first alternative, if I just change the min extents for tablespace is that sufficient ?

    In the second alternative what initial extents are recommended if we want to group separately?
    can u elaborate on 'once the extent issue is done....'?

    thanks

  4. #4
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    This is just a modified question:

    I have exported the full db to new instance.
    the user tablespace containing all tables has min_extent as 128k. all tables now have just one extent.

    But the initial and next are not same for many tables.
    Initial extents are different for different catagories of table as mentioned earlier.

    Should I make initial and next same and pct_inc as zero to avoid table fragmentation in future?

    ' tables having different initial/next ln same ts '-->is it ok?

    thanks



  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    hI

    As long as you have

    minimum extent 128K clause

    (dont confuse with minextents parameter, they are totally different)
    all extents will be multiples of 128K no matter the size of initial and next.

    I said when extent issue is done, it means after you have set initial next at tablespace level. When those are done the only thing we can change are pctfree and pctused parameters to accomodate updates/inserts/deletes (this is for both cases by the way)

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