3 Replies Latest reply on Jan 29, 2013 12:59 PM by 987518

    xml errors while Partitioning an existing table by DBMS_REDEFNITION

    987518
      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:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
        • 1. Re: xml errors while Partitioning an existing table by DBMS_REDEFNITION
          Rahul_India
          Hi welcome to the forum

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: xml errors while Partitioning an existing table by DBMS_REDEFNITION
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
            >
            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
            >
            That code works fine for me using 11.2.0.1 vanilla. The only change I made was using SCOTT for the user. I temporarily granted the DBA role to SCOTT.

            Have you confirmed that you granted ALL necessary privileges directly to the user? Privs granted via roles are disabled in PL/SQL.
            http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006801
            >
            Privileges Required for the DBMS_REDEFINITION Package
            Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

            •CREATE ANY TABLE

            •ALTER ANY TABLE

            •DROP ANY TABLE

            •LOCK ANY TABLE

            •SELECT ANY TABLE

            The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

            •CREATE ANY TRIGGER

            •CREATE ANY INDEX
            • 3. Re: xml errors while Partitioning an existing table by DBMS_REDEFNITION
              987518
              This is the output I got when I ran your query.


              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
              PL/SQL Release 11.2.0.3.0 - Production
              CORE     11.2.0.3.0     Production
              TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
              NLSRTL Version 11.2.0.3.0 - Production

              Note: I am using Oracle client and Sql Developer to execute my queries.