Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Global indexes become corrupted when a partition is dropped from the partition table and in this article we will learn how to fix these indexes as LOCAL indexes again.
Global indexes (for example Primary Key) that exist in a partition table are corrupted and become UNUSABLE when a partition is dropped from the partition table.
In this case, these indexes need to be rearranged as LOCAL indexes.
The steps are as follows.
1. When creating an index, the larger the table, the longer the process will take.
Therefore, we can list and delete unnecessary parititions.
Below are listed the partitions of the CHANNEL_STAT table that are older than 15 days.
These partitions can be easily deleted with the output of 5 columns.
1 2 3 | SQL> select object_name obn, subobject_name sbn, created c, rownum rw, 'alter table VQE.'||object_name||' drop partition '||subobject_name||';' from dba_objects where OBJECT_NAME in ('CHANNEL_STATS') AND subobject_name not in ('ST_01','STP_01') AND object_type='TABLE PARTITION' and created < sysdate -16 order by 3 desc; |
2. We check the index status (where index is PRIMARY KEY).
1 | SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS'); |
3. The unnecessary partitions we obtained with the first query are deleted.
1 | SQL> alter table VQE.STB_STATS drop partition SYS_P882; |
4. We check the index status again. After the partition deletion process done in the third step, PK indexes will be UNUSABLE.
This status is checked.
1 | SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS'); |
5. The PK indexes that cause the problem are deleted. We cannot add a new PK Index without doing this.
Also, we cannot delete the PK index with the “DROP INDEX <index_name>” sql code.
If we try to delete, we will get an error.
1 2 3 | SQL> alter table VQE.CHANNEL_STATS drop constraint CHANNEL_STATS_PK; SQL> alter table VQE.STB_STATS drop constraint STB_STATS_PK; |
6. Then these indexes are recreated in accordance with the PARTITION table.
One of these columns is the ID column and the other is the column we use for the PARTITION process.
The CREATE SCRIPT of the table to be partitioned is given at the bottom.
1 2 3 | SQL> ALTER TABLE VQE.STB_STATS ADD CONSTRAINT STB_STATS_PK1 PRIMARY KEY(STOP_TIME, ID) using index LOCAL; SQL> ALTER TABLE VQE.CHANNEL_STATS ADD CONSTRAINT CHANNEL_STATS_PK1 PRIMARY KEY(STOP_TIME, ID) using index LOCAL; |
7. Index statuses are checked again. If the work has been completed correctly, their status will appear as “N/A”.
1 | SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS'); |
8. The create script of the table being worked on is as follows.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE VQE.STB_STATS ( ID NUMBER NOT NULL, MAC CHAR(12 BYTE) NOT NULL, STOP_TIME DATE ) TABLESPACE VQE_TBS PARTITION BY RANGE (STOP_TIME) INTERVAL( NUMTODSINTERVAL(1, 'DAY'))( (PARTITION STP_01 VALUES LESS THAN (TO_DATE(' 2015-06-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE VQE_TBS )); |
