snippetMinor

How to find the actual space being consumed by indexes on a table in Oracle?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thespacehowactualconsumedbeingindexesfindoracletable

Problem

I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be considered.) I want the used bytes not the allocated bytes.

Can you help me in going ahead?

Also, is there a way to find the actual size of a long field in a table.

PS: vsize() and dbms_lob.getlength() do not work.

Solution

SELECT idx.index_name, SUM(bytes)
  FROM dba_segments seg,
       dba_indexes  idx
 WHERE idx.table_owner = >
   AND idx.table_name  = >
   AND idx.owner       = seg.owner
   AND idx.index_name  = seg.segment_name
 GROUP BY idx.index_name

will show you the amount of space actually consumed by each index. I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index. If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.

Code Snippets

SELECT idx.index_name, SUM(bytes)
  FROM dba_segments seg,
       dba_indexes  idx
 WHERE idx.table_owner = <<owner of table>>
   AND idx.table_name  = <<name of table>>
   AND idx.owner       = seg.owner
   AND idx.index_name  = seg.segment_name
 GROUP BY idx.index_name

Context

StackExchange Database Administrators Q#1151, answer score: 9

Revisions (0)

No revisions yet.