4 Replies Latest reply: Sep 28, 2011 4:36 PM by Barbara Boehmer RSS

    bug using dbms_redefinition on table with altered text index?

    Barbara Boehmer
      I think I may have encountered a bug when using DBMS_REDEFINITION on a table with a text index that has been altered using ALTER INDEX index_name REBUILD PARAMETERS (REPLACE ...). It seems that DBMS_REDEFINITION does not recognize the syntax with REPLACE and the redefinition fails. However, if I drop the text index and recreate it with all of the parameters set during the initial creation and no ALTER INDEX commands, then redo the redefinition, it works properly. I have provided a script below that reproduces the problem, then uses the workaround just mentioned. I have provided the execution of the script and a copy of the script separately, so that it can be copied and pasted to reproduce the problem. This is a simplification of a problem that arose during diagnosis of a larger problem presented by someone on another forum, where the object was to loop through a group of tables that met certain criteria and change certain varchar2 columns to nvarchar2 columns in those tables, where there were text indexes on other columns in the tables, and those indexes had been altered using the aforementioned syntax. Does this appear to be a bug or am I missing something or is there an easier workaround for the redefinition?

      -- version:
      SCOTT@orcl_11gR2> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      5 rows selected.
      
      SCOTT@orcl_11gR2>
      -- execution of script:
      SCOTT@orcl_11gR2> -- table, data, altered text index:
      SCOTT@orcl_11gR2> CREATE TABLE t_test
        2    (col1  NUMBER PRIMARY KEY,
        3       col2  VARCHAR2 (10),
        4       col3  CLOB)
        5  /
      
      Table created.
      
      SCOTT@orcl_11gR2> INSERT INTO t_test VALUES (1, 'A', 'test data')
        2  /
      
      1 row created.
      
      SCOTT@orcl_11gR2> CREATE INDEX i1 ON t_test (col3) INDEXTYPE IS CTXSYS.CONTEXT
        2  /
      
      Index created.
      
      SCOTT@orcl_11gR2> ALTER INDEX i1 REBUILD PARAMETERS ('REPLACE SYNC (ON COMMIT)')
        2  /
      
      Index altered.
      
      SCOTT@orcl_11gR2> SELECT * FROM t_test WHERE CONTAINS (col3, 'test data') > 0
        2  /
      
            COL1 COL2       COL3
      ---------- ---------- ----------
               1 A          test data
      
      1 row selected.
      
      SCOTT@orcl_11gR2> -- redefinition that fails:
      SCOTT@orcl_11gR2> CREATE TABLE t_test_interim
        2    (col1  NUMBER,
        3       col2  NVARCHAR2 (10),
        4       col3  CLOB)
        5  /
      
      Table created.
      
      SCOTT@orcl_11gR2> DECLARE
        2    v_num_errors  NUMBER;
        3  BEGIN
        4    DBMS_REDEFINITION.CAN_REDEF_TABLE
        5        (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
        6    DBMS_REDEFINITION.START_REDEF_TABLE
        7        (USER, 'T_TEST', 'T_TEST_INTERIM',
        8         'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
        9          DBMS_REDEFINITION.CONS_USE_PK);
       10    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
       11        (USER, 'T_TEST', 'T_TEST_INTERIM');
       12    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
       13        (USER, 'T_TEST', 'T_TEST_INTERIM',
       14         DBMS_REDEFINITION.CONS_ORIG_PARAMS,
       15         TRUE, TRUE, TRUE, FALSE, v_num_errors, TRUE);
       16    DBMS_REDEFINITION.FINISH_REDEF_TABLE
       17        (USER, 'T_TEST', 'T_TEST_INTERIM');
       18  END;
       19  /
      DECLARE
      *
      ERROR at line 1:
      ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-20000: Oracle Text error:
      DRG-11000: invalid keyword REPLACE
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 2025
      ORA-06512: at line 12
      
      
      SCOTT@orcl_11gR2> -- clean up the mess:
      SCOTT@orcl_11gR2> DROP MATERIALIZED VIEW t_test_interim
        2  /
      
      Materialized view dropped.
      
      SCOTT@orcl_11gR2> DROP TABLE t_test_interim CASCADE CONSTRAINTS
        2  /
      
      Table dropped.
      
      SCOTT@orcl_11gR2> BEGIN
        2    DBMS_REDEFINITION.ABORT_REDEF_TABLE
        3        (USER, 'T_TEST', 'T_TEST_INTERIM');
        4  END;
        5  /
      
      PL/SQL procedure successfully completed.
      
      SCOTT@orcl_11gR2> -- drop and recreate index with all parameters without altering:
      SCOTT@orcl_11gR2> DROP INDEX I1
        2  /
      
      Index dropped.
      
      SCOTT@orcl_11gR2> CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
        2  PARAMETERS ('SYNC (ON COMMIT)')
        3  /
      
      Index created.
      
      SCOTT@orcl_11gR2> -- redo redefinition:
      SCOTT@orcl_11gR2> CREATE TABLE t_test_interim
        2    (col1  NUMBER,
        3       col2  NVARCHAR2 (10),
        4       col3  CLOB)
        5  /
      
      Table created.
      
      SCOTT@orcl_11gR2> DECLARE
        2    v_num_errors  NUMBER;
        3  BEGIN
        4    DBMS_REDEFINITION.CAN_REDEF_TABLE
        5        (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
        6    DBMS_REDEFINITION.START_REDEF_TABLE
        7        (USER, 'T_TEST', 'T_TEST_INTERIM',
        8         'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
        9          DBMS_REDEFINITION.CONS_USE_PK);
       10    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
       11        (USER, 'T_TEST', 'T_TEST_INTERIM');
       12    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
       13        (USER, 'T_TEST', 'T_TEST_INTERIM',
       14         DBMS_REDEFINITION.CONS_ORIG_PARAMS,
       15         TRUE, TRUE, TRUE, FALSE, v_num_errors, TRUE);
       16    DBMS_REDEFINITION.FINISH_REDEF_TABLE
       17        (USER, 'T_TEST', 'T_TEST_INTERIM');
       18  END;
       19  /
      
      PL/SQL procedure successfully completed.
      
      SCOTT@orcl_11gR2> DROP TABLE t_test_interim CASCADE CONSTRAINTS
        2  /
      
      Table dropped.
      
      SCOTT@orcl_11gR2> -- results:
      SCOTT@orcl_11gR2> DESC t_test
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       COL1                                               NUMBER
       COL2                                               NVARCHAR2(10)
       COL3                                               CLOB
      
      SCOTT@orcl_11gR2> COLUMN col3 FORMAT A10
      SCOTT@orcl_11gR2> SELECT * FROM t_test WHERE CONTAINS (col3, 'test data') > 0
        2  /
      
            COL1 COL2       COL3
      ---------- ---------- ----------
               1 A          test data
      
      1 row selected.
      
      SCOTT@orcl_11gR2> -- clean-up:
      SCOTT@orcl_11gR2> DROP TABLE t_test
        2  /
      
      Table dropped.
      
      SCOTT@orcl_11gR2>
      -- script:
      -- table, data, altered text index:
      CREATE TABLE t_test
        (col1  NUMBER PRIMARY KEY,
         col2  VARCHAR2 (10),
         col3  CLOB)
      /
      INSERT INTO t_test VALUES (1, 'A', 'test data')
      /
      CREATE INDEX i1 ON t_test (col3) INDEXTYPE IS CTXSYS.CONTEXT
      /
      ALTER INDEX i1 REBUILD PARAMETERS ('REPLACE SYNC (ON COMMIT)')
      /
      SELECT * FROM t_test WHERE CONTAINS (col3, 'test data') > 0
      /
      -- redefinition that fails:
      CREATE TABLE t_test_interim
        (col1  NUMBER,
         col2  NVARCHAR2 (10),
         col3  CLOB)
      /
      DECLARE 
        v_num_errors  NUMBER;
      BEGIN 
        DBMS_REDEFINITION.CAN_REDEF_TABLE
          (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
        DBMS_REDEFINITION.START_REDEF_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM',
           'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
            DBMS_REDEFINITION.CONS_USE_PK);
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM');
        DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
          (USER, 'T_TEST', 'T_TEST_INTERIM',
           DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
           TRUE, TRUE, TRUE, FALSE, v_num_errors, TRUE);
        DBMS_REDEFINITION.FINISH_REDEF_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM');
      END;
      /
      -- clean up the mess:
      DROP MATERIALIZED VIEW t_test_interim
      /
      DROP TABLE t_test_interim CASCADE CONSTRAINTS
      /
      BEGIN 
        DBMS_REDEFINITION.ABORT_REDEF_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM');
      END;
      /
      -- drop and recreate index with all parameters without altering:
      DROP INDEX I1
      /
      CREATE INDEX I1 ON T_TEST (COL3) INDEXTYPE IS CTXSYS.CONTEXT
      PARAMETERS ('SYNC (ON COMMIT)')
      /
      -- redo redefinition:
      CREATE TABLE t_test_interim
        (col1  NUMBER,
         col2  NVARCHAR2 (10),
         col3  CLOB)
      /
      DECLARE 
        v_num_errors  NUMBER;
      BEGIN 
        DBMS_REDEFINITION.CAN_REDEF_TABLE
          (USER, 'T_TEST', DBMS_REDEFINITION.CONS_USE_PK);
        DBMS_REDEFINITION.START_REDEF_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM',
           'COL1 COL1,TO_NCHAR(COL2) COL2,COL3 COL3',
            DBMS_REDEFINITION.CONS_USE_PK);
        DBMS_REDEFINITION.SYNC_INTERIM_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM');
        DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
          (USER, 'T_TEST', 'T_TEST_INTERIM',
           DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
           TRUE, TRUE, TRUE, FALSE, v_num_errors, TRUE);
        DBMS_REDEFINITION.FINISH_REDEF_TABLE
          (USER, 'T_TEST', 'T_TEST_INTERIM');
      END;
      /
      DROP TABLE t_test_interim CASCADE CONSTRAINTS
      /
      -- results:
      DESC t_test
      COLUMN col3 FORMAT A10
      SELECT * FROM t_test WHERE CONTAINS (col3, 'test data') > 0
      /
      -- clean-up:
      DROP TABLE t_test
      /