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.<\/p>\n
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.<\/p>\n
In this case, these indexes need to be rearranged as LOCAL indexes.<\/p>\n
The steps are as follows.<\/p>\n
1. When creating an index, the larger the table, the longer the process will take.<\/p>\n
Therefore, we can list and delete unnecessary parititions.<\/p>\n
Below are listed the partitions of the CHANNEL_STAT table that are older than 15 days.<\/p>\n
These partitions can be easily deleted with the output of 5 columns.<\/p>\n
SQL> select object_name obn, subobject_name sbn, created c, rownum rw, 'alter table VQE.'||object_name||' drop \r\npartition '||subobject_name||';' from dba_objects where OBJECT_NAME in ('CHANNEL_STATS') AND subobject_name \r\nnot in ('ST_01','STP_01') AND object_type='TABLE PARTITION' and created < sysdate -16 order by 3 desc;\r\n<\/pre>\n2. We check the index status (where index is PRIMARY KEY).<\/p>\n
SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS');\r\n<\/pre>\n3. The unnecessary partitions we obtained with the first query are deleted.<\/p>\n
SQL> alter table VQE.STB_STATS drop partition SYS_P882;<\/pre>\n4. We check the index status again. After the partition deletion process done in the third step, PK indexes will be UNUSABLE.<\/p>\n
This status is checked.<\/p>\n
SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS');\r\n<\/pre>\n5. The PK indexes that cause the problem are deleted. We cannot add a new PK Index without doing this.<\/p>\n
Also, we cannot delete the PK index with the “DROP INDEX <index_name>” sql code.<\/p>\n
If we try to delete, we will get an error.<\/p>\n
SQL> alter table VQE.CHANNEL_STATS drop constraint CHANNEL_STATS_PK;\r\n\u00a0\r\nSQL> alter table VQE.STB_STATS drop constraint STB_STATS_PK;<\/pre>\n6. Then these indexes are recreated in accordance with the PARTITION table.<\/p>\n
One of these columns is the ID column and the other is the column we use for the PARTITION process.<\/p>\n
The CREATE SCRIPT of the table to be partitioned is given at the bottom.<\/p>\n
SQL> ALTER TABLE VQE.STB_STATS ADD CONSTRAINT STB_STATS_PK1 PRIMARY KEY(STOP_TIME, ID) using index LOCAL;\r\n\u00a0\r\nSQL> ALTER TABLE VQE.CHANNEL_STATS ADD CONSTRAINT CHANNEL_STATS_PK1 PRIMARY KEY(STOP_TIME, ID) using index LOCAL;<\/pre>\n7. Index statuses are checked again. If the work has been completed correctly, their status will appear as “N\/A”.<\/p>\n
SQL> select status from dba_indexes where table_owner='VQE' AND table_name in ('CHANNEL_STATS', 'STB_STATS');<\/pre>\n8. The create script of the table being worked on is as follows.<\/p>\n
CREATE TABLE VQE.STB_STATS\r\n(\r\n ID NUMBER NOT NULL,\r\n MAC CHAR(12 BYTE) NOT NULL,\r\n STOP_TIME DATE\r\n)\r\nTABLESPACE VQE_TBS\r\nPARTITION BY RANGE (STOP_TIME)\r\nINTERVAL( NUMTODSINTERVAL(1, 'DAY'))(\r\n(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 ));<\/pre>\n<\/div>\n