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 22.214.171.124.0
. . . .** 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.
. . . .** 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.
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.