Interanl execution during Disable versioning a table

Step 1:
SELECT 1  FROM DUAL WHERE EXISTS (SELECT 1 FROM  scott.experiment_lt   WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table 
WHERE workspace != 'LIVE'))
                       
Step 2:       
DELETE FROM experiment_lt  WHERE nextver = ',0,' OR nextver IN ( SELECT next_vers FROM wmsys.wm$nextver_table
WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table  WHERE workspace = 'LIVE')) OR delstatus < 0
           
Step 3:
ALTER TABLE  scott.experiment_lt   ADD CONSTRAINT sys_c0036728 PRIMARY KEY (experiment_id) USING INDEX scott.sys_c0036728

Step 4:
SELECT dtc.column_name FROM dba_constraints dc,dba_cons_columns dcc, dba_tab_columns dtc WHERE dc.owner ='SCOTT' AND dc.table_name ='ADDRESS' || '_LT'
AND dc.constraint_type = 'P' AND dcc.owner = 'SCOTT'  AND dcc.table_name = 'EXPERIMENT'|| '_LT' AND dc.constraint_name = dcc.constraint_name AND
dcc.column_name NOT IN ('VERSION','LTLOCK','DELSTATUS','NEXTVER') AND dcc.column_name NOT LIKE 'WM$%' AND dcc.column_name NOT LIKE 'WM@_%' ESCAPE '@'
AND dtc.owner = 'SCOTT' AND dtc.table_name = 'EXPERIMENT'||'_LT' AND dcc.column_name = dtc.column_name  ORDER BY dtc.column_id

Step 5:
INSERT INTO wmsys.wm$vt_errors_table
     VALUES (:b4, :b3, :b2, :b1, 'DV STEP BEING EXECUTED', NULL)

Step 6:
ALTER TABLE EXPERIMENT_lt DROP COLUMN ltlock;
ALTER TABLE EXPERIMENT_lt DROP COLUMN delstatus;
ALTER TABLE EXPERIMENT_lt DROP COLUMN VERSION;
ALTER TABLE EXPERIMENT_lt DROP COLUMN NEXTVER;

Step 7:

DELETE FROM TABLE (SELECT undo_code FROM wmsys.wm$versioned_tables WHERE owner ='SCOTT' AND table_name ='EXPERIMENT')
WHERE index_type =:b4 AND index_field =:b3

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

:)
---------

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

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

ORA-20171: Workspace Error during BEGIN DDL

SQL > exec dbms_wm.beginddl('TABLE_NAME');

ORA-20171: WM error: 'CREATE TABLE' and 'CREATE SEQUENCE' privileges needed.


This error can be avoided by giving these below  grants to the particular user.

SQL > grant create TABLE  to username ;

SQL >  grant create SEQUENCE  to username;

Then execute the script ;it will be successful

SQL > exec dbms_wm.beginddl('TABLE_NAME');


--------

ORA-20104: cannot version disable this table

ORA-20104: cannot version disable this table
ORA-06512: at "SYS.LT", line 9152
ORA-06512: at line 3

the following error will occur if the TESTP table is a parent table and all the child table that refers TESTP is not versiondisabled .  

 An Example

create table TEST (ee number(10),fg varchar2(100)) ;

ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (EE);

create table TESTP (ff number(10),ee number(10) ,constraint fk_fg foreign key(ee) references TEST(ee) );

ALTER TABLE TESTP ADD CONSTRAINT TESTp_PK PRIMARY KEY (ff);

execute dbms_wm.enableversioning('TEST');

execute dbms_wm.enableversioning('TESTP');

execute dbms_wm.disableversioning('TESTP');  This will result an error ( ORA-20104: cannot version disable this table )

to avoid this first you should disableversion child table  TEST and then parent table TESTP

execute dbms_wm.disableversioning('TEST');

execute dbms_wm.disableversioning('TESTP');

Drop foreign key from a Versioned Table

To drop a foreign key constraint from a version enable table .

EXECUTE DBMS_WM.BeginDDL('PARENT_TABLE');
EXECUTE DBMS_WM.BeginDDL('CHILD_TABLE');
ALTER TABLE CHILD_TABLE_LTS DROP CONSTRAINT FOREIGN_KEY_NAME
EXECUTE DBMS_WM.CommitDDL('CHILD_TABLE');
EXECUTE DBMS_WM.CommitDDL('PARENT_TABLE');


The foreign key constraints and its details of version enabled tables can be obtained from the view USER_WM_RIC_INFO or from
table wmsys.wm$ric_table rt

--------------------
Eg :
EXEC DBMS_WM.BEGINDDL('BILL');
EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
ALTER TABLE BILL_DETAILS_LTS DROP CONSTRAINT FK_BILL_DETAILS_CTS ;
EXEC DBMS_WM.COMMITDDL('BILL');
EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

DBMS_WM.CompressWorkspace

DBMS_WM.Compress Workspace

 If   you version enabled the tables with  DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')   API  THEN your  Oracle Workspace  database WILL  GET SLOW  WHEN SO MANY DATA COMES ; use DBMS_WM.CompressWorkspace API  TO MAKE IT FASTER; There is a scenario to check  whether your Workspace  is getting slow or Workspace database is to be Compressed or not ,execute this  workspace API
DBMS_WM.SetDiffVersions('LIVE',wksp2) . and  then  pick a version enabled table having highest record count   , issue command select count(*) from tablename_diff ; if it takes more than a minute to GET RESULT THEN YOU CAN MAKE SURE THAT YOU NEED TO COMPRESS YOUR WORKSPACE ;here u want to use the   statement  (1) , using statement (2) will not give better result because your table is version enabled  with DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')  .

(1) exec DBMS_WM.CompressWorkspace('LIVE',compress_view_wo_overwrite => true);

(2) exec DBMS_WM.CompressWorkspace('LIVE')



VIEW_WO_OVERWRITE: The without overwrite (WO_OVERWRITE) option. A view named _HIST  is created to contain history information, and it will show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

compress_view_wo_overwrite        A Boolean value (TRUE or FALSE).

TRUE causes history information between the affected savepoints to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled.

FALSE causes history information (between the affected savepoints) for a table not to be deleted if VIEW_WO_OVERWRITE was specified when versioning was enabled. (If VIEW_WO_OVERWRITE was not specified for a table, history information for the table is deleted regardless of the parameter value.) FALSE is assumed if the procedure format without this parameter is used.


Advantage
------------
1  Run-time performance for Workspace Manager operations is improved.
2  Less disk storage is used for Workspace Manager structures

ADD FOREIGN KEY TO VERSIONED TABLE

   ORACLE WORKSPACE MANAGER  TOPIC

    If there is  a version enabled table 'BILL' and
  
 /*  CREATE TABLE BILL (N_BILLID NUMBER(10), CONSTRAINT PK_BILL PRIMARY KEY (N_BILLID));
   EXEC DBMS_WM.ENABLEVERSIONING('BILL') ; */
   
 now i created a new table BILL_DETAILS which must be a child table of BILL
 
 /* CREATE TABLE BILL_DETAILS ( N_BILLDTID NUMBER(10),CONSTRAINT PK_BILLDETAILS PRIMARY KEY (N_BILLDTID) ,   N_BILLID NUMBER(10) , S_DESC VARCHAR2 (100), D_SYSDATE  DATE   DEFAULT SYSDATE ) ; */
  
 here you are not able to add a foreign key between one versioned and one non-versioned table.if it is done  oracle error will be the result


 ORA-20200: UNSUPPORTED CONSTRAINT
   
for that you would need to execute dbms_wm.enableversioning on the non-versioned table (you can add a foreign key between 2 version enabled tables) if you don't  want the detail to be kept as version enabled table then you can disable versioning of the detail table.

    EXEC DBMS_WM.ENABLEVERSIONING('BILL_DETAILS') 
  
    EXEC DBMS_WM.BEGINDDL('BILL');
  
    EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
  
   ALTER TABLE    BILL_DETAILS_LTS ADD CONSTRAINT FK_BILLDET_BILL FOREIGN KEY (N_BILLID)  REFERENCES BILL_LTS(N_BILLID);
  
   EXEC DBMS_WM.COMMITDDL('BILL');
  
   EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

   EXEC DBMS_WM.DISABLEVERSIONING('BILL_DETAILS')   ;

Oracle Workspace Manager

Difference between Long Transaction (Oracle WorkSpace) and Short Transaction (Normal )

Long Transactions
-----------------------

Complete over days or weeks
Likelihood of conflict is low - Optimistic concurrency permits conflicts

Selective versioning tables
Update creates a new row version
Each update is part of a short transaction

Collections of updates isolated in workspaces until merged into production

Multi user update

Short Transactions
----------------------
Complete in seconds or less
Likelihood of conflict is high - Pesimistic concurrency permits conflicts
Data is in a single state
Updates accessible upon commit
Single user updates

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

WorkSpace Manager Operations
----------------------------------------

Workspace : Create , Refresh,merge,rollback,remove,goto,compress,alter
Savepoints : Create , Alter,Goto,Compare,Rollback and Delete
History : Gotodate
Privileges : Access,Create,Delete,Rollback,Merge
Locks : Exclusive and Shared (Exclusive lock prevents changes by any other user Shared locks allow other users in the workspace to change row )
Differences : Compares savepoints and workspaces
Detect /Resolve Conflicts : choose version to merge.

Since the versioning process removes the physical unique index from the base table, multiple session would be allowed to enter the same value into a column that has a unique constraint defined on it. Since the changes by the other session might be part of an uncommitted transaction, the instead of triggers that are defined on the view are unable to enforce the constraint in this particular case. As a result,OWM use the _LCK VIEW to prevent this scenario by maintaining a unique constraint on the underlying table which the dmls are applied to.


Disabling Versioning for a table
------------------------------------

Disable versioning when changes to the version-enabled table are completed
Improves performance
Workspace hierarchy and savepoints remain
The latest version of each row in LIVE workspace remains.

Freeze WorkSpace
---------------------
Freezing a workspace specifies the kind of user access allowed to the workspace .

NO_ACCESS is default
READ_ONLY allows all workspace users to read.
1WRITER: Sessions are allowed in the workspace, but only one user
1WRITER_SESSION: Sessions are allowed in the workspace, but only the database session (as opposed to the database user) that called the FreezeWorkspace procedure is allowed to perform write operations
WM_ONLY: Only Workspace Manager operations are permitted.

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

Oracle Wokspace 4

dbms_wm.gotodate

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);

select * from test order by one ;

begin
dbms_wm.gotodate( (sysdate-5)) ;
end;

select * from test order by one ;

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

select * from test order by one ;

Freezeworkspace

begin
dbms_wm.freezeworkspace('B_focus_1', 'READ_ONLY');
end;

insert into test values (6,'Wayanad','YS','n',null,sysdate);


This Error will be throwed when we try any DML operations (here to insert data) into a readonly workspace.

ORA-20123: workspace 'B_focus_1' is currently frozen in READ_ONLY mode


Export And Import Implications
The following implications are a result of version-enabling tables:

* Imports of version-enabled tables can only be performed if the target database has Workspace Manager installed and no workspaces defined other than LIVE.
* Only full database exports are supported with version-enabled databases.
* The IGNORE=Y parameter must be set for imports of version enabled databases.
* Imports of version-enabled databases cannot use the FROMUSER and TOUSER functioanlity.

For More about workspaces Refer  here

Oracle Wokspace 3

To Refresh workspace

begin
dbms_wm.gotoworkspace('B_focus_9');
dbms_wm.refreshworkspace('B_focus_9');
end;

But if there are conflicts refreshworkspace will result an error

ORA-20056: conflicts detected for workspace: 'B_focus_9' in table: 'USER.TEST'

LIVE WORKSPACE CANNOT BE REFRESHED

The two rows that changed in both the LIVE and the 'B_focus_9' workspace create a conflict. These conflicts can be seen from the view Test_conf. But first go to the 'B_focus_9' workspace. The conflicts are not visible in this view while being in the LIVE workspace.

--------------------------
To Resolve this conflicts

SELECT * FROM test_conf ORDER BY wm_workspace;

begin
dbms_wm.gotoworkspace('B_focus_9');
dbms_wm.beginresolve('B_focus_9');
dbms_wm.resolveconflicts('B_focus_9','TEST',null/*where clause*/,'PARENT');
commit;
end;

begin
dbms_wm.commitresolve('B_focus_9');
end;

SELECT * FROM test_conf ORDER BY wm_workspace;

After resolving this conflict the workspace can be refreshed

begin
dbms_wm.refreshworkspace('B_focus_9');
end;


Merge Workspace

begin
dbms_wm.mergeworkspace('B_focus_9');
end;


When B_focus_9 workspace is merged into the LIVE workspace. A merge is the opposite of a refresh : it updates the parent workspace (LIVE) with the changes made to the merged workspace (B_focus_9). Where as refresh operation updates the refreshing workspace.
---------------------------------

After MergeWorkspace See Difference

begin
dbms_wm.gotoworkspace('B_focus_1');
end;

select * from test order by one




begin
dbms_wm.gotoworkspace('B_focus_9');
end;

select * from test order by one




begin
dbms_wm.gotoworkspace('LIVE');
end;

select * from test order by one



Here parent workspace (LIVE) was updated by merged workspace (B_focus_9) .

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

Oracle Workspace 2

The merge operation does not currently work with versioned tables. The optimizer translates the merge into
insert/update statements on the underlying _LT table

To have several versions of data

create table TEST (
one number primary key, -- Without primary key: ORA-20133:
two varchar2(15), three char(2), four clob,
five blob, six date );

insert into test values (1,'Cochin','NO','Kochi is a vibrant city situated on the south-west coast of the Indian peninsula in the breathtakingly scenic and prosperous state of Kerala, hailed as Gods Own Country',null,sysdate);

insert into test values (2,'Kerala','YS','n',null,sysdate);
insert into test values (3,'TVM','NO','n',null,sysdate);
insert into test values (4,'Calicut','NO','n',null,sysdate);
insert into test values (5,'Kannur','YS','n',null,sysdate);

commit;

begin
dbms_wm.enableversioning ('test');
end;

begin
dbms_wm.createworkspace ('B_focus_1');
dbms_wm.createworkspace ('B_focus_9' );
end;

begin
dbms_wm.gotoworkspace('B_focus_1');
dbms_wm.createworkspace ('test' );
end;

select workspace, parent_workspace from user_workspaces;

select * from test order by one;

update test set two='Kasargode',three='NO',four='Top end of Kerala',Six=sysdate where one=2;

commit;



begin
dbms_wm.gotoworkspace('LIVE');
end;

select * from test order by one



begin
dbms_wm.gotoworkspace('B_focus_9');
end;

select * from test order by one

update test set two='Thrissur',three='NO',four='Middle of Kerala',Six=sysdate where one=2;
commit;

select * from test order by one



begin
dbms_wm.gotoworkspace('test');
end;

select * from test order by one

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)

Oracle Workspace

With Oracle's Workspace Manager it's possible to have several versions of data. That is, data can be changed, thus making a new version, without affecting application data.For this a new workspace is to be created and the table is to be versioned

Workspace allows multiple transactions to exist within one table in a schema. This allows several departments or functional areas to work against a single schema without interfering with data from other groups. Changes to version-enabled tables are captured as new rows within the workspace. These changes are invisible to other workspaces until they are merged into a parent workspace.

The functionality (Packages, Procedures, Functions) used for the Workspace Manager are found in the wmsys schema.

In a workspace hierarchy consisting of Live->PreProduction->Development workspaces, the Development workspace can see all row changes made in the PreProduction workspace, along with all committed data from non-version-enabled tables belonging to the Live workspace. In addition it can see data from version-enabled tables in Live as they were when the PreProduction workspace was created. Once a workspace is refreshed, all changes can be cascaded down the hierarchy.


Workspace Manager makes only a copy of row it is changed ,which reduce hardware , software and time needed to manage multiple version of data in different schemas .A workspace is a virtual environment not physical storage.The default workspace is called LIVE.

Main concepts used for workspace are Instead of Triggers and Context .


For More On WorkSpace

To get Version

SELECT dbms_wm.getversion FROM dual;


To create Workspace

begin
dbms_wm.createworkspace('B_focus_1');
end;

select workspace, parent_workspace from user_workspaces;

To move to workspace

begin
dbms_wm.gotoworkspace('B_focus_1');
end;



To enable versoning for a table


begin
dbms_wm.enableversioning (table_name,hist);
end;

1)The length of a table name must not exceed 25 characters. The name is not case sensitive

2) Hist

NONE: No modifications to the table are tracked. (This is the default.)

VIEW_W_OVERWRITE: The with overwrite (W_OVERWRITE) option: A view named _HIST is created to contain history information, but it will show only the most recent modifications to the same version of the table. A history of modifications to the version is not maintained; that is, subsequent changes to a row in the same version overwrite earlier changes. (The CREATETIME column of the _HIST view contains only the time of the most recent update.)

VIEW_WO_OVERWRITE: The without overwrite (WO_OVERWRITE) option: A view named _HIST is created to contain history information, and it will show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

If the table is version-enabled with the VIEW_WO_OVERWRITE hist option specified, this option can later be disabled and re-enabled by calling the SetWoOverwriteOFF Procedure and SetWoOverwriteON Procedure.


begin
DBMS_WM.SetWoOverwriteOFF();
end;

This procedure enables the VIEW_WO_OVERWRITE history option that had been disabled by the SetWoOverwriteOFF Procedure.

begin
DBMS_WM.SetWoOverwriteON();
end;
---------------------------------------
a)Only the owner of a table can enable versioning on the table.

b)Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.

c)Tables owned by SYS cannot be version-enabled.

d)DDL operations are not allowed on version-enabled tables.

e)Index-organized tables cannot be version-enabled.

f)Object tables cannot be version-enabled.

g)A table with one or more columns of LONG data type cannot be version-enabled.
------------------------------------------------------------

Example

begin
dbms_wm.enableversioning ('TEST');
end;

This will rename the TEST table to TEST_LT(LT stands for Long Transaction ie:Completes over days or week ) and create a view called TEST (which contains original data for more detail refer script of view) addition to this 9 other views will be created.The view uses instead-of triggers to perform all operations against the version enabled table. This hides a lot of the versioning mechanism from the users.
The TEST_LT table has the following additional columns:

VERSION NOT NULL NUMBER(38)
NEXTVER VARCHAR2(500)
DELSTATUS NUMBER(38)
LTLOCK VARCHAR2(100)

Values for following tables when table is version enabled (0,-1,10,!O!)


Test_MV (Materialized view) View it contains data ;which the field is not affected ;with two extra fields WM_MODIFIEDBY and WM_OPTYPE

Test_base view contains the field of original table in addition RID,version,nextver,delstatus,ltlock

Test_BPKC contains fields Rowids of child,parent and base ,childstate ,parentstate ,DS and VER of child,parent & base (ie : 12 fields) if we are in LIVE workspace there will not be an data in it because it is in parent state if it is in any of child workspace there will be data

Test_PKC contains all the fields of Text_BPKC excluding Firstchildver

TEST_HIST This view is created only if ; during table versioning (ie: enableversioning) if we supply hist parameter if hist parameter is NONE then this view will not be there to track history details .

IF active workspace is LIVE then TEST,TEST_BASE,TEST_MV view only have records

Test_conf (Conflict) view contains all fields in Test addition to those wm_workspace,wm_delted


To disable versoning

begin
dbms_wm.disableversioning ('TEST');
end;

To compress WorkSpace

begin
dbms_wm.compressworkspace('LIVE');
end;

To view curent workspace

SELECT DBMS_WM.getworkspace FROM DUAL;



SELECT DBMS_WM.isworkspaceoccupied('B_focus_1') FROM DUAL;

To view versionenabled tables

select * from wmsys.wm$table_parvers_view
SELECT * FROM user_wm_versioned_tables;




To  get current version number 

select * from wmsys.WM$CURRENT_VER_VIEW;


To get current and next version 


select * from wmsys.WM$CURRENT_NEXTVERS_VIEW;

To  get current hierarchy of  workspace

select *  from wmsys.WM$CURRENT_HIERARCHY_VIEW;
select *  from wmsys.WM$CONF1_HIERARCHY_VIEW; 

To get parent of a workspace

You should be in a workspace other than LIVE then only we get data from this view ; because for LIVE workspace is parent of  .   
select *  from wmsys.WM$PARENT_HIERARCHY_VIEW; 

To get constraints of version enabled tables 


select * from wmsys.USER_WM_CONSTRAINTS;

To get indexes of version enabled tables




select * from wmsys.USER_WM_IND_COLUMNS;

To get  details of  versionenabled tables that are  modified

select * from wmsys.USER_WM_MODIFIED_TABLES;

To get trigger details on version enabled tables




select * from wmsys.USER_WM_TAB_TRIGGERS;

To get details of locked tables


select * from wmsys.USER_WM_LOCKED_TABLES;

To get   details of  version enabled tables

select * from wmsys.USER_WM_VERSIONED_TABLES;

To get errors in workspace 

select * from wmsys.USER_WM_VT_ERRORS;

To get savepoint  details

 select * from wmsys.USER_WORKSPACE_SAVEPOINTS;

To get foreign key of version enabled tables

 select * from wmsys.USER_WM_RIC_INFO;


To  get current hierarchy and depth of  workspace

 select * from wmsys.ALL_VERSION_HVIEW_WDEPTH;











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