1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Renaming the existing the partition to new name

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jul 28, 2017.

  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,

    I have partitioned table with local index. We are renaming the existing partition name with new name .
    But after renaming the partition the existing local indexes are not reflecting to the renamed new partition name.Still there are referring to the old Partition name only

    Instead of the dropping and re creating the indexes is there any way to achieve the same ?

    Thanks in Advance
    Revathi.T
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Are the indexes unusable? I suspect not so there really isn't a problem. To rename everything for a partitioned table (including indexes) you first need to rename the table partitions then rename the INDEX partitions with the ALTER INDEX command as this example shows:

    ALTER INDEX scuba RENAME PARTITION sys_p3254 TO bcd_types;

    Renaming a table partition DOES change the reference the index uses (it now references the renamed TABLE partition) but it does NOT rename index partitions.
     
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thank you very much for your valuable information David .

    Indexes are valid .But I am making it as UNUSABLE when I am performing all renames for TABLE Partition , INDEX Partition. After that making them as USABLE.

    Thanks
    Revathi Thirunagari
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is the cost of renaming partitions. Unless you're running 12.2 indexes must be rebuilt.