Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
You'll need to edit the question with the exact steps you've taken so far. There's not enough information to use in order to help you. – Phil Jan 17 at 10:51
Having said that, I think it'll be the PK on the new table. What does select * from dba_redefinition_errors; say? – Phil Jan 17 at 10:55
I have posted the question again. Please see. Thanks – Rajesh Arya Jan 17 at 12:12
If you observe the index 3 and index 10, both of them have one column in common. Is it causing the problem. But how is that existing in the original column? – Rajesh Arya Jan 17 at 12:13
The create table statement sets serial_id as pk, which will create an index. This will make statement 1 fail. – Phil Jan 17 at 12:26

1 Answer

up vote 1 down vote

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>
share|improve this answer
Thank you so much for your detailed answer. You solved a big problem for me. Just one more question, If I don't create the primary key, will it not be a problem?As I my original table has this primary key. will this primary key be automatically created during redefinition? – Rajesh Arya Jan 18 at 6:10
Yes, it will. Run my test cases yourself and you can see for yourself. – Phil Jan 18 at 9:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.