This discussion is archived
2 Replies Latest reply: Sep 9, 2011 9:31 AM by Pedro Lourenço RSS

AVTDLL state in versioned table

Pedro Lourenço Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points