3 Replies Latest reply: Dec 29, 2011 6:32 PM by 879994 RSS

    Text Index on MATERIALIZED VIEW giving error while DML on base table

    879994
      Hi Gurus,

      For providing search on different TEXT columns from different tables, I created a MATERIALIZED VIEW containing all the text columns from different tables and created text index on the MATERIALIZED VIEW using MULTI_COLUMN_DATASTORE. But when I am committing transaction on base table. I am getting following error.

      SQL> commit;
      commit
      *
      ERROR at line 1:
      ORA-00603: ORACLE server session terminated by fatal error


      Here What I did.
      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
      PL/SQL Release 10.2.0.3.0 - Production
      CORE    10.2.0.3.0      Production
      TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      
      SQL> CREATE TABLE test_table_1(id  NUMBER, text_1  VARCHAR2 (30));
      
      Table created.
      
      SQL> CREATE TABLE test_table_2(id  NUMBER, text_2  VARCHAR2 (30));
      
      Table created.
      
      SQL> CREATE MATERIALIZED VIEW LOG ON test_table_1 WITH ROWID INCLUDING NEW VALUES;
      
      Materialized view log created.
      
      SQL> CREATE MATERIALIZED VIEW LOG ON test_table_2 WITH ROWID INCLUDING NEW VALUES;
      
      Materialized view log created.
      
      SQL> ed
      Wrote file afiedt.buf
      
        1   CREATE MATERIALIZED VIEW my_test_view
        2      REFRESH FAST WITH ROWID ON COMMIT
        3      AS
        4      SELECT a.rowid table1_rowid, b.rowid table2_rowid,
        5              a.text_1, b.text_2
        6      FROM   test_table_1 a ,test_table_2 b
        7*     WHERE  a.id = b.id
      SQL> /
      
      Materialized view created.
      
      SQL>  BEGIN
        2        CTX_DDL.CREATE_PREFERENCE ('my_test_datastore', 'MULTI_COLUMN_DATASTORE');
        3        CTX_DDL.SET_ATTRIBUTE ('my_test_datastore', 'COLUMNS', 'text_1, text_2');
        4      END;
        5  /
      
      PL/SQL procedure successfully completed.
      
      SQL>  CREATE INDEX test_idx
        2     ON my_test_view (text_1)
        3     INDEXTYPE IS CTXSYS.CONTEXT
        4     PARAMETERS
        5     ('DATASTORE my_test_datastore SYNC (ON COMMIT) FILTER CTXSYS.AUTO_FILTER');
      
      Index created.
      
      SQL> insert into test_table_1 values( 100,'This is the text1');
      
      1 row created.
      
      SQL>   insert into test_table_2 values( 100,'This is the text2');
      
      1 row created.
      
      SQL> commit;
      commit
      *
      ERROR at line 1:
      ORA-00603: ORACLE server session terminated by fatal error
      When I commit with Text index Present, I am getting above error
      -- Again Connecting Oracle
      
      SQL> conn <username>/<password>@ORCL
      
      SQL> drop index test_idx;
      
      Index dropped.
      
      SQL> update test_table_1
        2  set text_1='Second Update'
        3  /
      
      2 rows updated.
      
      SQL> commit;
      
      Commit complete.
      After dropping TEXT index, There is no error on commit as shown above.
      -- Again Creating TEXT index
      
      SQL> CREATE INDEX test_idx
        2     ON my_test_view (text_1)
        3     INDEXTYPE IS CTXSYS.CONTEXT
        4     PARAMETERS
        5        ('DATASTORE my_test_datastore SYNC (ON COMMIT) FILTER CTXSYS.AUTO_FILTER');
      
      Index created.
      
      
      SQL>  SELECT text_1,text_2
        2     FROM  my_test_view
        3     WHERE  CONTAINS (text_1, 'update') > 0;
      
      TEXT_1                         TEXT_2
      ------------------------------ ------------------------------
      Second Update                  First Update_value
      Second Update                  First Update_value
      
      SQL> update test_table_1
        2  set text_1='Third Update'
        3  /
      
      2 rows updated.
      
      SQL> commit;
      commit
      *
      ERROR at line 1:
      ORA-00603: ORACLE server session terminated by fatal error
      With Text index present, Same error on Commit.


      Any Idea? Why Oracle behaving like this. Is there any patch needed to fix this issue? Please help.