This discussion is archived
2 Replies Latest reply: Dec 23, 2010 7:56 AM by Ben Speckhard RSS

Version Enabling / Disabling Errors please help

MRoche Newbie
Currently Being Moderated
Hi all

I am having a nightmare of a problem with workspace manager, I am getting two sets of errors.

When trying to enable tables I get the following error

ORA-20230: table 'NSD.NSD_FIELD_TYPE' failed during UndoEnableVersioning/DisableVersioning. Error:
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
ORA-06512: at "WMSYS.LT", line 9150
ORA-06512: at line 2

-- and --

ORA-20229: statement 'DECLARE
tab_owner           varchar2(30) := 'NSD' ;
tab_name            varchar2(30)' failed during EnableVersioning. Error:
ORA-24344: success with compilation error
ORA-06512: at "WMSYS.LT", line 9150
ORA-06512: at line 2

-- and --

ORA-20228: this operation is not allowed for table 'NSD.NSD_DB_VERSION' with version state 'DV'
ORA-06512: at "WMSYS.LT", line 9150
ORA-06512: at line 2


and when trying to disable tables I get the following errors

ORA-20231: table 'NSD.NSD_REF_CONSTRAINT' failed during DisableVersioning. Error:
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
ORA-06512: at "WMSYS.LT", line 9337
ORA-06512: at line 2

-- and --

ORA-20104: cannot version disable this table
ORA-06512: at "WMSYS.LT", line 9337
ORA-06512: at line 2

These issues occur on a selection of tables, but some tables however do enable and disable successfully.

Also it appears that the OVM_UPDATE and OVM_INSERT Triggers have become corrupt saying that ORA-00942: Table or View does not exist.

I have been able to create workspace quite successfully in the past on the same database and schema and i am at a loss at what could have caused these issues.

Do any of you have any suggestion that will resolve theses errors, I am using Oracle 11g Enterprise Edition Release 11.1.0.7.0

Many Thanks

Edited by: Roché on Dec 17, 2010 11:57 AM
  • 1. Re: Version Enabling / Disabling Errors please help
    NoelKhan Journeyer
    Currently Being Moderated
    Roché,

    . . . .*[1]* For the DisableVersioning errors, try bulldozing. Call DisableVersioning( <TABLE_NAME>, force=>true, ignore_last_error=>true) repeatedly within a PL/SQL loop. I suspect those error messages have to do with different tables, so be sure to try this on each of the problematic tables.

    . . . .*[2]* For the "missing BEFORE, AFTER..." and "DECLARE" errors, check the syntax of triggers associated to the tables that have the DisableVersioning error. Oracle Enterprise Manager can be used to identify dependancies such as triggers. It is possible to drop or replace a trigger without first DisableVersioning the table.

    Regards,
    Noel
  • 2. Re: Version Enabling / Disabling Errors please help
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    ORA-20230: This error is most likely occurring during UndoEnableVersioning. So some other error is causing EnableVerisoning to fail. This then becomes the same error as the ORA-20231 error that you listed. Would need to know the exact original trigger definition to be able to know why.

    ORA-20229: Would also need to know the trigger definitions for this one. If one of the triggers(or procedures) created by OWM are the cause, would need to see a trace of the EnableVersioning process, and the results from the all_errors view for the specific trigger.

    ORA-20228: The table is currently being DisableVersioned, and until that process completes EnableVerisoning cannot be called on the table. ignore_last_error set to true can be used to accommodate this, or fix the cause of the error so that no SQL statements get skipped.

    ORA-20104: This error will occur when you are trying to disable a child table in a foreign key relationship, without first dsableversioning the parent table. You can specify both tables in a single call to DisableVersioning, or be sure to do them in order starting with the parent tables.

    Regards,
    Ben

Legend

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