Hi guys,
I'm on OL 6.9 and EE 12.1
I've noticed an issue when doing some maintenance tasks (reclaiming space). Everything was running smoothly until I've changed from:
l_runCmd := 'ALTER INDEX '||rec_indexes.owner||'.'||rec_indexes.index_name||' SHRINK SPACE';
l_runCmd := 'ALTER INDEX '||rec_indexes.owner||'.'||rec_indexes.index_name||' SHRINK SPACE COMPACT';
to l_runCmd := 'ALTER INDEX '||rec_indexes.owner||'.'||rec_indexes.index_name||' REBUILD';
Any ideas how come REBUILD in stead of SHRINK SPACE/SPACE COMPACT can cause some indexes to end up in UNUSABLE state?
Attached there's a log of my shrinkDB.sql. There's no direct load, DDLs for tables occur before index rebuild. Or can the last step of coalescing tablespaces and resizing datafiles cause this?