Author: Laurent Schneider

After table maintenance, like move or split partition, underlying indexes are marked as unusable.

This boils down to segment reorganisation, not dictionary change.

For instance :

CREATE TABLE t(x NUMBER)
PARTITION BY RANGE(x)
(PARTITION p1 VALUES LESS THAN (MAXVALUE));

CREATE INDEX i ON t (x);

INSERT INTO t VALUES (1);

ALTER TABLE T SPLIT PARTITION p1 AT 
  (100) INTO (PARTITION p2, PARTITION p3);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';

INDEX_NAME STATUS  
---------- --------
I          VALID

The partition p1 is splitted into two p2 and p3, all rows from p1 belongs to p2, p3 is created empty, no data manipulation, the index remains valid

delete from t; 
INSERT INTO t VALUES (250);

ALTER TABLE T SPLIT PARTITION p3 AT 
  (200) INTO (PARTITION p4, PARTITION p5);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';

INDEX_NAME STATUS  
---------- --------
I          VALID

delete from t; 
INSERT INTO t VALUES (250);
INSERT INTO t VALUES (350);

ALTER TABLE T SPLIT PARTITION p5 AT 
  (300) INTO (PARTITION p6, PARTITION p7);

SELECT index_name, status 
FROM user_indexes 
WHERE index_name = 'I';
INDEX_NAME STATUS  
---------- --------
I          UNUSABLE

One row goes to p6, one row to p7, the index is invalidated.

To avoid this, use ALTER TABLE T SPLIT PARTITION p5 AT (300) INTO (PARTITION p6, PARTITION p7) UPDATE INDEXES;

What are the consequences?

INSERT INTO t VALUES (320);
1 row inserted

It does not prevent DML in this case.

select * from t where x=320;

         X
----------
       320

Execution Plan
---------------------------------------------------
 0   SELECT STATEMENT Optimizer Mode=ALL_ROWS
 1 0   PARTITION RANGE SINGLE
 2 1     TABLE ACCESS FULL SCOTT.T

The row is retrieved, but the unusable index is not used.

In some case, DML may be prevented.

alter index i rebuild;
alter table t modify (x primary key using index);
ALTER TABLE T SPLIT PARTITION p7 AT 
  (330) INTO (PARTITION p8, PARTITION p9) ;

insert into t values (450);
ORA-01502: index 'SCOTT.I' or partition of such index is in unusable state

The index can no longer be used for constraint enforcement and the INSERT fails.

If the index is partitioned, the index is not marked as unusable as a whole, only the affected (sub)partitions are marked.

Check all your indexes status in dba_indexes.status, dba_ind_partitions.status and dba_ind_subpartitions.status.

Rebuild them with alter index i rebuild, alter index i rebuild partition p and alter index i rebuild subpartition sp.

You need to check the high_value from dba_tab_partitions.
Or you you could metadata.

With metadata, it is not a long, it either clob or clob-xml.

  SELECT t.table_name,
         MAX (
            TO_DATE (
               REGEXP_SUBSTR (
                  EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
                  ' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
               'YYYY-MM-DD HH24:MI;:SS'))
            high_value
    FROM user_tables t,
         TABLE (
            XMLSEQUENCE (
               EXTRACT (
                  xmltype (DBMS_METADATA.get_xml ('TABLE', t.table_name)),
                  '//ROWSET/ROW/TABLE_T/PART_OBJ/PART_LIST/PART_LIST_ITEM/HIBOUNDVAL'))) x
   WHERE partitioned = 'YES'
  HAVING MAX (
            TO_DATE (
               REGEXP_SUBSTR (
                  EXTRACT ( (VALUE (x)), 'HIBOUNDVAL').getStringVal (),
                  ' \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'),
               'YYYY-MM-DD HH24:MI;:SS')) < DATE '2015-01-02'
GROUP BY t.table_name
ORDER BY 1;

TABLE_NAME HIGH_VALUE                    
---------- ------------------------------
T1         2015-01-01                    
T11        2000-01-01                    
T20        1436-03-09                    
T6         2000-01-01                    
T7         2014-12-21                    
T8         2015-01-01

Ok, out of xml, I get all HIBOUNDVAL and hazardously try some regular expression to find a pattern.

It already returned most of my table that could have caused me New Eve headache. But partitioned is not that simple. Line 3 for instance is not in a gregorian format (it’s a bug). And it does not cover index partition, subpartitions, interval partitions, neither partition with more than one date column as key.

This sounds a lot, and there is only one HIGH_VALUE for multiple key, and the High_value is something like 0,TO_DATE(' 3543-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),MAXVALUE

Okay, if you forget about non-gregorian calendars, and ignore about MAXVALUE, we could use something like

curid := DBMS_SQL.open_cursor;
txt :=
  'select '
  || REPLACE (g.high_value, 'MAXVALUE', 'NULL')
  || ' from dual';
DBMS_SQL.parse (curid, txt, DBMS_SQL.NATIVE);

Then from dba_PART_KEY_COLUMNS (pkc) or dba_SUBPART_KEY_COLUMNS, joined with dba_tab_columns, we could retrieve the datatype (date and timestamp with or without time zone) and the pkc.column_position.

DBMS_SQL.DEFINE_COLUMN (curid,
  f.column_position,
  'date',
  12);

Casting to date should be fine.

Then we fetch and retrieve the Nth column

IF DBMS_SQL.FETCH_ROWS (curid) > 0
THEN
  DBMS_SQL.COLUMN_VALUE (curid, f.column_position, namevar);
END IF;

If you read me that far, you probably can write the rest of the code for yourself.

Could be useful to do this before end-of-year 😉