How can I alter the primary key of an index-organized table? We started with a naive primary key:

SQL&gt; CREATE TABLE books(
        title VARCHAR2(10),
        CONSTRAINT pk_title PRIMARY KEY(title))
      ORGANIZATION INDEX;

Table created.


Then realized it is not unique. Say, a book can be re-released under the same title:

SQL&gt; ALTER TABLE books ADD(release_date DATE NOT NULL);

Table altered.


Now to insert a re-released book, we have to relax the uniqueness on title somehow.

But I cannot drop and recreate the PK due to the table being index organized:

SQL&gt; ALTER TABLE books DROP PRIMARY KEY;
ALTER TABLE books DROP PRIMARY KEY
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for
index-organized tables or sorted hash cluster


I cannot manage to extend the existing key in-place either:

SQL&gt; CREATE UNIQUE INDEX pk_title_date ON books(title, release_date);

Index created.

SQL&gt; ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date;
ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.


What are my options ? Is it possible without moving data or recreating the table (the actual table is huge) ?
An index-organized table (IOT) is just that; an index with no "real" table.  All data besides the primary key are just "tacked on" to the primary key, so for example if you have an IOT with 6 columns, 2 of which make up the primary key, behind the scenes you just have an index with 6 columns, of which the first 2 columns make it unique.

So, sorry, only way to fix it is to recreate the table; to improve the rebuild time temporarily disable logging.  You cannot use an append hint on inserts to an  IOT table to force a direct path insert: 

SQL&gt; CREATE TABLE books_new (
    title VARCHAR2(10),
    release_date DATE NOT NULL,
    CONSTRAINT pk_title PRIMARY KEY(title,release_date))
  ORGANIZATION INDEX NOLOGGING;

SQL&gt; INSERT INTO books_new 
     (SELECT title, '01-Jan-1980' FROM books);
SQL&gt; ALTER TABLE books_new LOGGING;
SQL&gt; ALTER TABLE books RENAME TO books_old;
SQL&gt; ALTER TABLE books_new RENAME TO books;