size of indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: size of indexes

  1. #1
    Join Date
    Dec 2007
    Location
    Buenos Aires - Argentina
    Posts
    31

    size of indexes

    Hi,

    I need help about, that how i can get the size real used of a index in a TBS managed locally uniform? My tbs has a autoextend 256M and the indexes has 256M, 512M or 768M etc. but i dont know which really is the size occupied??

    Thanks

    Javier.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    look at dba_segments for the size of your index

  3. #3
    Join Date
    Dec 2007
    Location
    Buenos Aires - Argentina
    Posts
    31
    Yes, but the view dont say the size that really is ocuppied.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes it does, the bytes column tells you

  5. #5
    Join Date
    Dec 2007
    Location
    Buenos Aires - Argentina
    Posts
    31
    Yes. I have a TBS with managed locally uniform, with autoextend 256M. The indexes has the size of the autoextend. When the tbs is out of space and one index requiere space, is assigned one new autoextend de 256M. And so, i have indexes with 256, 512, 768M etc, but i dont know that size is ocuppied really inside of tbs.

    Thanks

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    that is the size occupied, bytes in dba_segments

  7. #7
    Join Date
    Dec 2007
    Location
    Buenos Aires - Argentina
    Posts
    31
    I understand, that the 256M, 512M etc is space allocated, but not the space occupied really? Is true ??

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    No! The Size Of The Index Is That Listed In Dba_segments

  9. #9
    Join Date
    Mar 2007
    Posts
    11
    from oracle.com: DBA_SEGMENTS describes storage allocated for all database segment.

    If he creates an index on an empty table in a tablespace with uniform size 256M extents - the intial size is 256M in dba_segments in the bytes column.

    If he creates the same index in a tablespace with 128k extents, I'm betting it would show 128k in dba_segments.

    That's the allocated space - since oracle automatically gives that much space no matter what...

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Dave has being answering the question correctly once and again yet poster comes back every single time looking for more.

    This might be the case of language barrier... perhaps what poster really wants to know -even when he/she asked about the size of an index, is how much not-in-use space the index has.

    This is a totally different question and, if this is the case I assume the poster wants to detect which indexes might benefit from reorganization because of its deep depth or high percentage of deleted entries.

    Poster might be interested -if this is the case in checking Oracle Note #122008.1
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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