Hi Experts,
I have a process which creates a table with partitions and load data.
So while creating the table the index is created with unusable and once the data is loaded the indexes are to be made usable.
However all the indexes are local indexes so when the table is created the index status is showing ‘N/A’ in user_Indexes view and User_ind_Partitions there are no entries as there are no partitions yet and just a transition parition.
The moment data is loaded, and with out making the index usable also, User_ind_Partitions view is showing the indexes are usable.
I want to check is there any way to check when the indexes are made usable is it before the data load or after the data load.
Thanks in advance we are using oracle 19c.
much appreciate your responses.
sample code snippet :
– This is partition
PARTITION BY RANGE (AS_OF_DATE) INTERVAL ( NUMTODSINTERVAL(7, ''DAY''))
STORE IN (TS_BALANCE_PRIORDAY_BH)
( PARTITION TRANSITION_PARTITION
VALUES LESS THAN ( TO_DATE(''1999-01-04 00:00:00'', ''YYYY-MM-DD HH24:MI:SS''))
)
– This is index creation script
CREATE INDEX OFX.IDX01_Balp1 ON OFX.Balance_P
(AS_OF_DATE,ACCOUNT_ID )
LOGGING
LOCAL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
UNUSABLE TABLESPACE TS_IDX_BALANCE_PRIORDAY_BH ;
--Enable the index
dbms\_pclxutil.build\_part\_index(3,8, ‘BALANCE\_P’ ,V\_ind.Index\_Name,TRUE);