Hi Tom,
We are facing an issue in our enterprise application running on JPA, hybernet and sprind and on Oracle 12c. Indexes on one table are getting invalid again and again. We tried to analyze the issue but could not. The issue might be at application level also but we are not able to trap it.

We need your help to get all possible reasons of indexes becoming invalid and also suggest how this issue can be analyzed.

Please also let me know if any further information is required on this from my side.

Your prompt revert will be very helpful for us.

Thanks and Regards

Here Invalid indexes means, UNUSABLE. Below are the table and Indexes creation scripts on which we are facing issue. Regarding your query "the statements you execute that cause the indexes to be invalid", we are not able to identify what statements are making the indexes unusable. We use to deploy a DB patch where we compile the complete schema with no invalid objects and unusable indexes at end and then deploy JAVA application. Indexes becoming unusable during testing of application. Hope this will be helpful.

CREATE TABLE AXIA_INC.MTG_SENDING_QUEUE
(
MESSAGE_ID NUMBER(16,0) NOT NULL ENABLE,
CORELLATION_ID VARCHAR2(50 CHAR),
TXN_ID NUMBER(16,0),
SOURCE_TXN_ID NUMBER(16,0),
TRANSACTION_REF_NUMBER VARCHAR2(50 CHAR),
MESSAGE_TYPE VARCHAR2(50 CHAR),
MESSAGE_CODE VARCHAR2(50 CHAR),
SENDING_DATE DATE,
SENDING_DATETIME TIMESTAMP (6),
SENT_TS TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP,
RECEIVED_TS TIMESTAMP (6),
AMOUNT NUMBER(24,6),
APPLICATION_CODE VARCHAR2(50 CHAR),
CANCEL_RETRY_STATUS VARCHAR2(2 CHAR),
STATUS VARCHAR2(2 CHAR),
ERROR_CODE VARCHAR2(500 CHAR),
ERROR_DESCRIPTION VARCHAR2(4000 CHAR),
ACTION_TAKEN VARCHAR2(2 CHAR),
PARENT_MESSAGE_ID NUMBER(16,0),
TXN_TYPE VARCHAR2(50 BYTE),
SOURCE_CHANNEL_NAME VARCHAR2(50 CHAR),
UTR_NUMBER VARCHAR2(50 CHAR),
AMOUNT_CREDITED_TIME DATE,
STAGE_CODE VARCHAR2(50 CHAR),
MESSAGE VARCHAR2(4000 CHAR),
REMARKS VARCHAR2(765 CHAR),
ACTION_REMARKS VARCHAR2(765 CHAR),
ACTIVE_FLAG VARCHAR2(1 CHAR),
MC_FLAG VARCHAR2(1 CHAR),
BUS_DATE DATE,
TXN_SRC VARCHAR2(1 CHAR),
MAKERID VARCHAR2(16 CHAR),
MAKER_CODE VARCHAR2(50 CHAR),
MAKERDATE DATE,
CHECKERID VARCHAR2(16 CHAR),
CHECKER_CODE VARCHAR2(50 CHAR),
CHECKERDATE DATE,
WORK_BR_ID NUMBER(16,0),
WORK_BRANCH_CODE VARCHAR2(50 CHAR),
LV_ID NUMBER(16,0),
TXN_CTRY_ID NUMBER(16,0),
BATCH_ID NUMBER(16,0),
VERSION_ID NUMBER(16,0),
RETRY_AVAILABLE_FLAG VARCHAR2(1 CHAR) DEFAULT 'N',
COUNTER_PARTY_BANK_IDENT_NO VARCHAR2(50 BYTE),
CUSTOMER_ACC_NO VARCHAR2(50 BYTE),
PRODUCT_TYPE VARCHAR2(50 BYTE),
CUSTOMER_NAME VARCHAR2(50 BYTE),
MAX_RETRY_COUNT NUMBER(16,0),
CURRENT_RETRY_COUNT NUMBER(16,0),
EXECUTION_DURATION NUMBER(16,0),
CONSOLIDATION_MODE VARCHAR2(3 BYTE),
HEADER_CORRELATION_ID VARCHAR2(50 CHAR),
CREDIT_ACC_NO VARCHAR2(50 CHAR),
LINK_ID VARCHAR2(50 CHAR),
PRIMARY KEY (MESSAGE_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE USERS ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC LOGGING STORAGE
(
INITIAL 15728640 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE INDEX AXIA_INC.INDX_SOURCE_TXN_ID ON AXIA_INC.MTG_SENDING_QUEUE
(
SOURCE_TXN_ID
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE INDEX AXIA_INC.IDX_MTG_SENDING_QUEUE ON AXIA_INC.MTG_SENDING_QUEUE
(
TRUNC(SENT_TS)
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE UNIQUE INDEX AXIA_INC.SYS_C0032457 ON AXIA_INC.MTG_SENDING_QUEUE
(
MESSAGE_ID
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE INDEX AXIA_INC.MTG_SENDING_QUEUE_COMP_IDX ON AXIA_INC.MTG_SENDING_QUEUE
(
MESSAGE_TYPE,
STATUS,
SENDING_DATE
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE INDEX AXIA_INC.MTG_SENDING_QUEUE_COMP_IDX1 ON AXIA_INC.MTG_SENDING_QUEUE
(
SENDING_DATE
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;

CREATE INDEX AXIA_INC.MTG_SENDING_QUEUE_IDX2 ON AXIA_INC.MTG_SENDING_QUEUE
(
MESSAGE_TYPE,
SOURCE_TXN_ID,
LV_ID
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE USERS ;