DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: INDX tablespace in increasing

  1. #11
    Join Date
    Jun 2006
    Posts
    40
    Increasing the table data but not in preportion to indx tablespace. Free space in Indx tablespace is decreasing everyday by 400 Mb.
    But now to free that index tablespace? Is there any way to find the data from the index tablespace before 2 years and delete the same.

    As i am first time facing this issue not able to understand how to manage the space of indx tablespace.

    Can u please help me to solve this issue?

    Thanks in advance.

  2. #12
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    the index is as big as it needs to be, you cant avoid that

    yuo could try rebuilding them and watch them grow to that size again though

  3. #13
    Join Date
    Jun 2006
    Posts
    40
    Increasing the table data but not in preportion to indx tablespace. Free space in Indx tablespace is decreasing everyday by 400 Mb.
    But now to free that index tablespace? Is there any way to find the data from the index tablespace before 2 years and delete the same.

    As i am first time facing this issue not able to understand how to manage the space of indx tablespace.

    Can u please help me to solve this issue?
    how to taketree dump of the index that growing fast?


    Thanks in advance.

  4. #14
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The rate of growth of your indexes will be mostly determined by their size (width of indexed columns) and their number. If you have a lot of both or either then your indexes will take up a lot of space.

    Can you post a DESCRIBE of the table and a list of the indexes and columns? (use the user_indexes view)
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #15
    Join Date
    Jun 2006
    Posts
    40

    Exclamation

    HI,
    I've found out those indexes which r growing everyday.
    There r 3 indexes of size 10 GB each which r growing.
    Can I drop these indexes? Whether it will release space?
    What will be the issues after dropping these?

    Thanks in advance.

  6. #16
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    do you need the indexes? are they being used?

  7. #17
    Join Date
    Jun 2006
    Posts
    40

    Exclamation

    On our development database,
    data part - 9 GB
    index part - 13 GB.
    Its not logical that index part is larger than data part.
    So If i will do rebuilding the indexes whether it will increase space.
    Suppose at the start i rebuild index of 1 GB ,does it make any issue?
    because as per my knowledge rebuilding indexes requires free space.
    And free space :
    In data tablepace:5 GB
    In index tablespace : 5 GB.

    Let me know your comment on it as soon as possible.
    Thanks in advance.

  8. #18
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why is it no logical - happens a lot

    if you insist on re-creating them (no idea why) - drop and re-create

  9. #19
    Join Date
    Jun 2006
    Posts
    40

    Exclamation

    I've rebuilded some of indexes in our development database.
    After that i've checked the space status:
    It shows:


    FILE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
    C:\ORACLE\ORADATA\INDX01.DBF14,088.75 9,208.63 4,880.13
    D:\ORADATA\INDX02.DBF 5,240.00 4,624.00 615.94
    All values in MB.
    It shows that free space in indx01.dbf is 4880 MB and in indx02.dbf is 615 MB. but previously there was reverse case.
    The free space in indx01.dbf is 615 MB and in indx02.dbf is 4880 Mb. Why this happened?
    Then i tried to alter datafile indx01.dbf resize 11000 MB,it shows that used space specified is beyond the used space in datafile.
    Can u please tell me what is the reason behind it?

    Thanks in advance.

  10. #20
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    it will put it extents where it wants them.

    so overall you have gained no space but wasted time rebuilding. Give up, the indexes are at the size they want to be

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