Index Rebuild Script for IPE database!!

For iProcess engine DB maintenance , you can schedule the Index rebuiding script on periodic basis.

Check the Index space before rebuilding
select sum(bytes)/1024/1024,segment_name from dba_segments
where tablespace_name='Data_Tablespace_Name'
and segment_type='INDEX' group by segment_name;

Rebuild the IndexDECLARE
cursor c_index IS
SELECT index_name
FROM all_indexes"
WHERE index_name not like '%AQ%'"
and tablespace_name = 'Data_Tablespace_Name';

BEGIN
FOR idx_Rec in c_index LOOP
EXECUTE IMMEDIATE 'alter index '||idx_rec.index_name||' rebuild online';
END LOOP;
END;
/

Check the status of Index Rebuild
select object_name,last_ddl_time from user_objects where OBJECT_TYPE='INDEX' order by last_ddl_time;

Check the Index space after rebuilding
select sum(bytes)/1024/1024,segment_name from dba_segments
where tablespace_name='Data_Tablespace_Name'
and segment_type='INDEX' group by segment_name;

Note : Ensure that index rebuild is not happening on AQ tables and AQ indexes.