drop a partitioned index

User_KWXZ9

Hi Friends ,

I am using 10.2.0.4 oracle on solaris .

i have multiple partitioned local indexes from 2011 created on a daily basis . I tried to drop one of the indexes and got the below error .

SQL> ALTER INDEX QOSDEV.PK_RATE_CISCOMEMORYPOOL DROP PARTITION "05OCT2012"

ALTER INDEX QOSDEV.PK_RATE_CISMEPOOL DROP PARTITION "05OCT2012"

Error at line 2

ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index

Script Terminated on line 2.

I kindly request the way to drop these partitions .

Thanks ,

DBApps

Anand...

Hi,

Try --

alter table RATE_CISCOMEMORYPOOL drop partition "05OCT2012";

Anand

Anand...

Hi,

"QOSDEV.PK_RATE_CISMEPOOL"  index seems to be local index. Local index will be added implicitly to new partition when we add new partition on table. Likewise, we can not drop the local index on specific partition. It can be dropped automatically when we drop the partition from underlying table.

HTH

Anand

onkar.nath

Local indexes of a partition can not be dropped. Only the global partition indexes can.

Onkar

User_KWXZ9

Hi Anand ,

Ok i shall try droppin the underlying partition and update you .

Thanks ,

CloudDB

Dropping Index Partitions

You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked <a name="sthref524"></a>UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:

ALTER INDEX npr DROP PARTITION P1; ALTER INDEX npr REBUILD PARTITION P2; 

Maintaining Partitions

Anand...

Ok i shall try droppin the underlying partition and update you .

As you will need to drop the table partition, make sure what you are dropping is correct . If you have any global indexes, it will get unusable, so you would need to rebuild it.

Anand

User_KWXZ9

Hi ,

I am getting the below error .

SQL> alter table RATE_CISCOMEMORYPOOL drop partition 05OCT2012;

alter table RATE_CISCOMEMORYPOOL drop partition 05OCT2012

                                                *

ERROR at line 1:

ORA-14006: invalid partition name

i checked the table and partition name as below .

SQL> select table_name,PARTITION_NAME from dba_tab_partitions where PARTITION_NAME = '05OCT2012' and table_name = 'RATE_CISCOMEMORYPOOL';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
RATE_CISCOMEMORYPOOL           05OCT2012

can you please help me .

Anand...

Hi,

Try --

alter table RATE_CISCOMEMORYPOOL drop partition "05OCT2012";

Anand

User_KWXZ9

Thanks Anand .

now i will have to write the script to drop all the partitions until last year .

Regards,

DBApps