4 Replies Latest reply: Nov 5, 2010 2:33 PM by NoelKhan RSS

    Creating indexes (ORA-00904: "DELSTATUS": invalid identifier)

    783963
      Hi,

      I created some indexes on version-enabled tables and had no problem but in one table.

      Im getting this error message:

      Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
      Connected as sismalha

      SQL> EXECUTE DBMS_WM.BeginDDL('T_LM_SETORES');

      begin DBMS_WM.BeginDDL('T_LM_SETORES'); end;

      ORA-00904: "DELSTATUS": invalid identifier
      ORA-06512: at "WMSYS.LT", line 12178
      ORA-06512: at line 2

      Anyone could give a hint?

      Thanks in advance,
      Kenzo.
        • 1. Re: Creating indexes (ORA-00904: "DELSTATUS": invalid identifier)
          Ben Speckhard-Oracle
          Hi,

          You will need to use the DDL option of dbms_wm.AlterVersionedTable and drop the index directly. Workspace Manager only supports adding indexes using beingDDL/commitDDL. Since the index includes the delstatus column, this indicates that the index was directly added to the _LT table, and so needs to be removed from the table prior to executing beginDDL.

          Regards,
          Ben
          • 2. Re: Creating indexes (ORA-00904: "DELSTATUS": invalid identifier)
            Pedro Lourenço
            Hello,

            I have a similar problem. We have created an index directly in the _LT table by mistake.
            Following your advice to use the command alterVersionedTabke with the option ddl, we tried:

            begin
            dbms_wm.AlterVersionedTable('<table_name>LT', 'DDL', 'ddl=drop index <indexname>;');
            end;

            It didn't work...
            Then, we tried just <table_name>, without '_LT', but continued giving an error.
            Finally, we put 'force=true' inside ddl parameter and setted ignore_last_error=true and it worked...

            begin
            dbms_wm.AlterVersionedTable(
            table_name=>'<table_name>',
            alter_option=>'DDL',
            parameter_options=>'ddl=drop index <index_name>, force=true',
            ignore_las_error=>true);
            end;

            Is this the correct way of doing the operation? There are no negative impacts? No possible problems in the data dictionary?
            Thanks in advance.
            Best regards,

            Pedro Miguel Lourenço
            • 3. Re: Creating indexes (ORA-00904: "DELSTATUS": invalid identifier)
              Ben Speckhard-Oracle
              Hi,

              Yes, that is what you want to use. The table_name parameter does not include LT, as is the case for all of the dbmswm procedures. However, the DDL statement needs to include LT since it is a DDL that will act directly on the base table.  This last part is not relevant when dropping an index however.  You also do not need to specify ignorelast_error unless you encountered an error on a previous execution of the procedure.

              Regards,
              Ben
              • 4. Re: Creating indexes (ORA-00904: "DELSTATUS": invalid identifier)
                NoelKhan
                Kenzo,

                . . . .If you are on 11g, then the solutions mentioned by Ben (and elaborated by Pedro) will work.

                . . . .If you are on 10g, then you could:

                . . . .(a) A workaround (assuming you have DBA credentials), is posted here: {message:id=4596120}

                . . . .(b) A solution is to migrate your versioned content into another table.

                . . . . . . . .(b-1) Workspace manager has it's own Import and Export tools for versioned data BUT fails to export records that end with a delete (i.e., HISTORY = Insert --> Update* --> Delete).

                . . . . . . . .(b-2) You could write your own table-level "migration" routine, which can accommodate revised records that end with a delete operation. Not that hard, really.


                Regards,
                Noel