Oracle Text Index statistics report / fragmentation report

Here is an example of using CTX_REPORT.INDEX_STATS for text Index


create this table
create table output (result CLOB);

or

if already there

truncate table OUTPUT ;

from sys


prompt create table output (result CLOB);

truncate table OUTPUT ;

declare
x clob := null;

begin

ctx_report.index_stats('PROD1.SYSOBJECT_FULLTEXT',x);
insert into output values (x);
commit;
dbms_lob.freetemporary(x);
end;
/



set this line to read clob

spool text_stat_report

SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
select * from output;

spool off