5 Replies Latest reply: Jul 24, 2009 2:30 PM by Ben Speckhard-Oracle RSS

    Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8

    485046
      Hello,

      We running Oracle 9i with OWM 9.2.0.8

      We are trying to run BeginDDL on a version enabled table with approx 2.8 million records
      in it. And when we try running the statment:

      BEGIN
      DBMS_WM.BeginDDL('PFS_SPOT_SHOTS');
      END;

      We get the following error:

      ORA-20203: enable/disable versioning or begin/commitDDL is being executed on PFSDB.PFS_SPOT_SHOTS
      ORA-06512: at "WMSYS.OWM_DDL_PKG" line 3378
      ORA-06512: at "WMSYS.LT", line 11827
      ORA-06512: at line 2

      We also try running BeginDDL on another version enabled table with no data in it

      BEGIN
      DBMS_WM.BeginDDL('PFS_DUMMY_POINT');
      END;

      then we are getting the following error:

      ORA-25150: ALTERING of extent parameters not permitted
      ORA-06512: at "WMSYS.OWM_DDL_PKG", line 3378
      ORA-06512: at "WMSYS.LT", line 11827
      ORA-06512: at line 2


      Any suggestions in regards to this problem?

      Thanks
      Gary
        • 1. Re: Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8
          Ben Speckhard-Oracle
          Hi Gary,

          For the first error, check the state of the table by using the all_wm_versioned_tables view. dbms_wm.beginDDL can only be called on a table that is in the 'VERSIONED' state. If you let me know specifically which state the table is in, I can suggest how to get the table back into the VERSIONED state.

          The second error I have seen before on other versions of the database, but cannot say without additional information whether it is the same issue. If you file a SR for this, we would be able to confirm that and provide a fix for 9.2 if necessary.

          Regards,
          Ben
          • 2. Re: Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8
            485046
            1) For the first table PFS_SPOT_SHOTS that we are having trouble with, the query return the following:

            select * from wmsys.all_wm_versioned_tables t
            where table_name = 'PFS_SPOT_SHOTS';

                 TABLE_NAME     OWNER     STATE     HISTORY     NOTIFICATION     NOTIFYWORKSPACES     CONFLICT     DIFF     VALIDTIME
                 PFS_SPOT_SHOTS     PFSDB     RB_IND     VIEW_WO_OVERWRITE     NO          NO     NO     YES

            2) For the second table PFS_DUMMY_POINT table, the query return the following:

            select * from wmsys.all_wm_versioned_tables t
            where table_name = 'PFS_DUMMY_POINT';

                 TABLE_NAME     OWNER     STATE     HISTORY     NOTIFICATION     NOTIFYWORKSPACES     CONFLICT     DIFF     VALIDTIME
                 PFS_DUMMY_POINT     PFSDB     VERSIONED VIEW_WO_OVERWRITE     NO          NO     NO     YES

            Other than the PFS_DUMMY_POINT table, the query for other version enabled table also return STATE:VERSIONED except the PFS_SPOT_SHOTS table


            We have filed a SR, and the number is : 7599412994

            Thank you for your response.
            • 3. Re: Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8
              Ben Speckhard-Oracle
              Hi,

              The RB_IND state indicates that there was an error while executing dbms_wm.AlterVersionedTable with the REBUILD_INDEX option. Try to execute this procedure again. If it continues to fail, you can query the all_wm_vt_errors view to determine which statement the procedure is failing on.

              For the 2nd issue, continue with the SR.

              Regards,
              Ben
              • 4. Re: Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8
                485046
                Hi,

                Yes, we ran the dbms_wm.AlterVesionedTable with the REBUILD_INDEX option on an index that was giving us problem before, but we managed to
                get rid of that problematic index(so that index no longer exists), but the RB_IND state remains on the table, so now I am wondering how we could
                bring this table back to the VERSIONED state?

                Would running something like:

                begin
                dbms_wm.alterversionedtable('PFSDB.PFS_SPOT_SHOTS', 'RENAME_INDEX', 'index_owner=PFSDB,
                index_name=PFS_SPOT_SHOTS_F1, new_index_name=PFS_SPOT_SHOTS_F1',
                true);
                end;

                PFS_SPOT_SHOTS_F1 is already an index on the table, so if I run the above to rename(without really renaming it) and set the ignore_last_error to TRUE, would that
                help in bringing the table back into the VERSIONED state?

                Thanks for your response again
                • 5. Re: Unable to run BeginDDL on a version enabled table on OWM 9.2.0.8
                  Ben Speckhard-Oracle
                  Hi,

                  Yes, using ignore_last_error=>true should return the table to the VERSIONED state.

                  Regards,
                  Ben