DB version: 11.2.0.4
OS : RHEL 6.5
The following table EPM_AUDIT_LOG was 130 GB in size. I did the following delete (25 GB worth size) and then ran the SHRINK commands with CASCADE clause which should take care of indexes. After the shrink, the table size reduced to 105 GB.
This table is partitioned (Range partition on a date column) with local indexes. Since shrink involves moving extents around, I was expecting that the index partitions will become UNUSABLE. But none of the indexes (index partitions) went to UNUSABLE state. Why is that?
SQL> delete from EPM_AUDIT_LOG where PROC_STAT_CODE='Yes' ;
alter table EPM_AUDIT_LOG enable row movement;
alter table EPM_AUDIT_LOG shrink space compact CASCADE;
alter table EPM_AUDIT_LOG shrink space CASCADE;
alter table EPM_AUDIT_LOG disable row movement;
--- gather stats for EPM_AUDIT_LOG
SQL> select index_name, status from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'EPM_AUDIT_LOG') order by 1 asc;
INDEX_NAME STATUS
------------------------------ --------
IDX_AUDITPARAM1 USABLE
IDX_AUDITPARAM1 USABLE
IDX_AUDITPARAM1 USABLE
IDX_AUDITPARAM1 USABLE
IDX_AUDITPARAM2 USABLE
.
.
.
.
<output snipped>
SQL> select distinct status from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'EAI_AUDIT_LOG');
STATUS
--------
USABLE
The indexes are created with NOLOGGING as shown below. Could this be the reason , by any chance ?
create index IDX_AUDITPARAM1 on EPM_AUDIT_LOG (AUDIT_PARAM_1, AUDIT_DATE_TIME) nologging local;