Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

xml errors while Partitioning an existing table by DBMS_REDEFNITION

987518Jan 28 2013 — edited Jan 29 2013
I 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:
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 26 2013
Added on Jan 28 2013
3 comments
556 views