Indexing issues while partitioning with dbms_redefinition

oracle-11gpartitioning

I get an error while doing the redefinition. Following are the steps which I have taken so far:

Step 1. Start redefinition

DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
 dbms_redefinition.CONS_USE_ROWID);

Successful

Step 2. Creating a interim table

CREATE TABLE "CDS_USER"."T_FDC_TOOLCONTEXT_ONLINE" 
(   "SERIAL_ID" NUMBER(15,0), 
"CONTEXTID" NUMBER(15,0) NOT NULL ENABLE, 
"ENTITYNAME" VARCHAR2(35 BYTE) NOT NULL ENABLE, 
"ENTITYCOMPONENT" VARCHAR2(10 BYTE), 
"SOLUTIONTYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
"JOBTIMESTAMP" DATE NOT NULL ENABLE, 
"EAJOBNAME" VARCHAR2(35 BYTE), 
"PRJOBNAME" VARCHAR2(35 BYTE), 
"FAJOBNAME" VARCHAR2(50 BYTE), 
"STEPNAME" VARCHAR2(80 BYTE), 
"LOTNAME" VARCHAR2(20 BYTE), 
"LOTTYPE" VARCHAR2(20 BYTE), 
"CARRIERNAME" VARCHAR2(20 BYTE), 
"PRODUCTNAME" VARCHAR2(80 BYTE), 
"RECIPENAME" VARCHAR2(80 BYTE), 
"WAFERID" VARCHAR2(23 BYTE), 
"MATERIALNAME" VARCHAR2(20 BYTE), 
"MATERIALCOUNT" NUMBER(4,0), 
"CAPACITY" NUMBER(4,0), 
"TECHNOLOGY" VARCHAR2(128 BYTE), 
"RETICLE" VARCHAR2(60 BYTE), 
"LAYER" VARCHAR2(20 BYTE), 
"SWRLOT" VARCHAR2(5 BYTE), 
"PORTNAME" NUMBER(1,0), 
"SLOT" NUMBER(3,0), 
"RUNNUMBER" NUMBER(2,0), 
"MVINTIME" DATE, 
"ROUTE" VARCHAR2(80 BYTE), 
"EPA" VARCHAR2(64 BYTE), 
"PROCTIME" DATE, 
"IMAGEID" VARCHAR2(25 BYTE), 
"PRODUCTGROUP" VARCHAR2(25 BYTE), 
"BASICTYPE" VARCHAR2(25 BYTE), 
"PRETOOL" VARCHAR2(25 BYTE), 
"REWORK" VARCHAR2(100 BYTE), 
"SENDAHEAD" VARCHAR2(100 BYTE), 
"SEQUENCE" VARCHAR2(80 BYTE), 
"RSN" VARCHAR2(25 BYTE), 
"COMPLETED" NUMBER(1,0) DEFAULT 0, 
"LAYER2" VARCHAR2(20 BYTE), 
"RETICLE2" VARCHAR2(60 BYTE), 
"TRUSTED_FLAG" VARCHAR2(2 BYTE), 
"VALID_FLAG" VARCHAR2(5 BYTE), 
"RECIPE" VARCHAR2(80 BYTE), 
"POSITIONTYPE" VARCHAR2(10 BYTE), 
"PROCESS_TYPE" VARCHAR2(3 BYTE), 
"IN_WORK" NUMBER(1,0) DEFAULT 0, 
"D_CARRIERNAME" VARCHAR2(25 BYTE), 
"SUMOSTEP" VARCHAR2(10 BYTE), 
"INSERTED_TIME" DATE DEFAULT SYSDATE, 
 PRIMARY KEY ("SERIAL_ID"))
PARTITION BY RANGE ("INSERTED_TIME")
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))

Successful

Step 3. Starting the redefinition

DBMS_REDEFINITION.START_REDEF_TABLE (uname => v_name, 
  orig_table => 'T_FDC_TOOLCONTEXT', 
  int_table => 'T_FDC_TOOLCONTEXT_ONLINE',
  col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

Successful

Step 4. Copying the dependents

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(v_name, 'T_FDC_TOOLCONTEXT',
  'T_FDC_TOOLCONTEXT_ONLINE', 
  dbms_redefinition.cons_orig_params,
  TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));

Error:

ORA-01408: such column list already indexed.

I have following indexes on my existing column.

CREATE UNIQUE INDEX "CDS_USER"."SYS_C0012094" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SERIAL_ID")
CREATE INDEX "CDS_USER"."I_FDC_CONTEXTID" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CONTEXTID") 
CREATE INDEX "CDS_USER"."I_FDC_ENTITY" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME")
CREATE INDEX "CDS_USER"."I_FDC_TIMESTAMP"ON"CDS_USER"."T_FDC_TOOLCONTEXT"("JOBTIMESTAMP") 
CREATE INDEX "CDS_USER"."I_FDC_CTRL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("EAJOBNAME") 
CREATE INDEX "CDS_USER"."I_FDC_PRJ" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PRJOBNAME")
CREATE INDEX "CDS_USER"."I_FDC_CARRIER" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CARRIERNAME")
CREATE INDEX "CDS_USER"."I_FDC_MATERIAL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("MATERIALNAME") 
CREATE INDEX "CDS_USER"."I_FDC_PORT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PORTNAME")
CREATE INDEX "CDS_USER"."I_FDC_SLOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SLOT")
CREATE INDEX "CDS_USER"."I_FDC_ENTITY_LOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME", "LOTNAME")

What can I do to avoid the error?

Best Answer

Ok, minimal test case that reproduces the error, showing it's the intermediate table PK that is the problem:

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  )
  7  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  8  (
  9    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 10  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>FALSE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2026
ORA-06512: at line 5


SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110330" ON "PH
IL"."SO33070_NEW" ("SERIA


SQL>
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

You have 2 options to fix this.

Option 1 - create the intermediate table without a primary key (best option!!):

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE
  5  )
  6  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  7  (
  8    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  9  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

no rows selected

SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

Option 2 - simply tell DBMS_REDEFINITION to ignore the error (ignore_errors=>true).

It looks like there are some errors, but they don't matter as the PK constraint and index was created when the intermediate table was created:

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110410" ON "PH
IL"."SO33070_NEW" ("SERIA

CONSTRAINT   PHIL   SO33070_ORIGINAL
ALTER TABLE "PHIL"."SO33070_NEW" ADD CONSTRAINT "TMP$$_
SYS_C00110410" PRIMARY KE


SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>
Related Question