Script:Lists Text index and it’s dependent objects

Script:Lists Text index and it’s dependent objects

set linesize 85;
set verify off;
spool bde_chk_imt.lst
accept idx_name prompt 'Enter the Text index name: '
accept tbl_name prompt 'Enter the base table name: '
prompt '-- Printing Object Information'

set pagesize 20
column segment_name format a25     heading 'Object|Name'
column tablespace_name format a10  heading 'Tablespace'
column segment_type format a10     heading 'Object|Type'
column owner format a10            heading 'Owner'       

select /*+ FIRST_ROWS */ unique s.segment_name, s.segment_type,s.tablespace_name,s.owner
from dba_segments s
where 
s.segment_name in (
(select /*+ FIRST_ROWS */ unique ic.index_name
  from dba_ind_columns IC
where 
ic.table_name like upper('%&&tbl_name%')) 
union
(select /*+ FIRST_ROWS */ unique t.table_name 
  from dba_tables T
where 
t.table_name like upper('%&&tbl_name%')) 
union 
(select /*+ FIRST_ROWS */ unique l.segment_name 
  from dba_lobs L
where 
l.table_name like upper('%&&tbl_name%'))
union
(select /*+ FIRST_ROWS */ unique ic.table_name
  from dba_ind_columns IC
where 
ic.index_name like upper('%&&tbl_name%')));

prompt '-- Printing Index Information'
prompt '-- $X index should be created with compress2 (i.e. Comp=ENAB)'
prompt '-- alter index  rebuild compress 2;'
prompt '-- Example: alter index DR$FND_LOBS_CTX$X rebuild compress 2;'

column index_name  format a35      heading 'Index|Name'
column index_type  format a10      heading 'Index|Type'
column compression format a4       heading 'Comp'
column table_name  format a30      heading 'Table|Name'

select unique index_name,index_type,substr(compression,1,4) compression, table_name 
from dba_indexes
where table_name like upper('%&&tbl_name%')
or index_name like upper('%&&idx_name%')
group by index_name,index_type, compression,table_name;

prompt'--Printing LOB Information'
prompt'--$R Table should be cached (i.e. Cached = YES)'
prompt'--ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );'
prompt'--tabname = R_TABLE name'
prompt'--lobname = lob column of R_TABLE, which is the 'DATA' column'
prompt'--(example: alter table DR$FND_LOBS_CTX$R modify lob (DATA) (CACHE);' 

column index_name format a25      heading 'Index|Name'
column segment_name format a25      heading 'LOB|Name'
column table_name format a20      heading 'Table|Name'
column cache format a6            heading 'Cached'

select segment_name,table_name, index_name, cache 
from dba_lobs
where table_name like upper('%&&tbl_name%')
order by segment_name,table_name, index_name, cache;

spool off;