2 Replies Latest reply: Sep 9, 2011 11:31 AM by Pedro Lourenço RSS

    AVTDLL state in versioned table

    Pedro Lourenço
      Hi,

      By mistake, we've created an index directly over the LT table.
      Trying to remove that index, we used dbms_wm.alterVersionedTable with DDL option (as SYSDBA).
      After running the instruction, we obtained an error (that is visible on all_wm_vt_errors), and the state of the table turned to AVTDDL.
      Now, the index still exists and we still can't execute beginddl in the refered table.

      The instruction:

      begin
      DBMS_WM.AlterVersionedTable('CADREDE.NS_RES_INS_TP_TP’, 'DDL', 'ddl=drop index NS_RES_INS_TP_TP_LT_DS_IDX;');
      end;
      /

      The error in all_wm_vt_errors:

      OWNER     CADREDE
      TABLE_NAME NS_RES_INS_TP_TP
      STATE     AVTDDL
      SQL_STR     "Begin execute immediate 'DROP INDEX NS_RES_INS_TP_TP_LT_DS_IDX' ; End;"
      STATUS     ALTERVERSIONEDTABLE DDL STEP EXECUTED WITH ERRORS
      ERROR_MSG ORA-01418: specified index does not exist

      Just to confirm, the index exists:

      select a.index_name, a.table_name
      from user_indexes a
      where a.index_name = 'NS_RES_INS_TP_TP_LT_DS_IDX'

      INDEX_NAME NS_RES_INS_TP_TP_LT_DS_IDX
      TABLE_NAME NS_RES_INS_TP_TP_LT

      What am i doing wrong or what can i do to solve this problem?
      Thanks in advance.

      Regards,

      Pedro Lourenço