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;











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

Insertion with condition

If there is a table with fields rt (number) which is a primary key and DF varchar2.If there r 10 records and first 5 records r deleted then when inserting records it want to start from 1 and then increment after each insert .

create table DF(DF VARCHAR2(3000),RT NUMBER(2) not null);

alter table DF add constraint E primary key (RT);

insert into df(rt,df)
with
all_numbers as (select level num from dual connect by level <=99),
available_number as (select num,rownum line from
(select num from all_numbers minus select rt from df order by 1) order by num )
select num,'B-'||rownum from available_number where rownum <=10;

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

Unknown Functions

A Word : Risk of using Oracle Undocumented functions or procedures here is that without any notice Oracle may remove them and there will not be support from ORACLE

To get similar characters among the two fields (from 9i onwards)

SELECT merge$actions('lNvl', 'pppl') FROM dual;
SELECT replace(merge$actions(1204, 1260),'BB','') FROM dual;

------------------------------------------------------------------------------
To view blob details (from 10g onwards)

SELECT sys_op_cl2c(ad_finaltext),s.product_id FROM pm.print_media s

-----------------------------------------------------------------------------
To get blocknumber of a particular tables (from 9i onwards)

select dbms_rowid.rowid_block_number(rowid) from dual;

SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)),
COUNT( dbms_rowid.rowid_block_number(rowid))FROM pm.print_media;

----------------------------------------------------------------------
To convert into HEX (from 9i onwards)

SELECT sys_op_descend('9')FROM dual;

-----------------------------------------------------------------------------
To show generate null coloumn (from 9i onwards)

SELECT sys_op_lvl(1,21,1,1,1,1 ,2) FROM dual;

-------------------------------------------------------------------------------
To generates and returns a globally unique identifier of 16 bytes The returned data type is RAW(16) (from 9i onwards)

SELECT sys_guid() FROM dual;

-----------------------------------------------------------------------
To get comma seperated values in rows wise (from 10g onwards)

select sys.odcivarchar2list('Football','Rugby') from dual;

select column_value from table(
select sys.odcivarchar2list('Football','Rugby') from dual);
-----------------------------------------------------------------------
To get data in ascii characters (from 10g onwards)


SELECT sys_op_c2c(9068986) FROM dual;
-------------------------------------------------------------------------
TO get number representation of rowid (from 9i onwards)

SELECT rowid, sys_op_rpb(rowid),dbms_rowid.rowid_block_number(rowid) FROM scott.emp
-------------------------------------------------------------------------
Similar to null (from 10g onwards)

SELECT sys_fbt_insdel, decode(sys_fbt_insdel,null,1,0) FROM dual;
-------------------------------------------------------------------------
To convert hex to num (from 9i onwards)


SELECT sys_op_rawtonum('000000FF'),UTL_RAW.CAST_FROM_BINARY_INTEGER(255) FROM dual;
-------------------------------------------------------------------------
To check whether a number is even or odd (from 9i onwards)

Return the value of the bit at position N
The return value is 0 or 1

SELECT sys_op_vecbit('255',0),sys_op_vecbit('255',1),sys_op_vecbit('22',1),sys_op_vecbit('22',0) FROM dual;
-------------------------------------------------------------------------
Return the binary AND of two hex values (from 9i onwards)

SELECT sys_op_vecand('FC','FD') from dual;
SELECT sys_op_vecand('FF','FD') from dual;
-----------------------------------------------------------------------
Return the binary OR of two hex values (from 9i onwards)


SELECT sys_op_vecor(('FC'),('FE')) from dual;
-----------------------------------------------------------------------
Return the binary XOR of two hex values (from 9i onwards)

SELECT sys_op_vecxor(('FF'),('FE')) from dual;
-----------------------------------------------------------------------
To get ref value visible (from 9i onwards)

SELECT sys_op_r2o(CUSTOMER_REF),CUSTOMER_REF FROM oe.oc_orders WHERE rownum = 1;

--------------------------------------------------------------------------------
Query to show which all values are present in a table and not present

SELECT nvl(to_char(e.EID), column_value||' Not found') empno from table(sys.odcinumberlist(7369,7370,7566,7555,1))l left outer join emp e on e.EID = l.column_value order by 1

sys.odcinumberlist is the default varray that oracle supplies . If you want to create your own type

CREATE TYPE typ_p is varray (32767) of number

SELECT * from table(typ_p(7369,7370,7566,7555,1 ))

-----------------------------------------------------------------------
To compare null (from 9i onwards)

SELECT 'hi there' FROM DUAL WHERE NULL = NULL;

SELECT 'hi there' FROM DUAL WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);
SELECT 'hi there' FROM DUAL WHERE nvl(null,'FF')='FF';
select sys_op_map_nonnull(null) from dual; --similat to nvl(null,'FF')
select * from dual where sys_op_map_nonnull(null) = 'FF';
SELECT 'hi there' FROM DUAL WHERE to_char(1||NULL) = to_char(1||NULL);
-----------------------------------------------------------------------

JSP Format Model

To convert date into numbers and words

select to_char(sysdate,'J') "WORDS" from dual;
select to_date(to_char(sysdate,'J'),'J') "WORDS" from dual;
select to_char(to_date( to_char(sysdate,'J') ,'J'), 'JSP') "WORDS" from dual;

----*--------------------------------*-----------------------
To convert numbers into words

select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

------*-------------------------------------*------------------------
To convert words into numbers


SELECT LEVEL wordasint FROM dual
WHERE TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') = 'ONE HUNDRED TWENTY-THREE'
CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') != 'ONE HUNDRED TWENTY-THREE'
AND LEVEL < 124; ---FOR 10 g and above
SELECT sp, n
FROM (SELECT 'EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE' sp FROM dual)
MODEL DIMENSION BY (1 dim)
MEASURES (0 n, sp) RULES ITERATE (86400) UNTIL (TO_CHAR(DATE '0001-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
(n[1]=ITERATION_NUMBER);

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