Index getting unusable in partitioned table

-- create a tablespace for data

CREATE TABLESPACE TBS_PART_TEST DATAFILE
'TBS_PART_TEST01.DBF' SIZE 10M AUTOEXTEND OFF,
'TBS_PART_TEST02.DBF' SIZE 10M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

-- create a tablespace for index

CREATE TABLESPACE TBS_PART_TEST_IND DATAFILE
'TBS_PART_TEST_IND01.DBF' SIZE 10M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

-- creat a user to perform the activities

CREATE USER PART
IDENTIFIED BY VALUES '9725DA1D2FBD22FC'
DEFAULT TABLESPACE TBS_PART_TEST
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 4 Roles for PART
GRANT SELECT_CATALOG_ROLE TO PART;
GRANT DBA TO PART;
GRANT RESOURCE TO PART;
GRANT CONNECT TO PART;
ALTER USER PART DEFAULT ROLE ALL;
-- 1 System Privilege for PART
GRANT UNLIMITED TABLESPACE TO PART;
-- 2 Tablespace Quotas for PART
ALTER USER PART QUOTA UNLIMITED ON TBS_PART_TEST_IND;
ALTER USER PART QUOTA UNLIMITED ON TBS_PART_TEST;

conn part/*****

-- create a partitioned table and two unique indexes

CREATE TABLE TRANSFERS_DETAIL
(
USER_ID VARCHAR2(15 BYTE) NOT NULL,
TRANS_DATE DATE NOT NULL,
RECEIVER_NETWORK_CODE VARCHAR2(10 BYTE) NOT NULL,
SERVICE_TYPE NUMBER NOT NULL
)

PARTITION BY RANGE (SERVICE_TYPE)
(
PARTITION TRANSFERS_DETAIL_P10 VALUES LESS THAN (10)
TABLESPACE TBS_PART_TEST,
PARTITION TRANSFERS_DETAIL_P20 VALUES LESS THAN (20)
TABLESPACE TBS_PART_TEST,
PARTITION TRANSFERS_DETAIL_P40 VALUES LESS THAN (40)
TABLESPACE TBS_PART_TEST
);

CREATE UNIQUE INDEX TRANSFERS_DETAIL_IDX ON TRANSFERS_DETAIL
(USER_ID)
LOGGING
TABLESPACE TBS_PART_TEST
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE UNIQUE INDEX TRANSFERS_DETAIL_IDX2 ON TRANSFERS_DETAIL
(service_type) tablespace TBS_PART_TEST_IND local;

-- create a procedure

CREATE OR REPLACE procedure proc_part is
v_count number;
begin
select count() into v_count
from transfers_detail;
dbms_output.put_line(v_count);
end proc_part;

-- create a view

CREATE OR REPLACE VIEW VIEW_PART
(USER_ID, TRANS_DATE, RECEIVER_NETWORK_CODE, SERVICE_TYPE)
AS
select USER_ID,TRANS_DATE,RECEIVER_NETWORK_CODE,SERVICE_TYPE
from transfers_detail
where service_type0

-- insert data into the table

-- select data from a particular partition

select from transfers_detail partition(transfers_detail_p40);

USER_ID TRANS_DATE RECEIVER_NETWORK_CODE SERVICE_TYPE
5 12182009 11 20
6 12182009 20 24
7 12242009 21 30
8 1292009 23 37
9 1112009 22 32
11 1182009 26 33
12 12312009 25 38

-- split the partition

ALTER TABLE TRANSFERS_DETAIL
SPLIT PARTITION TRANSFERS_DETAIL_P40 AT (30)
INTO (
PARTITION TRANSFERS_DETAIL_P30 TABLESPACE TBS_PART_TEST ,
PARTITION TRANSFERS_DETAIL_P40 TABLESPACE TBS_PART_TEST);

-- check the data

select from transfers_detail partition(transfers_detail_p30);

USER_ID TRANS_DATE RECEIVER_NETWORK_CODE SERVICE_TYPE
5 12182009 11 20
6 12182009 20 24

select from transfers_detail partition(transfers_detail_p40)

USER_ID TRANS_DATE RECEIVER_NETWORK_CODE SERVICE_TYPE
7 12242009 21 30
8 1292009 23 37
9 1112009 22 32
11 1182009 26 33
12 12312009 25 38

-- Add a new partition (with data movement) and split once again

ALTER TABLE transfers_detail
ADD PARTITION TRANSFERS_DETAIL_P30 VALUES LESS THAN ( 50 )
TABLESPACE tbs_part_test

ALTER TABLE TRANSFERS_DETAIL
SPLIT PARTITION TRANSFERS_DETAIL_P40 AT (35)
INTO (
PARTITION TRANSFERS_DETAIL_P35 TABLESPACE TBS_PART_TEST ,
PARTITION TRANSFERS_DETAIL_P40 TABLESPACE TBS_PART_TEST);

-- check the status of indexes

select status, partition_name from dba_ind_partitions where index_owner='PART';

STATUS PARTITION_NAME
UNUSABLE TRANSFERS_DETAIL_P40
USABLE TRANSFERS_DETAIL_P30
UNUSABLE TRANSFERS_DETAIL_P35
USABLE TRANSFERS_DETAIL_P10
USABLE TRANSFERS_DETAIL_P20

select index_name, status from dba_indexes where owner='PART';

TRANSFERS_DETAIL_IDX UNUSABLE
TRANSFERS_DETAIL_IDX2 NA

-- now make the indexes usable

ALTER INDEX TRANSFERS_DETAIL_IDX REBUILD;
ALTER INDEX TRANSFERS_DETAIL_IDX2 REBUILD PARTITION TRANSFERS_DETAIL_P35 TABLESPACE TBS_PART_TEST_IND;
ALTER INDEX TRANSFERS_DETAIL_IDX2 REBUILD PARTITION TRANSFERS_DETAIL_P40 TABLESPACE TBS_PART_TEST_IND;

select status, partition_name from dba_ind_partitions where index_owner='PART';

USABLE TRANSFERS_DETAIL_P40
USABLE TRANSFERS_DETAIL_P30
USABLE TRANSFERS_DETAIL_P35
USABLE TRANSFERS_DETAIL_P10
USABLE TRANSFERS_DETAIL_P20

-- add a New Partition with no data movment

ALTER TABLE transfers_detail
ADD PARTITION TRANSFERS_DETAIL_P60 VALUES LESS THAN ( 60 )
TABLESPACE tbs_part_test

ALTER TABLE TRANSFERS_DETAIL
SPLIT PARTITION TRANSFERS_DETAIL_P60 AT (55)
INTO (
PARTITION TRANSFERS_DETAIL_P55 TABLESPACE TBS_PART_TEST ,
PARTITION TRANSFERS_DETAIL_P60 TABLESPACE TBS_PART_TEST);


select status, partition_name from dba_ind_partitions where index_owner='PART';

no UNUSABLE status found

-- Splitting Partition with data movement

ALTER TABLE TRANSFERS_DETAIL
SPLIT PARTITION TRANSFERS_DETAIL_P40 AT (38)
INTO (
PARTITION TRANSFERS_DETAIL_P38 TABLESPACE TBS_PART_TEST ,
PARTITION TRANSFERS_DETAIL_P40 TABLESPACE TBS_PART_TEST);


STATUS PARTITION_NAME
------ ----------------
USABLE TRANSFERS_DETAIL_P37
USABLE TRANSFERS_DETAIL_P60
USABLE TRANSFERS_DETAIL_P55
UNUSABLE TRANSFERS_DETAIL_P40
USABLE TRANSFERS_DETAIL_P30
UNUSABLE TRANSFERS_DETAIL_P38
USABLE TRANSFERS_DETAIL_P35
USABLE TRANSFERS_DETAIL_P10
USABLE TRANSFERS_DETAIL_P20

UNUSUABLE status found.


-- Partitioned Index Rebuilding

ALTER INDEX TRANSFERS_DETAIL_IDX2 REBUILD PARTITION TRANSFERS_DETAIL_P38 TABLESPACE TBS_PART_TEST_IND;
ALTER INDEX TRANSFERS_DETAIL_IDX2 REBUILD PARTITION TRANSFERS_DETAIL_P40 TABLESPACE TBS_PART_TEST_IND;