1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Getting xml errors while partitioning an existing table by DBMS_REDEFNITION

Discussion in 'SQL PL/SQL' started by Arya, Jan 28, 2013.

  1. Arya

    Arya Guest

    Hi, I am trying to partition an existing table and I am getting the following errors. Follwing are the steps that I have taken.

    1. Creating a table to be partitioned.


    Code (SQL):
     
    CREATE TABLE SO33070_ORIGINAL
    (
    SERIAL_ID NUMBER(15,0),
    INSERTED_TIME DATE DEFAULT SYSDATE,
    PRIMARY KEY (SERIAL_ID)
    );
     
    2. Checking if the table can be partitioned

    Code (SQL):
     
    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;
     
    3. Creating a duplicate table

    Code (SQL):
     
    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'))
    )
     
    3. Starting the redefnition process

    Code (SQL):
     
    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);
     
    4. Copying the dependents

    Code (SQL):
     
    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;
     
    And I get the following error

    Code (SQL):
     
    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:
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would be extremely helpful for you to report which release of Oracle you're using -- not knowing that information prevents any of us from providing any useful assistance. Running your example on 11.2.0.3 I find:

    Code (SQL):
    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> DECLARE
      2  v_name VARCHAR2(256);
      3  BEGIN
      4          SELECT sys_context('userenv', 'current_user') INTO v_name FROM dual;
      5          DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'SO33070_ORIGINAL', dbms_redefinition.CONS_USE_ROWID);
      6  END;
      7  /
     
    PL/SQL PROCEDURE successfully completed.
     
    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  )
     10  ;
     
    TABLE created.
     
    SQL>
    SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE( uname => USER, orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> EXEC dbms_redefinition.sync_interim_table(USER, 'SO33070_ORIGINAL', 'SO33070_NEW', NULL);
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2  num_errors NUMBER;
      3  BEGIN
      4          DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
      5                  uname => USER,
      6                  orig_table=>'SO33070_ORIGINAL',
      7                  int_table=>'SO33070_NEW',
      8                  copy_indexes=>dbms_redefinition.cons_orig_params ,
      9                  copy_triggers=>TRUE,
     10                  copy_constraints=>TRUE,
     11                  copy_privileges=>TRUE,
     12                  ignore_errors=>TRUE,
     13                  num_errors=>num_errors,
     14                  copy_statistics=>FALSE);
     15  END;
     16  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname => USER, orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', part_name => NULL)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    I generate no errors in that release of Oracle.