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;











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

Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;


select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;


WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno

------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1

Split values ;///; Pyramid

To split values

CREATE OR REPLACE Function Tokenizer(p_string In VarChar2,
p_separators In VarChar2)
Return varchar2_table Pipelined
Is
v_strs dbms_sql.varchar2s;
Begin
/*SELECT Tokenizer('1E030N,898989,6565,3535,67676',',') FROM DUAL;*/
With sel_string As (Select p_string fullstring From dual)
Select substr(fullstring, beg+1, end_p-beg-1) token
Bulk Collect Into v_strs
From (Select beg, Lead(beg) Over (Order By beg) end_p, fullstring
From (Select beg, fullstring
From (Select Level beg, fullstring
From sel_string
Connect By Level <= length(fullstring)
)
Where instr(p_separators,substr(fullstring,beg,1)) >0
Union All
Select 0, fullstring
From sel_string
Union All
Select length(fullstring)+1, fullstring
From sel_string)
)
Where end_p Is Not Null
And end_p > beg + 1;
For i In v_strs.first..v_strs.last Loop
PIPE ROW(v_strs(i));
End Loop;
RETURN;
End Tokenizer;

----------------------------------------------------------------
Check this query

select wmsys.wm_concat(case when Rn <=46 then Rn end) over(order by Rn) str
from (select RowNum as rn from all_catalog where RowNum <= 46);

To get diff data 's of similar id in a row

-----------

create or replace type string_agg_type as object (
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;

end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

select deptno, stragg(ename)
from scott.emp
group by deptno
/

From 10g onwards No need for user defined function and type , We can use Oracle supplied function

select deptno, wmsys.wm_concat(ename)
from scott.emp group by deptno

In Oracle 10g  wm_concat is an oracle undocumnented function 
 
From 11g onwards , We can use Oracle supplied listlagg function .

select distinct listagg(orcl, ',')   within group(order by orcl) over(partition by 1) orcl  from
 ( select 'ORACLE9i' orcl from dual
 union
 select 'ORACLE10g' from dual
union select 'ORACLE11g' from dual )  c ;

Instead of wm_concat  in Oracle 10 g

SELECT DEPARTMENT_ID , RTRIM(XMLAGG(XMLELEMENT(E, FIRST_NAME || ',')).EXTRACT('//text()'), ',') ENAMES FROM EMPLOYEES GROUP BY DEPARTMENT_ID ;

 SELECT SUBSTR(SYS_CONNECT_BY_PATH(username, ';'), 2) csv FROM (SELECT username, ROW_NUMBER() OVER(ORDER BY username) rn, COUNT(*) OVER() cnt FROM all_users where rownum <= 3) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1;

Level queries

To get the higher manager of a particular employee


select yy.empno,yy.mgr,yy.ename,rt.empno,rt.mgr,rt.ename from
(select * from emp t
connect by prior t.empno=t.mgr start with mgr is null) yy,
emp rt
where yy.empno=rt.mgr --and rt.empno=7782

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

To get repeating data

select deptno,dname,loc,ltrim(ename,' ,') ename,ltrim(hiredate,' ,') hiredate,ltrim(mgr,' ,') mgr
,ltrim(job,' ,') job
from (
select deptno,dname,loc,SYS_CONNECT_BY_PATH (ename,' ,') ename,SYS_CONNECT_BY_PATH (hiredate,' ,') hiredate,
SYS_CONNECT_BY_PATH (job,' ,') job,SYS_CONNECT_BY_PATH (mgr,' ,') mgr
from (
select r.deptno,r.dname,r.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm ,
row_number() over (partition by r.deptno order by r.deptno) r,
row_number() over (partition by r.deptno order by r.deptno) -1 c
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
) start with r = 1
connect by c = prior r
order by rownum desc)
where rownum = 1 ;


OR


select r.deptno,r.dname,r.loc,wmsys.wm_concat(e.ename),wmsys.wm_concat(e.job),wmsys.wm_concat(e.mgr),wmsys.wm_concat(e.hiredate),wmsys.wm_concat(e.sal),wmsys.wm_concat(e.comm)
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
group by r.deptno,r.dname,r.loc