Tablespace reorganisation?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Tablespace reorganisation?

  1. #1
    Join Date
    Apr 2003
    Posts
    6

    Unhappy Tablespace reorganisation?

    Hi! I've been told that i need to carry out a tablespace reorganisation of one of the instances...
    Does this mean recreating the database?
    Anyone have any pointers or know of any resouces/articles to help with this?
    Many thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Who ever told you that ask him/her what does he/she mean by tablespace reorganization and why?

    --Do they want you to move the datafiles from one location to other?? why ??
    --Shrink the datafiles ?? why ??
    --Rename (name) the tablespaces ?? why ??
    --Change tablespaces from data dictionary to LMT?? why ??
    --Partition the table(more tablespaces) ?? why ??
    --Change storage clauses for Tablespaces?? why ??

    Tablespace reorganization can be any thing... ask them to be specific and ask the reason... don't just say yes and the run here and there asking others what to do...

    If they have the answer to why then they know what they want, if no, forget it... and have a BEER
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Mar 2000
    Posts
    39

    Re: Tablespace reorganisation?

    They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_free_space
    union all
    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_extents;

    Run the following to de-fragment the tablespaces that are fragmented:

    alter tablespace coalesce;

    Good luck.
    Thanks,
    Afshin.

  4. #4
    Join Date
    Mar 2000
    Posts
    39

    Re: Tablespace reorganisation?

    One more thing to keep in mind:

    From William Page's book:

    "In object-level fragmentation, the object might look fine and healthy at the tablespace level, having a single extent, but some probing inside those extents can reveal a different story.

    A common occurence is that a DBA sees space allocation problems in an often-modified tablespace, and rebuilds the tablespace and all its objects. Subsequently, the database performance can increase. This increase in performance is attributed to the tablespace fragmentation being eliminated, but it could very well be the eliminition of object-level fragmentation that gives you the performance boost."

    Mr. Page says that in order to de-fragment the tablespace, do an export and import of the objects within that tablespace. The export/import process "not only coalesces your free space, but Export also coalesces your database objects into one extent." So if you were asked to de-frgment the tablespace because of performance issues, then you might want to consider export/import to solve two problems in one shot.
    Thanks,
    Afshin.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That stuff about how it is better to have tables/indexes as a single extent is a big pile of BS, and has been debunked for many years now.

    Export/import to reduce number of extents is ...

    i) not going to improve performance unless you have more than many thousands of extents
    ii) the wrong way to do it any way -- do "alter table move" and "alter index rebuild" if you really really do have an extent problem.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Re: Tablespace reorganisation?

    Originally posted by akhorram
    They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_free_space
    union all
    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_extents;

    Run the following to de-fragment the tablespaces that are fragmented:

    alter tablespace coalesce;

    Good luck.
    oh my.... here he comes with the old myth... pile of BS as slimdave pointed out

  7. #7
    Join Date
    Mar 2000
    Posts
    39
    Other than the attitude, I appreciate the fact that you point out I am wrong. Please let me know how to de-fragment tablespaces. What different approaches can be taken? Thank you.
    Thanks,
    Afshin.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The primary approach is to not let your tablespaces get fragmented. If locally managed tablespaces are not available in your version of Oracle then divide your tables/indexes into three groups, by size -- small, medium, large.

    Put each type in it's own tablespace -- suggested extent sizes are generally something like 256k, 8m, 256M -- and use pctincrease of 0 on all segments. do not specifyinitial or next when creating segments, just specify the appropriate tablespace based on size.

    If locally managed tablespaces are available, then use them. Specify uniform extent sizes, divide the segments into small medium and large as before and put them in an appropiate TS.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Oct 2002
    Posts
    391

    Re: Re: Tablespace reorganisation?

    Originally posted by akhorram
    They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_free_space
    union all
    select tablespace_name, block_id, bytes, blocks, segment_name
    from dba_extents;

    Run the following to de-fragment the tablespaces that are fragmented:

    alter tablespace coalesce;

    Good luck.
    isnt defragment just to collect the blocks so that they can be contiguous for data usage?

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    They might be logically contiguous within Oracle, but physicaly (on disk) they are almost certain not to be. That's one of the reasons why the benefits of small number of extents in nonsense
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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