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.
DBMS_WM.AlterVersionedTable('CADREDE.NS_RES_INS_TP_TP’, 'DDL', 'ddl=drop index NS_RES_INS_TP_TP_LT_DS_IDX;');
The error in all_wm_vt_errors:
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'
What am i doing wrong or what can i do to solve this problem?
Thanks in advance.
You need to be sure that any objects specified have the appropriate schema included, otherwise you will run into this error. Since you already executed it without including it, you need to use ignore_last_error=>true, and have it successfully complete, and then run it again with the modified DDL statement.