UNUSABLE Partitioned Indexes Query in Oracle
With the article ” UNUSABLE Partitioned Indexes Query in Oracle”, we examine all the invalid indexes that point to partitioned tables in the database users.
When it is necessary to delete a partition of a table with numerous partitions, great care must be taken during the drop of the partition. If the UPDATE GLOBAL INDEX clause is not declared at the end of the drop command, the global index of all partitions or the table will become invalid and therefore unusable.
This will cause significant problems because statements that operate on the table will cause full_table_scans and therefore significant general slowdowns due to excessive resource consumption (CPU, read disk I/O).
Another significant problem is the downtime of the table, which will last for the entire time of the index rebuild.
With the Query to identify Oracle partitioned indexes, we can determine if this dramatic situation has indeed occurred.
The fields we select are:
- OWNER: Owner of the index.
- INDEX_NAME: Name of the index.
- PARTITION_NAME: Name of the partition.
- STATUS: Status of the index.
Additionally, the above-mentioned information is seen from the DBA_IND_PARTITIONS view.
Finally, we filter the information based on the condition STATUS <> ‘VALID’ and INDEX_OWNER is different from SYS and SYSTEM.
UNUSABLE Partitioned Indexes Query in Oracle
Query for Invalid Partitioned Indexes in Oracle:
set lines 400
col owner for a10
col index_name for a25
select INDEX_OWNER,index_name,partition_name,status
from dba_ind_partitions
where status <>'VALID'
and index_owner not in ('SYSTEM','SYS')
order by 1,2;To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.
0 Comments