Hi, Oracle EE 19.21 here.
I'm trying to change the size of the partioning key column used in a partitioned table using dbms_redefinition.
There is no data in the original table. This is not a simple matter of dropping and recreating with the correct column size. This process will eventually be carried out in another system where dropping and recreating a table would be very difficult.
Original Table is list partitioned by ACCTID:
Name Null? Type
-------------- -------- ------------
ACCTID NOT NULL NUMBER(10)
USERID NOT NULL NUMBER(10)
LOTYPE NUMBER(10)
ASSIGNMENTID NOT NULL NUMBER(10)
LOID NUMBER(10)
ATTEMPT NOT NULL NUMBER(3)
START_TIME TIMESTAMP(6)
LAST_MOD TIMESTAMP(6)
SIMPATH_STEPID NUMBER(1)
Redefinition Table:
CREATE TABLE "REDEF_ASSIGNMENTS_INPROGRESS"
( "ACCTID" INTEGER,
"USERID" NUMBER(10,0),
"LOTYPE" NUMBER(10,0),
"ASSIGNMENTID" NUMBER(10,0),
"LOID" NUMBER(10,0),
"ATTEMPT" NUMBER(3,0),
"START_TIME" TIMESTAMP (6),
"LAST_MOD" TIMESTAMP (6),
"SIMPATH_STEPID" NUMBER(1,0) DEFAULT 1 )
PARTITION BY LIST ("ACCTID")
(PARTITION "PTN_1000" VALUES (1000) ) ;
The column I'm wanting to change is ACCTID from NUMBER(10) to INTEGER.
I receive an “ORA-06502: PL/SQL: numeric or value error: character to number conversion error” when I run this:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('<SCHEMA>,'ASSIGNMENTS_INPROGRESS', 'REDEF_ASSIGNMENTS_INPROGRESS');
END;
/
Thanks in advance for any help.