3 Replies Latest reply: Jan 2, 2013 6:48 PM by Barbara Boehmer RSS

    Extra DR$ Tables Created

    982552
      I've had a context index in production for about 2 years now and during a recent install, support noted that some extra DR$ tables were created that had never been before. Normally we only have the I, K, N and R DR$ tables. This install yielded two extra tables in this format: DR$[index name]MI and DR$[index name]MN.

      Can anyone tell me what those tables are for and why they would get created now despite no changes to the script or Oracle version? Creation scripts are below. Thanks in advance.


      call ctx_ddl.create_preference('CTX_STORE_TBS', 'BASIC_STORAGE');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'I_TABLE_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'K_TABLE_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'R_TABLE_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'N_TABLE_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'I_INDEX_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.set_attribute('CTX_STORE_TBS', 'P_TABLE_CLAUSE', 'tablespace TEXT_INDX_TBS storage (initial 40M next 20M minextents 1 maxextents unlimited pctincrease 0)');
      call ctx_ddl.create_preference('basicLexer', 'BASIC_LEXER');
      call ctx_ddl.create_section_group('SENTGROUP','NULL_SECTION_GROUP');
      call ctx_ddl.add_special_section('SENTGROUP', 'SENTENCE');
      call CTX_DDL.SET_ATTRIBUTE ('basicLexer', 'PRINTJOINS', '~!@$%^&*()-_=+|;:,"./');
      call CTX_DDL.CREATE_STOPLIST ('basicStoplist', 'BASIC_STOPLIST');

      create index MY_INDEX on MY_TABLE(myclobcolumn) indextype is ctxsys.context parameters('section group SENTGROUP LEXER basicLexer filter ctxsys.null_filter storage CTX_STORE_TBS STOPLIST basicStoplist');
        • 1. Re: Extra DR$ Tables Created
          Barbara Boehmer
          I see this is your first post, so welcome to the Oracle forms and to the Oracle Text sub-forum.

          The document in the following link explains that those tables are created as part of the rebuild optimization process.

          http://www.oracle.com/technetwork/database/enterprise-edition/default-1590763.html#ch6_idxmaint_opt_rebuild
          • 2. Re: Extra DR$ Tables Created
            982552
            Beautiful. Thank you! **EDIT** If I may; It is apparent that the process was interrupted in some fashion and these shadow objects are still hanging around. Is there a way to get Oracle to clean these objects up without having to do it manually?

            Edited by: user10165869 on Jan 2, 2013 10:39 AM

            Edited by: user10165869 on Jan 2, 2013 10:42 AM
            • 3. Re: Extra DR$ Tables Created
              Barbara Boehmer
              You might try either dropping and recreating the index or altering and rebuilding the index. I am not aware of any other methods. You should query the ctx_index_errors view to see what went wrong with the original index creation.