Oracle Database Discussions

Hi all,

I need a little help understanding and using DBMS_REDEFINITION.

I have a table that I need to transform in a partitioned table, keeping all the indexes, constraints, primary and foreign keys unaltered. I found the DBMS_REDEFINITION and figured it is the best choice since I have to transform a table with more than 1 million records and a BLOB field. BTW I am working in a Windows 2003 R2 SP2 64bit environment on an Oracle 10.2.0.4 DB.

To test the procedure I used a smaller table, with fewer rows. This is what I did:

CREATE TABLE LONGO.ARRI_RICORSO as SELECT * from PRODUCTION.ARRI_RICORSO;

CREATE TABLE "LONGO"."ARRI_RICORSO2" ( "ARRI_ID" NUMBER(12) NOT NULL , "ARVB_ID" NUMBER(12) NOT NULL , "ARIN_ID" NUMBER(12), 
"ARAV_ID" NUMBER(12), "ANAU_COD" VARCHAR2(4), "ANCO_ID" NUMBER(5), "ANNA_COD" CHAR(3), "ARRI_DATA" DATE,
 "ARRI_PROT" VARCHAR2(20), "ARRI_D_TRASM" DATE, "ARRI_F_ESITO" CHAR(1), "ARRI_D_ESITO" DATE, "ARRI_PROT_ESITO" VARCHAR2(20), 
"ERDO_ID_RICOR" NUMBER(12), "ERDO_ID_ESITO" NUMBER(12), 
"ERDO_ID_PROVV" NUMBER(12)) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 0K BUFFER_POOL DEFAULT)
 LOGGING PARTITION BY RANGE ("ARRI_DATA") (PARTITION "ARRI_RICORSO2_P1" VALUES LESS THAN (TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 'NLS_CALENDAR=GREGORIAN')) 
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
 LOGGING NOCOMPRESS , PARTITION "ARRI_RICORSO2_P2" VALUES LESS THAN (TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
 ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS , 
PARTITION "ARRI_RICORSO2_P3" VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
 LOGGING NOCOMPRESS , PARTITION "ARRI_RICORSO2_P4" VALUES LESS THAN ...... ETC up to 60 partitions, one per MONTH

So I have the first table full and with constraints (a couple of NOT NULL values) and a second, completely empty, partitioned table.

I started off with:

exec dbms_redefinition.can_redef_table('LONGO', 'ARRI_RICORSO');

that gave me the error

ERROR at line 1:
ORA-12089: cannot online redefine table "LONGO"."ARRI_RICORSO" with no primary
key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 1

So I modified the script with:

exec dbms_redefinition.can_redef_table('LONGO', 'ARRI_RICORSO',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

OK! After this I launched:

SQL> exec dbms_redefinition.start_redef_table('LONGO', 'ARRI_RICORSO', 'ARRI_RICORSO2');
BEGIN dbms_redefinition.start_redef_table('LONGO', 'ARRI_RICORSO', 'ARRI_RICORSO2'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "LONGO"."ARRI_RICORSO" with no primary
key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1

Same problem I thought!

SQL> exec dbms_redefinition.start_redef_table('LONGO', 'ARRI_RICORSO', 'ARRI_RICORSO2', dbms_redefinition.cons_use_rowid);
BEGIN dbms_redefinition.start_redef_table('LONGO', 'ARRI_RICORSO', 'ARRI_RICORSO2', dbms_redefinition.cons_use_rowid); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "LONGO"."ARRI_RICORSO" with no primary
key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1

...ok... Now I am stuck! What have I done wrong? I also read various documents that explained how to use correctly the procedure, but I can't understand why I am getting this error!

This is how the table is structured:

SQL> desc arri_ricorso
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

 ARRI_ID                                   NOT NULL NUMBER(12)
 ARVB_ID                                   NOT NULL NUMBER(12)
 ARIN_ID                                            NUMBER(12)
 ARAV_ID                                            NUMBER(12)
 ANAU_COD                                           VARCHAR2(4)
 ANCO_ID                                            NUMBER(5)
 ANNA_COD                                           CHAR(3)
 ARRI_DATA                                          DATE
 ARRI_PROT                                          VARCHAR2(20)
 ARRI_D_TRASM                                       DATE
 ARRI_F_ESITO                                       CHAR(1)
 ARRI_D_ESITO                                       DATE
 ARRI_PROT_ESITO                                    VARCHAR2(20)
 ERDO_ID_RICOR                                      NUMBER(12)
 ERDO_ID_ESITO                                      NUMBER(12)
 ERDO_ID_PROVV                                      NUMBER(12)

ARRI_RICORSO2 is exactly the same, but with no data in it and with partitions....

Please help! :)

Thanks in advance!

P.s. I also added a primary key to both the tables. That is when I got the ORA-42016 error while attempting the exec dbms_redefinition.start_redef_table process ant not the ORA-12089 anymore...

Edited by: Phelit on 17-mag-2013 6.07