size of indexes

[cjgrozo]

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.

[davey23uk]

look at dba_segments for the size of your index

[cjgrozo]

Yes, but the view dont say the size that really is ocuppied.

[davey23uk]

yes it does, the bytes column tells you

[cjgrozo]

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

[davey23uk]

that is the size occupied, bytes in dba_segments

[cjgrozo]

I understand, that the 256M, 512M etc is space allocated, but not the space occupied really? Is true ??

[davey23uk]

No! The Size Of The Index Is That Listed In Dba_segments

[rktscientist]

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...

[PAVB]

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