Google it ....

Showing posts with label Index size. Show all posts
Showing posts with label Index size. Show all posts

Wednesday, November 8, 2017

Oracle Text index size

Oracle Text index is a very powerful feature for searching user specified text in big text columns for example in clob type columns. As a Database Administrator (DBA) you need to monitor it's sizes and growth, but it's segments are not shown in dba_segments view, because every text index internally consists several tables with names prefixed 'DR$', some of these tables have their own indexes and some of them are Index Organized Tables (IOT). Therefore when you need to find actual size of oracle text index you have to consider all 'DR$' tables and their indexes.
Here is script which calculates all text indexes sizes in Oracle Database:

------ partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
)
union 
--- not partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
 )
 order by table_name,  index_name;