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.

I have one index on it on date_of_transaction column :

Let us drop partition Y_2014

Let us check index status :

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

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

Here the SKIP_UNUSABLE_INDEXES init parameter comes in picture.

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.

Now let us try inserting some data in the table.

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

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:

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.