ORA-20229: Workspace Manager Error

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1



when this error occurs


delete the GOLF_YARD_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

Even if  its not working !

Then check whether a trigger exist on GOLF_YARD table or GOLF_YARD view ie : ( when
disabling GOLF_YARD that trigger may not be dropped) .That trigger is now restricting you to version enable your table GOLF_YARD drop the trigger/triggers from GOLF_YARD

and then execute


EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

it will works sure

---------

Outer Join tends to Complex

  Left outer join retreives all rows in the table1 which is left table ;even if there is no match with table2.


 Right outer join retreives all rows in the table1 which is right table ;even if there is no match with table2.
 
Example
 
CREATE TABLE DEPT_MASTER
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);
CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

ALTER TABLE USER_DETAILS ADD ( CONSTRAINT FK_USER_DETAILS_DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT_MASTER (DEPT_ID));

Insert some data in both tables.

Old Fashion Left Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC  from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;
New  Fashioned  Left Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d left outer join dept_master dd on D.DEPT_ID=DD.DEPT_ID order by 1;

Old Fashion Right Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID   order by 1;

New  Fashioned  Right Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d right outer join dept_master dd on d.DEPT_ID=DD.DEPT_ID order by 1;

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

Here the scope of this article is not meant to describe this in detail , only to analyze in detail about the Old fashioned outer join it becomes complex and complicated , we can just have a look on it.

Left outer join can be written as below in 3 ways

a) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,left table and (+) outer notation in left to right table column

b) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,right table and (+) outer notation in left to left table column

c) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;-- left outer join (actual) ,left table and (+) outer notation in right to right table column

 Right outer join can be written as below in 3 ways
 

a) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID order by 1;-- right outer join (actual) right table and (+) outer notation in left to left table column

b) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

c) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

Normal Join


1) select d.user_id,DD.DEPT_DESC from user_details d  join dept_master dd on d.dept_id=dd.dept_id;

2) select d.user_id,DD.DEPT_DESC from user_details d natural join dept_master dd ;

3) select d.user_id,DD.DEPT_DESC from user_details d join dept_master dd using(DEPT_ID) ;

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

Oracle WorkspaceManager ErrorC0des

 
exec dbms_wm.enableversioning('TABLEA');

ORA-20129: table 'TABLEA' does not exist
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version enabling  


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


exec dbms_wm.disableversioning('TABLEA');

ORA-20132: table 'TABLEA' is not version enabled
ORA-06512: at "WMSYS.LT", line 9355
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version disabling

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

 exec dbms_wm.enableversioning('USER_RECORDSS_ON_01_SEP_2011')

ORA-20136: table names are limited to 25 characters
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

In an Oracle Workspace Manager only 25 characters named tables are allowed to version enbale


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

ORA-20061 ,ORA-00604 and ORA-2017

ORA-00604: error occurred at recursive SQL level 1
ORA-20061: versioned objects have to be version disabled before being dropped
ORA-06512: at "WMSYS.WM_ERROR", line 338
ORA-06512: at "WMSYS.WM_ERROR", line 346
ORA-06512: at "WMSYS.NO_VM_DROP_PROC", line 42

One scenario where  this error is , when you try to drop TESTVERSION_LT  table

If this error is occuring and not able to drop the 'TESTVERSION_LT' table and also not able to version disable the 'TESTVERSION'

FIRST take the backup of TESTVERSION table as
create table TESTVERSION_back as select * from TESTVERSION 
 and create all the indexes on that TESTVERSION_back table
.SECOND try to delete the entry of that table   from WM$VERSIONED_TABLES table and the try to drop VIEW  TESTVERSION AND TESTVERSION_LT  TABLE


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

ORA-00604: error occurred at recursive SQL level 1
ORA-20171: WM error: Versioned objects cannot be altered.


alter table TESTVERSION_lt add VERSION INTEGER


FIRST take the backup of TESTVERSION table as create table TESTVERSION_back as select * from TESTVERSION and create all the indexes on that TESTVERSION_back table

SECOND try to delete the entry of that table for example from WM$VERSIONED_TABLES table and the try to drop VIEW TESTVERSION AND TESTVERSION_LT TABLE

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

Compressed Tables Cannot Versiondisable

create table testversion ( pkl number primary key,s_var varchar2(10));

ALTER TABLE TESTVERSION CACHE COMPRESS  PARALLEL ( DEGREE Default INSTANCES Default );

exec dbms_wm.enableversioning('TESTVERSION');

exec dbms_wm.disableversioning('TESTVERSION');

ORA-20231: 'TESTVERSION'  failed during DisableVersioning. Error:
ORA-39726: unsupported add/drop column operation on compressed tables .

  This error occurs when we try to version disable a  compressed table ;its  because during version enabling a compressed table   four columns  VERSION, NEXTVER, DELSTATUS, LTLOCK are not created

SO THERE IS NO USE TO VERSION ENABLE A COMPRESSED TABLE BECAUSE FOLLOWING 
VERSION, NEXTVER, DELSTATUS, LTLOCK COLUMNS ARE NOT CREATED WHICH ARE THE NUCLUES OF THE VERSIONING 


WHEN A COMPRESSED TABLE IS VERSION ENABLED ONLY  A VIEW NAMED 'TESTVERSION' IS CREATED .NO TRIGGERS AND OTHER VIEWS ARE CREATED


WHEN A COMPRESSED TABLE IS TRIED TO VERSION DISABLED ITS STATE IS CHANGED TO 'DV' .EVEN THOUGHT IT THROWS ERROR

CHECK THE BELOW QUERY FOR VERSIONED STATUS OF A TABLE

SELECT DISABLING_VER,UNDO_CODE FROM WMSYS.WM$VERSIONED_TABLES WHERE OWNER='SCHEMAMERGE' AND TABLE_NAME='TESTVERSION' ;

AND IF YOU CHECK IN UNDO_CODE COLUMN OF THIS WMSYS.WM$VERSIONED_TABLES TABLE YOU CAN GET WHY VERSION DISABLE OF THE COMPRESSED TABLE DEOSNT WORK

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