2 Replies Latest reply on Aug 12, 2014 7:33 PM by stuartu

    ORA-20203 on dbms_wm.beginddl - status=DV




      One of our developers wants changes to a version enabled table, which hasn't been structurally changed for some months.


      Unfortunately, I get an error when setting the table into maintenance mode, nor is the table in maintenance mode already.


      NUTRIWEB: MPI > exec dbms_wm.beginddl('product');

      BEGIN dbms_wm.beginddl('product'); END;



      ERROR at line 1:

      ORA-20203: enable/disable versioning or begin/commitDDL is being executed on MPI.PRODUCT

      ORA-06512: at "WMSYS.LT", line 12166

      ORA-06512: at line 1


      NUTRIWEB: MPI > exec dbms_wm.commitddl('product');

      BEGIN dbms_wm.commitddl('product'); END;



      ERROR at line 1:

      ORA-20204: beginDDL not called on MPI.PRODUCT

      ORA-06512: at "WMSYS.LT", line 12253

      ORA-06512: at line 1


      Looking at the ALL_WM_VT_ERRORS, I can see the state of the table = DV, and from the documentation indicates 'the table is being version disabled'.

      As this table should NOT be version disabled, I'm presuming someone mistakenly ran the wrong dbms_wm command over it then did a Control-C to abort.


      NUTRIWEB: MPI > select owner, table_name, state, status, error_msg, sql_str from all_wm_vt_errors;


      OWNER                   TABLE_NAME      STATE  STATUS                 ERROR_MSG

      ------------------------------ --------------- ------ ------------------------------ -------------------------------------------------------



      MPI                   PRODUCT           DV     DV STEP EXECUTED WITH ERRORS   ORA-01013: user requested cancel of current operation

      delete from MPI.PRODUCT_LT

           where nextver = ',0,' or

                 nextver in

              (select next_vers

               from wmsys.wm$nextver_table

               where version in (select version from wmsys.wm$version_hierarchy_table where workspace='LIVE')) or

                 delstatus < 0



      1 row selected.


      Ideas on how to get this back into a version-enabled state?     I see 389k records in the PRODUCT_HIST table, so we still have history, but as to the state of the rest of the related WM objects...


      Thanks in advance



        • 1. Re: ORA-20203 on dbms_wm.beginddl - status=DV
          Ben Speckhard-Oracle

          Hi Stuart,


          The 'DV' state means that dbms_wm.DisableVerisoning was executed on the table.  It looks like whoever executed the procedure, tried to stop it.  However, once this procedure starts, typically there is no way to revert it back as the majority of the operations performed are destructive to the table.  The only way to get the table to be versioned again is to finish the DisableVersioning procedure by executing it again, and then executing EnableVersioning.  However, by doing this all of the remaining history would be lost.  If you don't necessarily need that history, you can perform those operations and skip the remaining part of this post.


          If you do need the history, I would recommend filing a SR.  Based on the delete statement listed in the all_wm_vt_errors view, the only statement that has already been run is the delete from the base table of any row that is not in the LIVE workspace.  You can determine if there had been rows in any such workspace by using the all_wm_modified_tables view.  The metadata for this view is likely to still exist at this point.  If this lists a non-LIVE workspace for the table, that does mean that the metadata of the remaining rows would be invalid.  In either case, it should be possible to maintain the remaining history, if needed.




          • 2. Re: ORA-20203 on dbms_wm.beginddl - status=DV

            Thanks.  We're considering our options, and will possibly end up going down the SR route.