Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES parameter

In this article we will see how we can drop the PARTITION without any index maintenance at run time and doing it later.

I have following partitions available for my table TRANSACTIONS_NEW.

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS_NEW';

TABLE_NAME		       PARTITION_NAME
------------------------------ ------------------------------
TRANSACTIONS_NEW	       Y_2011
TRANSACTIONS_NEW	       Y_2012
TRANSACTIONS_NEW	       Y_2013
TRANSACTIONS_NEW	       Y_2014
TRANSACTIONS_NEW	       Y_2015
TRANSACTIONS_NEW	       Y_2016
TRANSACTIONS_NEW	       Y_2017
TRANSACTIONS_NEW	       Y_2018
TRANSACTIONS_NEW	       Y_2019

9 rows selected.

I have one index on it on date_of_transaction column :

SQL> 
SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS,
 STATUS, TABLESPACE_NAME, PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='TRANSACTIONS_NEW';SQL>   2    3    4  

INDEX_NAME															 INDEX_TYPE		     UNIQUENES STATUS	TABLESPACE_NAME 	       PAR
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- --------- -------- ------------------------------ ---
TRANSACTION_NEW_IDX														 NORMAL 		     NONUNIQUE VALID	SYSTEM		            NO

Let us drop partition Y_2014

SQL> ALTER TABLE TRANSACTIONS_NEW DROP PARTITION Y_2014;

Table altered.

Let us check index status :

SQL> 
SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS,
 STATUS, TABLESPACE_NAME, PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='TRANSACTIONS_NEW';SQL>   2    3    4  

INDEX_NAME															 INDEX_TYPE		     UNIQUENES STATUS	TABLESPACE_NAME 	       PAR
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- --------- -------- ------------------------------ ---
TRANSACTION_NEW_IDX														 NORMAL 		     NONUNIQUE UNUSABLE SYSTEM		            NO

We can see index is in unusable state now. Let us try to insert some records in the table and see what happens.

SQL> INSERT INTO TRANSACTIONS VALUES (1111,TO_DATE('22-JAN-2019','DD-MON-YYYY'),22,11111111111);

1 row created.

SQL> COMMIT;

Commit complete.

I am able to insert records in the table then what is the impact ?

Here the SKIP_UNUSABLE_INDEXES init parameter comes in picture.

SQL> SHOW PARAMETER SKIP

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes		     boolean	 TRUE

This parameter if set to true skips all the indexes which are in unusable state. So it allows data insertion. Now lets set it to false and see the impact.

SQL> ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES=FALSE;

System altered.

Now let us try inserting some data in the table.

SQL> INSERT INTO TRANSACTIONS_NEW VALUES (1111,TO_DATE('22-JAN-2014','DD-MON-YYYY'),222,2);
INSERT INTO TRANSACTIONS_NEW VALUES (1111,TO_DATE('22-JAN-2014','DD-MON-YYYY'),222,2)
*
ERROR at line 1:
ORA-01502: index 'SYS.TRANSACTION_NEW_IDX' or partition of such index is in unusable state

We can see here it gives error and discarded row insertion operation.Now let us rebuild the index and insert the same row.

SQL> ALTER INDEX TRANSACTION_NEW_IDX REBUILD;

Index altered.

SQL> SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS,
 STATUS, TABLESPACE_NAME, PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='TRANSACTIONS_NEW';  2    3    4  

INDEX_NAME															 INDEX_TYPE		     UNIQUENES STATUS	TABLESPACE_NAME 	       PAR
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- --------- -------- ------------------------------ ---
TRANSACTION_NEW_IDX														 NORMAL 		     NONUNIQUE VALID	SYSTEM		            NO

SQL> 
SQL> INSERT INTO TRANSACTIONS_NEW VALUES (1111,TO_DATE('22-JAN-2014','DD-MON-YYYY'),222,2);

1 row created.

SQL> COMMIT;

Commit complete.

We can see in above output after rebuilding index index became valid and we are able to insert rows in the table.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp