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;
