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;

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

While dropping a tablespace it fails with error ORA-29857 like below.

SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution:

Drop the domain indexes on the tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

SQL> select index_name from dba_indexes where index_type = 'DOMAIN' 
and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects.

select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you don't need these users you can try to delete users

SQL> drop user HR cascade;
User dropped.

SQL> drop user OE cascade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop user ix cascade;
User dropped.

after that we can delete tablespace

SQL> drop tablespace example including contents and datafiles;