Showing posts with label ORA-20229. Show all posts
Showing posts with label ORA-20229. Show all posts

May 10, 2012

Workspace Error : ORA-20229

EXEC DBMS_WM.ENABLEVERSIONING('FACEBOOKBOUNDARY');

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 FACEBOOKBOUNDARY_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('FACEBOOKBOUNDARY');

it will works

:)
---------

Mar 25, 2009

Oracle Workspace 1

Workspace Manager PL/SQL APIs

The PL/SQL APIs in the DBMS_WM package can be executed

* Workspace operations: create,refresh,merge,rollback remove,goto,compress,alter
* Savepoints: create, alter, goto
* History: goto date
* Privileges: access, create, delete, rollback and merge workspace
* Access Modes: read, write, management or no access to workspaces
* Locks: exclusive and shared workspace locks to prevent data update conflicts
* Find Differences: compare savepoints and workspaces
* Detect / Resolve Conflicts: Automatically detect and resolve conflicts

When a particular table is versioned primary key index is rebuild version field is added with the current primary key fields inorder to uniquely identify each row version .Each table that is versioned with the workspace manager must have a primary key.No issue if there is a Foreign Key

To get Lock Mode

SELECT dbms_wm.GetLockMode FROM dual;


Some Errors
-----------

(I)
ORA-20101: child table must be version enabled (This error occur if TEST table is referenced by another table TESTCHILD ;if TESTCHILD is version enabled then only TEST table can be versioned )


(II)ORA-20100: 'USER.TEST' is both parent and child tables of referential integrity constraints

If a field of TEST table is referenced by another field of TEST table itself such tables cannot be version-
enabled ie:( In Scott.EMP table MGR field is referenced by EMPNO field )

ALTER TABLE EMP ADD CONSTRAINT FK_EMP
FOREIGN KEY (MGR) REFERENCES EMP (EMPNO) )


(III) Temporary Table cannot be version enabled

ORA-20229:statement 'CREATE INDEX TMP_TEST_PKI$ on TMP_TEST_LT(A)LOGGING PCTFREE 10 INITRANS 2 M' failed during EnableVersioning.Error:
ORA-14451: unsupported feature with temporary table


Referential integrity constraints cannot be added after versioning is enabled. They must be present before version-enabling
------------------*------------------*---------------------*----------------

EXEC DBMS_WM.FINDRICSET('B_focus_1', 'RICTEST') ; --CREATE A TABLE WITH FIELD TABLE_OWNER & TABLE_NAME WITH DATA

SELECT dbms_wm.GetPrivs('LIVE') FROM dual ; //GET Privileges of particular workspace

To Get Lock Mode
SELECT dbms_wm.GetLockMode FROM dual ;


DECLARE
lockmode varchar2(1);
begin
lockMode := sys.lt_ctx_pkg.lock_Mode;
dbms_output.put_line(lockMode);
end;



The min & max time that Oracle supports

SELECT DBMS_WM.max_time,DBMS_WM.min_time FROM DUAL; (from 10g onwards)