Forum Stats

  • 3,781,434 Users
  • 2,254,520 Discussions
  • 7,879,694 Comments

Discussions

drop a partitioned index

User_KWXZ9
User_KWXZ9 Member Posts: 232 Blue Ribbon
edited Mar 3, 2014 8:44AM in General Database Discussions

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

Tagged:

Best Answer

Answers

  • Anand...
    Anand... Member Posts: 3,590

    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
    onkar.nath Member Posts: 733

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

    Onkar

  • User_KWXZ9
    User_KWXZ9 Member Posts: 232 Blue Ribbon

    Hi Anand ,

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

    Thanks ,

  • CloudDB
    CloudDB Member Posts: 1,059 Gold Badge

    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...
    Anand... Member Posts: 3,590
    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
    User_KWXZ9 Member Posts: 232 Blue Ribbon

    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...
    Anand... Member Posts: 3,590
    Accepted Answer

    Hi,

    Try --

    alter table RATE_CISCOMEMORYPOOL drop partition "05OCT2012";
    

    Anand

  • User_KWXZ9
    User_KWXZ9 Member Posts: 232 Blue Ribbon

    Thanks Anand .

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

    Regards,

    DBApps

This discussion has been closed.