DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Index tablespace growing so much faster than data tablespaces

  1. #11
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    But index coalesce does not reduce the height of the index.If the said indexes have 5-6 branches index rebuild would be a better idea.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #12
    Join Date
    Jun 2000
    Posts
    315
    Rebuild or coalesced?

    Then, I found following information for one of the tablespace that has been growing very fast:

    select block_id, sum(bytes) from dba_free_space
    where tablespace_name like 'XYZ'
    group by block_id;

    block_id sum(bytes)
    562209 419430400

    and
    select total_bytes, bytes_coalesced, total_blocks, blocks_coalesced from dba_free_space_coalesced
    where tablespace_name like 'XYZ';

    total_bytes bytes_coalesced total_blocks blocks_coalesced
    419430400 419430400 51200 51200


    It seems to me system automatically coalesced the free space.

    The branch level is only up to 3 from dba_ind_subpartitions.

  3. #13
    Join Date
    Jun 2000
    Posts
    315
    The tablespaces are all locally managed and the initial=next, pctincrease=0. So it's not necessary to coalesce tablespaces.

    The table is a composite partitioned table (rang_hash). If I rebuild the index, I have to rebuild at subpartition level, alter index rebuild subpartition xyz ...

    Is there a command to coalesce index for this kind of table?

  4. #14
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by lgaorcl

    Is there a command to coalesce index for this kind of table?
    Yes.
    Alter Index INDEX_NAME Coalesce;
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #15
    Join Date
    Jun 2000
    Posts
    315
    What I really like to know is: is there any command down to subpartition level like alter index ... rebuild subpartition ....? Because this is a composite partitioned table.

  6. #16
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    A 320 column table has 85 indexes and the table has 40 M rows and it's size 40 GB
    PeopleSoft perchance

  7. #17
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    PeopleSoft perchance
    ===
    No, it is Siebel.

    Tamil

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