xml errors while Partitioning an existing table by DBMS_REDEFNITION
987518Jan 28 2013 — edited Jan 29 2013I am trying to partition an existing table through DBMS_REDEFNITION. Following are the steps that I have taken and the error I have got.
1. Creating a table to be partitioned.
CREATE TABLE SO33070_ORIGINAL
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE,
PRIMARY KEY (SERIAL_ID)
);
Success
2. Checking if the table can be partitioned
DECLARE
v_name VARCHAR2(256);
BEGIN
SELECT sys_context('userenv', 'current_user') INTO v_name FROM dual;
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'SO33070_ORIGINAL', dbms_redefinition.CONS_USE_ROWID);
END;
Success
3. Creating a duplicate table
CREATE TABLE SO33070_NEW
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE
)
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'))
)
Success
4. Starting the redefnition process
EXEC DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'CDS_USER', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
Success
5. Copying the dependents
DECLARE
num_errors NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'CDS_USER',
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);
END;
Here, I Get the following errors.
ORA-06502: PL/SQL: NUMERIC OR VALUE error
ORA-31606: XML context 27 does NOT MATCH any previously allocated context
ORA-06512: at "SYS.DBMS_METADATA", line 1475
ORA-06512: at "SYS.DBMS_METADATA", line 7481
ORA-06512: at "SYS.DBMS_REDEFINITION", line 803
ORA-06502: PL/SQL: NUMERIC OR VALUE error
ORA-31606: XML context 27 does NOT MATCH any previously allocated context
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1869
ORA-06512: at line 6
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action: