Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anil.

Asked: February 14, 2005 - 10:19 am UTC

Last updated: July 10, 2007 - 2:45 pm UTC

Version: 10.1

Viewed 1000+ times

You Asked

Hi tom


I have 70 tables belong to a document object, All the tables have document Id . Also there are 70 corresponding Audit tables.A recod will be inserted into audit table on update and delete (old images of the record) not on Insert. So the latest image will in the actual table.

say

dt1 paranet table woth Document Id PK
dt2 Child table with Document Id FK
..
..
..
dt70 Child table with FK



Now I need to get the audit history of a purticular document object from the point it was created up till now. I have to scan throug this 140 tables(will be a straigh forward index hi) to get the data and sort and group based on the business transactuion and order by time etcc.

I also have to join with an audit header table to get actual user name, role , city and other details of person who created this tranasction since I am not keeping this along with each record. Only foreign key id to audit header table is kept.That is One record in the audit header table per transaction. One transaction may insert/update/delete records into/from more than one table.

Now I have following different ways to do it. I would like to get a comment on the following options.


OPtion 1 :

Run 140 select document_id,aud_id,col1||col2||... > union on 70 audit tables and 70 actual tables with document id access and sort and group the data.


Option 2 :

Run 140 "insert into <formated data> temp_table select from table" into a GLOBAL temporay table with ON COMMIT DELETE ROWS. Then run a query on tamporary table


Option 3:

Keep one audit table for all the 70 ables and insert formated (all fields concatanated) data into this table. Here there is only one Query which reteives all the audit records. But disadvantage is the audit records are of no other use since data is in string not in separate colums. Other Queries on the audit will be difficult,


Some other fact.

Getting History of a document is not a frequent operation
On an average a typical history of a document will have 300-400 records from all the 70 tables.
Response shoud be visibly fast 2-3 sec


Would appreciate some comments on this

Rgds
Anil

and Tom said...

or.....

option #4, just run "70" union all queries (unioning current with history) and get the precise columns you need from each for the report.... (i would not concatenate unless that is how the report was to be printed, I would definitely not use a temporary table)


option #5, use workspace manager and let it do the versioning for you and you just "dbms_wm.goto_date()" to query the data as of a point in time!

</code> http://docs.oracle.com/cd/B14117_01/appdev.101/b10824/toc.htm <code>

we'll start off by creating a WSMGMT user with the appropriate privs


ops$tkyte@ORA10G> CREATE USER wsmgmt IDENTIFIED BY wsmgmt;

User created.

ops$tkyte@ORA10G> GRANT connect, resource, create table to wsmgmt;

Grant succeeded.

ops$tkyte@ORA10G> begin
2 DBMS_WM.GrantSystemPriv
3 ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, ' ||
4 'CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ' ||
5 'ROLLBACK_ANY_WORKSPACE', 'WSMGMT', 'YES');
6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> set echo off
scott@ORA10G>
scott@ORA10G>
scott@ORA10G> set echo off
Now we'll log in as wsmgmt and set up our schema...


wsmgmt@ORA10G> create table emp as select * from scott.emp;
wsmgmt@ORA10G> create table dept as select * from scott.dept;
wsmgmt@ORA10G> alter table emp add constraint emp_pk primary key(empno);
wsmgmt@ORA10G> alter table dept add constraint dept_pk primary key(deptno);
wsmgmt@ORA10G> alter table emp add constraint emp_fk_dept foreign key(deptno)
2 references dept(deptno);
wsmgmt@ORA10G> alter table emp add constraint emp_fk_emp foreign key(mgr)
2 references emp(empno);
wsmgmt@ORA10G> set feedback on
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Version-enable the table. Specify hist option of VIEW_WO_OVERWRITE so that
the xxx_HIST view contains complete history information.


wsmgmt@ORA10G> begin
2 DBMS_WM.EnableVersioning ('emp', 'VIEW_WO_OVERWRITE');
3 DBMS_WM.EnableVersioning ('dept', 'VIEW_WO_OVERWRITE');
4 end;
5 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> column t new_val t
wsmgmt@ORA10G> select to_char(sysdate,'yyyymmddhh24miss') t from dual;

T
--------------
20050214103154

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Next, create workspaces, these workspaces will have their own views
of the data...


wsmgmt@ORA10G> begin
2 DBMS_WM.CreateWorkspace ('ws1');
3 DBMS_WM.CreateWorkspace ('ws2');
4 end;
5 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
By default, we are in the LIVE workspace, now lets get into workspace ws1


wsmgmt@ORA10G> begin
2 DBMS_WM.GotoWorkspace ('ws1');
3 end;
4 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
In this workspace, we'll update the emp table, LIVE/ws2 cannot see this


wsmgmt@ORA10G> update emp set sal = sal * 1.10;

14 rows updated.

wsmgmt@ORA10G> commit;

Commit complete.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Here is the history and current view now for one of the rows of interest


wsmgmt@ORA10G> select ename, sal, comm, wm_workspace, wm_optype,
2 to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
3 to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
4 from emp_hist where ename = 'KING';

ENAME SAL COMM WM_WOR W CREATED RETIRED
---------- ---------- ---------- ------ - --------------- ---------------
KING 5000 LIVE I 14-feb 10:31:49
KING 5500 ws1 U 14-feb 10:31:54

2 rows selected.

wsmgmt@ORA10G> select ename, sal, comm
2 from emp where ename = 'KING';

ENAME SAL COMM
---------- ---------- ----------
KING 5500

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Lets do another mass update and see the effects of that


wsmgmt@ORA10G> update emp set comm = 0.1 * (greatest(0,sal-1000));

14 rows updated.

wsmgmt@ORA10G> commit;

Commit complete.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Notice the history has retired records now


wsmgmt@ORA10G> select ename, sal, comm, wm_workspace, wm_optype,
2 to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
3 to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
4 from emp_hist where ename = 'KING';

ENAME SAL COMM WM_WOR W CREATED RETIRED
---------- ---------- ---------- ------ - --------------- ---------------
KING 5000 LIVE I 14-feb 10:31:49
KING 5500 ws1 U 14-feb 10:31:54 14-feb 10:31:55
KING 5500 450 ws1 U 14-feb 10:31:55

3 rows selected.

wsmgmt@ORA10G> select ename, sal, comm from emp where ename = 'KING';

ENAME SAL COMM
---------- ---------- ----------
KING 5500 450

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
See how LIVE cannot see these changes as yet


wsmgmt@ORA10G> begin
2 DBMS_WM.GotoWorkspace ('LIVE');
3 end;
4 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> select ename, sal, comm
2 from emp
3 where ename = 'KING'
4 /

ENAME SAL COMM
---------- ---------- ----------
KING 5000

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
nor can ws2


wsmgmt@ORA10G> begin
2 DBMS_WM.GotoWorkspace ('ws2');
3 end;
4 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> select ename, sal, comm
2 from emp
3 where ename = 'KING'
4 /

ENAME SAL COMM
---------- ---------- ----------
KING 5000

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
here we will update a row we know has been modified in another workspace
we could use long term locking to avoid this (but we didn't)


wsmgmt@ORA10G> update emp set sal = sal * 1.5 where ename = 'KING';

1 row updated.

wsmgmt@ORA10G> update emp set comm = 0.1 * (greatest(0,sal-1000)) where ename = 'KING';

1 row updated.

wsmgmt@ORA10G> commit;

Commit complete.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Notice the history for the KING row across workspaces..
wsmgmt@ORA10G> select ename, sal, comm, wm_workspace, wm_optype,
2 to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
3 to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
4 from emp_hist where ename = 'KING';

ENAME SAL COMM WM_WOR W CREATED RETIRED
---------- ---------- ---------- ------ - --------------- ---------------
KING 5000 LIVE I 14-feb 10:31:49
KING 5500 ws1 U 14-feb 10:31:54 14-feb 10:31:55
KING 5500 450 ws1 U 14-feb 10:31:55
KING 7500 ws2 U 14-feb 10:31:55 14-feb 10:31:55
KING 7500 650 ws2 U 14-feb 10:31:55

5 rows selected.

wsmgmt@ORA10G> select ename, sal, comm from emp where ename = 'KING';

ENAME SAL COMM
---------- ---------- ----------
KING 7500 650

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
Lets check out this savepoint feature, we want to be able to
come back to this point in time in this workspace for whatever reason


wsmgmt@ORA10G> begin
2 DBMS_WM.CreateSavepoint ('ws2', 'ws2_SP1');
3 end;
4 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
We want to what-if a coup. Blake is taking over...


wsmgmt@ORA10G>
wsmgmt@ORA10G> update emp
2 set mgr = ( select empno from emp where ename = 'BLAKE' )
3 where mgr = ( select empno from emp where ename = 'KING' );

3 rows updated.

wsmgmt@ORA10G> delete from emp where ename = 'KING';

1 row deleted.

wsmgmt@ORA10G> delete from dept where deptno = 40;

1 row deleted.

wsmgmt@ORA10G> commit;

Commit complete.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
the changes are all made now... and committed

wsmgmt@ORA10G> select ename, mgr, sal, comm from emp;

ENAME MGR SAL COMM
---------- ---------- ---------- ----------
SMITH 7902 800
ALLEN 7698 1600 300
WARD 7698 1250 500
MARTIN 7698 1250 1400
TURNER 7698 1500 0
ADAMS 7788 1100
JAMES 7698 950
FORD 7566 3000
MILLER 7782 1300
SCOTT 7566 3000
JONES 7698 2975
BLAKE 7698 2850
CLARK 7698 2450

13 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

3 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
but king mounts a counter attack and comes back...

wsmgmt@ORA10G> begin
2 DBMS_WM.GotoWorkspace ('LIVE');
3 DBMS_WM.RollbackToSP ('ws2', 'ws2_SP1');
4 DBMS_WM.GotoWorkspace ('ws2');
5 end;
6 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G> select ename, sal, comm, wm_workspace, wm_optype,
2 to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
3 to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
4 from emp_hist where ename = 'KING';

ENAME SAL COMM WM_WOR W CREATED RETIRED
---------- ---------- ---------- ------ - --------------- ---------------
KING 5000 LIVE I 14-feb 10:31:49
KING 5500 ws1 U 14-feb 10:31:54 14-feb 10:31:55
KING 5500 450 ws1 U 14-feb 10:31:55
KING 7500 ws2 U 14-feb 10:31:55 14-feb 10:31:55
KING 7500 650 ws2 U 14-feb 10:31:55

5 rows selected.

wsmgmt@ORA10G> select ename, sal, comm from emp where ename = 'KING';

ENAME SAL COMM
---------- ---------- ----------
KING 7500 650

1 row selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
now we would like to merge the changes into the LIVE workspace from ws1

wsmgmt@ORA10G> EXECUTE DBMS_WM.GotoWorkspace ('LIVE');

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G> select ename, sal, comm from emp;

ENAME SAL COMM
---------- ---------- ----------
SMITH 800
ALLEN 1600 300
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500 0
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
SCOTT 3000

14 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
That was easy enough... However

wsmgmt@ORA10G> EXECUTE DBMS_WM.MergeWorkspace ('ws1');

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G> select ename, sal, comm from emp;

ENAME SAL COMM
---------- ---------- ----------
MILLER 1430 43
FORD 3300 230
JAMES 1045 4.5
ADAMS 1210 21
TURNER 1650 65
KING 5500 450
SCOTT 3300 230
CLARK 2695 169.5
BLAKE 3135 213.5
MARTIN 1375 37.5
JONES 3272.5 227.25
WARD 1375 37.5
ALLEN 1760 76
SMITH 880 0

14 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
ws2 gives us some hard times -- there is a conflict we have to resolve

wsmgmt@ORA10G> EXECUTE DBMS_WM.MergeWorkspace ('ws2');
BEGIN DBMS_WM.MergeWorkspace ('ws2'); END;

*
ERROR at line 1:
ORA-20055: conflicts detected for workspace: 'ws2' in table: 'WSMGMT.EMP'
ORA-06512: at "SYS.LT", line 5852
ORA-06512: at line 1


wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
going into that workspace, we can see the conflicts and decide how to resolve
them

wsmgmt@ORA10G> EXECUTE DBMS_WM.GotoWorkspace ('ws2');

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> select * from emp_conf;

WM_W EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO WM_
---- ---------- ---------- --------- --------- ----- ---- ------ ---
ws2 7839 KING PRESIDENT 17-NOV-81 7500 650 10 NO
BASE 7839 KING PRESIDENT 17-NOV-81 5000 10 NO
LIVE 7839 KING PRESIDENT 17-NOV-81 5500 450 10 NO

3 rows selected.

wsmgmt@ORA10G> set echo off
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
we decide the child should win....


wsmgmt@ORA10G> begin
2 DBMS_WM.BeginResolve( 'ws2' );
3 DBMS_WM.ResolveConflicts('ws2',
4 'emp',
5 'empno = 7839',
6 'CHILD' );
7 commit;
8 DBMS_WM.CommitResolve( 'ws2' );
9 end;
10 /

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
and now we can merge easily...

wsmgmt@ORA10G> execute dbms_wm.gotoWorkspace( 'LIVE' );

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> EXECUTE DBMS_WM.MergeWorkspace ('ws2');

PL/SQL procedure successfully completed.

wsmgmt@ORA10G> select ename, sal, comm from emp;

ENAME SAL COMM
---------- ---------- ----------
KING 7500 650
MILLER 1430 43
FORD 3300 230
JAMES 1045 4.5
ADAMS 1210 21
TURNER 1650 65
SCOTT 3300 230
CLARK 2695 169.5
BLAKE 3135 213.5
MARTIN 1375 37.5
JONES 3272.5 227.25
WARD 1375 37.5
ALLEN 1760 76
SMITH 880 0

14 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G> exec dbms_wm.gotodate( to_date('&T','yyyymmddhh24miss') )

PL/SQL procedure successfully completed.

wsmgmt@ORA10G>
wsmgmt@ORA10G> select ename, sal, comm from emp;

ENAME SAL COMM
---------- ---------- ----------
SMITH 800
ALLEN 1600 300
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500 0
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
SCOTT 3000

14 rows selected.

wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G>
wsmgmt@ORA10G> set echo off
a little cleanup and we are done...


Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent demo of the features of Workspace Manager

A reader, February 14, 2005 - 12:29 pm UTC


Another very cool article

Dan Kefford, February 14, 2005 - 1:45 pm UTC

Tom...

Can WSM also capture _who_ made changes to data?

Tom Kyte
February 14, 2005 - 5:37 pm UTC

yes, I just didn't show that.

Some Doubts

Anil, February 24, 2005 - 3:59 am UTC

Hi Tom

In Option 4 you have mentioned UNION ALL and not concatanate. How can I take induvidaual fields without concatanating from different tables since different tables have different columns. One way would be to use Null values to make the number columns same in the query .. Is there any other trick to do that

Select a,b,c,NULL,NULL from taba
union all
select null,null,null,c,d from tabb


Also in Workspace manager can we add additional information to the workspace while creating teh record. I have addition session information a part of the business transaction. If I am inserting into a audit table I can add these information in thr insert statment into the audit table. Is that possible in WS. IF Then WS is the way to go I think...

Rgds
Anil

Tom Kyte
February 24, 2005 - 5:46 am UTC

yes, you would have to use NULLS where appropriate (and to_number(Null), to_date(null) or cast( null as number) when needed at well - to get the right types setup for the query)

"can we add additional information to the workspace"?


Not sure what you mean by that, you have access to anything you had access to without workspace manager?

Alexander the ok, June 28, 2006 - 9:23 am UTC

Hi Tom,

I have a problem where I'm trying to drop a user but it won't let me because it says:

ORA-20031: All version enabled tables owned by 'SCOTT' must be disabled first.

The table that was originally enabled with dbms_wm has already been dropped. Of course I would disabled it had I know it mattered for some reason when you go to drop this stuff. What can I do?

Tom Kyte
June 28, 2006 - 9:33 am UTC

version and detailed steps to reproduce?

Alexander the ok, June 28, 2006 - 9:45 am UTC 

SQL> drop table scott.backup_lt;
drop table scott.backup_lt
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20061: versioned objects have to be version disabled before being dropped
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 297
ORA-06512: at "SYS.NO_VM_DROP_PROC", line 60
ORA-06512: at line 3


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

I don't what else I can provide.  The issue is the table that I enabled with the wm is now gone.  So I'm not sure what it's complaining about.  The backup_lt table is one of the tables that the wm creates when you version enable (but you know that already).

Do you understand my problem?

Tom Kyte
June 28, 2006 - 9:55 am UTC

do not try to drop the workspace tables directly, use the DBMS_WM package to disable versioning on the original table. I don't know how you could drop the "original table", since it ceased to exist when you versioned it:

a@ORA9IR2> create table test( x int primary key, y int );

Table created.

a@ORA9IR2>
a@ORA9IR2> exec dbms_wm.EnableVersioning( 'TEST' );

PL/SQL procedure successfully completed.

a@ORA9IR2>
a@ORA9IR2> drop table test;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist

and you cannot drop the view that replaced the original table:

a@ORA9IR2> drop view test;
drop view test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20061: versioned objects have to be version disabled before being dropped
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 297
ORA-06512: at "SYS.NO_VM_DROP_PROC", line 29
ORA-06512: at line 3


Now, if you dropped the "_AUX" table, just put it back (just has to exist)


a@ORA9IR2> drop table test_aux;

Table dropped.

a@ORA9IR2> exec dbms_wm.DisableVersioning( 'TEST' );
BEGIN dbms_wm.DisableVersioning( 'TEST' ); END;

*
ERROR at line 1:
ORA-20231: table 'A.TEST' failed during DisableVersioning. Error:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.LTDDL", line 831
ORA-06512: at "SYS.LTDDL", line 1663
ORA-06512: at "SYS.LTDDL", line 1648
ORA-06512: at "SYS.LT", line 8116
ORA-06512: at line 1


a@ORA9IR2>
a@ORA9IR2> create table test_aux ( x int );

Table created.

a@ORA9IR2> exec dbms_wm.DisableVersioning( 'TEST' );

PL/SQL procedure successfully completed.


If that is not working (because you dropped something), please utilize support.


The 'step by steps' I would need are:

a) create this table
b) run this dbms_wm command on it
c) do this (eg: drop this thing)
d) observe this


a method to "reproduce" - it'll be what support wants as well.

Alexander the ok, June 28, 2006 - 12:03 pm UTC 

I'm having a different problem now:

SQL> exec dbms_wm.disableversioning ('BACKUP');
BEGIN dbms_wm.disableversioning ('BACKUP'); END;

*
ERROR at line 1:
ORA-20231: table 'SCOTT.BACKUP' failed during DisableVersioning. Error:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.LTDDL", line 831
ORA-06512: at "SYS.LTDDL", line 1663
ORA-06512: at "SYS.LTDDL", line 1648
ORA-06512: at "SYS.LT", line 8116
ORA-06512: at line 1

Do you know why I would get this?  Why would it try to create something when called a disable function?

Tom Kyte
June 28, 2006 - 4:48 pm UTC

you must - repeat must - provide exact steps you took to "get into this situation"


using sql_trace, we can find

o creates of the original primary key index.
o creates of a temporary procedure
o alter table to rename the _lt table back

turn on trace and edit the raw trace file looking for #err to see if the failing statement is "obvious"



Alexander the ok, June 28, 2006 - 4:42 pm UTC

Tom I'm basically having the same problem as this guy:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4529781014729#12830520780183 <code>

You know it won't even let me drop the tablespace this stuff is in? I'm totally hosed unless you can come up with some magic. I can't even believe it won't let you just drop. Why on earth would they do that....

Tom Kyte
June 28, 2006 - 5:13 pm UTC

why on earth would you mess with the unerlying structures?

But anyway - unless and until you tell me "what you did", I cannot help anymore than I have and my advice would be consistent with what I said on that other thread.


Did you even try the sql_trace?

Alexander the okj, June 29, 2006 - 10:33 am UTC

Ok I was able to drop the user and everything else by looking at the trace. I have a feeling you are definitely not going to approve of how I did it though. For whatever reason there was still a row in wmsys.wm$versioned_tables for the table I enabled. I tried every which way, putting the tables back, disabling, renaming etc..so I was forced to do something disparate

I will try and retrace my steps to produce a test case for you. It's just that I originally version enabled this table a month or two ago so it's hard for me to remember everything. Thanks for your help and patience as always.

Tom Kyte
June 29, 2006 - 10:39 am UTC

you are correct, I would not approve, do not. Your system is somewhat "suspect" at this point - if you use versioned tables for anything - there could be issues.

You should have worked it via support (like I said a couple of times......)

Excellent

CT, July 09, 2007 - 6:04 pm UTC

Hi Tom,

Excellent Example. I was able to reproduce all the features discussed here in this example in a development system but when I tried something different I am getting a problem.
-- Session 1----------
1.Created single table
2. enbled versioning
3. created workspace (ws1)
4. played around with the help of this article.
5. Merged the work space
6. verified the results by going back to the past in live work space.
everything is perfect upto this point of time.

----- Now I loged into new session session 2 ---
1. inserted a new row into the table.
2. commited the transaction
3. updated the same row after some time.

Problem is

-- went back to session one --

1. gotoworkspace ws1

Here when I delete or update this row or select , I am not able to see this row in the table. Is that we can only see the rows what we have inserted or updated in the workspace ? or Am I missing something, because very first time I never had this workspace in place. If you can guide me thru ,I would like to put this functionality in production.
Here is the sequence what I was trying to do.

in LIVE workspace

ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED where sales_dtl_nbr = 5216180; 2

SALES_DTL_NBR UNITS
------------- ----------
5216180 25

ct@DEVEL> select to_char(sysdate,'yyyymmddhh24miss') t from dual;

T
--------------
20070709170021



ct@DEVEL> execute dbms_wm.gotoworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> update ct_sales_verson_enabled set units = 255 where sales_dtl_nbr = 5216180;

0 rows updated.

ct@DEVEL>
ct@DEVEL>
ct@DEVEL> select sales_dtl_nbr, units
2 from
3 ct_sales_verson_enabled
4 where sales_dtl_nbr = 5216180;

no rows selected

ct@DEVEL> execute dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED where sales_dtl_nbr = 5216180; 2

SALES_DTL_NBR UNITS
------------- ----------
5216180 25

ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED_HIST where sales_dtl_nbr = 5216180; 2

SALES_DTL_NBR UNITS
------------- ----------
5216180 25

ct@DEVEL>
select sales_dtl_nbr,units, wm_workspace, wm_optype,
to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
from CT_SALES_VERSON_ENABLED_HIST where sales_dtl_nbr = 5216180;ct@DEVEL> 2 3 4

SALES_DTL_NBR UNITS WM_WORKSPACE W CREATED
------------- ---------- ------------------------------ - ---------------
RETIRED
---------------
5216180 25 LIVE I 09-jul 16:49:56



ct@DEVEL>
ct@DEVEL>
ct@DEVEL> update ct_sales_verson_enabled set units=units+10 where sales_dtl_nbr=5216180;

1 row updated.

ct@DEVEL> commit;

Commit complete.

ct@DEVEL> exec dbms_wm.gotodate( to_date('&T','yyyymmddhh24miss') )

PL/SQL procedure successfully completed.

ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED where sales_dtl_nbr = 5216180; 2

SALES_DTL_NBR UNITS
------------- ----------
5216180 25

ct@DEVEL> delete from sales_dtl where sales_dtl_nbr = 5216180;

0 rows deleted.

ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED where sales_dtl_nbr = 5216180; 2

SALES_DTL_NBR UNITS
------------- ----------
5216180 25

ct@DEVEL> execute dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ct@DEVEL> execute dbms_wm.gotoworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> select sales_dtl_nbr,units, wm_workspace, wm_optype,
to_char(wm_createtime,'dd-mon hh24:mi:ss') created,
to_char(wm_retiretime,'dd-mon hh24:mi:ss') retired
from CT_SALES_VERSON_ENABLED_HIST where sales_dtl_nbr = 5216180; 2 3 4

SALES_DTL_NBR UNITS WM_WORKSPACE W CREATED
------------- ---------- ------------------------------ - ---------------
RETIRED
---------------
5216180 25 LIVE I 09-jul 16:49:56
09-jul 17:12:55

5216180 35 LIVE U 09-jul 17:12:55


ct@DEVEL> update CT_SALES_VERSON_ENABLED set units =units+300
where sales_dtl_nbr = 5216180; 2

0 rows updated.


ct@DEVEL> select sales_dtl_nbr,units
from CT_SALES_VERSON_ENABLED where sales_dtl_nbr = 5216180; 2

no rows selected



Thank you very much for your time.

--CT



Tom Kyte
July 09, 2007 - 9:00 pm UTC

1) unless in the other session you updated in ws1, you will not see the changes. ws1 - when you branched it - is what it is, it is a version. modifications to LIVE will not affect it. this is by design, it is the entire GOAL

Chain reaction

CT, July 10, 2007 - 10:05 am UTC

Hi Tom,

Thank you for the answer.Your Answer triggered me to ask you this and also to go thru the documenation.

1. In a live work space user A inserts 100 rows in a customer table as ACTIVE flag as 'Y'.Similarly In workspace ws1 user B inserts 100 more customers with active flag as 'Y' . The transactions in ws1 are commited and merged to the LIVE workspace. One month from now we go to workspace ws1 and update the customers ACTIVE flag as 'N' if they bought nothing in the last 15 days. This transaction will update only the rows inserted in worksapce ws1.
On testing ,that is what has happened I have pasted the sequence for your perusal, (I have inserted two rows in two different workspaces and updated the entry_dttm field in ws1 ) and now the verson in live work space has wrong results......I was expecting it to throw an error message while merging but it did not.
ct@DEVEL> execute dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ct@DEVEL> INSERT INTO ct_sales_verson_enabled
2 select
3 5216179, SALES_HDR_NBR, ORG_NBR_SELLER,
4 ORG_NBR_PURCHASER, ITEM_NBR, UNITS,
5 SHIP_DT, INV_NO, EXT_NET_AMT,
6 CASES_SHIPPED, BOXES_SHIPPED, UNITS_SHIPPED,
7 ORG_NBR_MFR, DISTRIBUTOR_ID, MANUFACTURER_ID,
8 CUSTOMER_ID, DISTRIBUTOR_ITEM_ID, PACK_COUNT,
9 PRODUCT_SIZE, SYSDATE, RECORD_NBR,
10 ITEM_NBR_MATCH_CD, DST_ITEM_IDENTIFIER, SPEC_PRICE_FLG
11 FROM CT_SALES_VERSON_ENABLED
12 where sales_dtl_nbr = 5216180 ;

1 row created.

ct@DEVEL> commit;

Commit complete.

ct@DEVEL> execute dbms_wm.gotoworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> INSERT INTO ct_sales_verson_enabled
2 select
3 5216178, SALES_HDR_NBR, ORG_NBR_SELLER,
4 ORG_NBR_PURCHASER, ITEM_NBR, UNITS,
5 SHIP_DT, INV_NO, EXT_NET_AMT,
6 CASES_SHIPPED, BOXES_SHIPPED, UNITS_SHIPPED,
7 ORG_NBR_MFR, DISTRIBUTOR_ID, MANUFACTURER_ID,
8 CUSTOMER_ID, DISTRIBUTOR_ITEM_ID, PACK_COUNT,
9 PRODUCT_SIZE, SYSDATE, RECORD_NBR,
10 ITEM_NBR_MATCH_CD, DST_ITEM_IDENTIFIER, SPEC_PRICE_FLG
11 FROM CT_SALES_VERSON_ENABLED
12 where sales_dtl_nbr = 5216180 ;

1 row created.

ct@DEVEL> commit;

Commit complete.

ct@DEVEL> EXECUTE DBMS_WM.MergeWorkspace ('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> execute dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ct@DEVEL> EXECUTE DBMS_WM.MergeWorkspace ('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> select sales_dtl_nbr,units
2 from ct_sales_verson_enabled
3 where sales_dtl_nbr = 5216178;

SALES_DTL_NBR UNITS
------------- ----------
5216178 35

ct@DEVEL> execute dbms_wm.gotoworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> select count(*)
2 from ct_sales_verson_enabled
3 where trunc(entry_dttm) = trunc(sysdate);

COUNT(*)
----------
1

ct@DEVEL> update ct_sales_verson_enabled
set entry_dttm = sysdate+1
where trunc(entry_dttm) = trunc(sysdate); 2 3

1 row updated.

ct@DEVEL> commit;

Commit complete.

ct@DEVEL> execute dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ct@DEVEL> execute dbms_wm.mergeworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> select count(*)
from ct_sales_verson_enabled
where trunc(entry_dttm) = trunc(sysdate); 2 3

COUNT(*)
----------
1

ct@DEVEL>


2. I am going thru the documentation link you have provided and found that we can refresh a workspace to get the latest version of the table.This solves part of my problem once I refresh the workspace I can see the row from LIVE workspace. I can freeze the live workspace to do any DML and enable only the 'WS1' and 'WS2' but everytime when some one get into the workspace it needs to refreshed.otherwise we are leaving the databse in a wrong state, which is something can happen.

ct@DEVEL> select count(*)
2 from ct_sales_verson_enabled;

COUNT(*)
----------
216965

ct@DEVEL> execute dbms_wm.refreshworkspace('ws1');

PL/SQL procedure successfully completed.

ct@DEVEL> select count(*)
2 from ct_sales_verson_enabled;

COUNT(*)
----------
216966

3) Is there any harm if we have only one workspace( LIVE) and enable versioning because by that way I won't hang the table in LIVE workspace in a inconsistent state like what I have done in point no 1 ws1.In reality some of the super users log into TOAD and modify the data in LIVE workspace. someone may get into WS1 and update without refreshing WS1 .

I really appreciate your time and guidence in this .

Thanks again
CT

Tom Kyte
July 10, 2007 - 12:02 pm UTC

this was too long to read everything - i just read (1)

so, why did you think that?

Sorry

CT, July 10, 2007 - 12:38 pm UTC

Hi Tom,
Sorry about that long posting. I apologize if I caused any inconvienience.

My intention was to update the rows in the table(irrespective of in which workspace it was inserted or modified) for the give condition or don't update if the workspace does not have latest commited data which is in LIVE at the time we entered the work space, but RDBMS updates the rows in the ws1 even though the data set is not the latest committed version in live so this leads to the wrong result set when you merge this row in to live.

I think I have not clearly understood the mechanism here.I am considering following two possibilities as solution.
1. Everytime You go to a work space , refresh the work space so that you will have the latest version of the table. Then do the DML.

2. Just have a LIVE work space and do everything in live.(Mine is a very simple requirement that I need to see the history of changes and the data at anypoint of time)

Expecting your opinions or solutions to this.

Thank you very much for consideration.

CT
Tom Kyte
July 10, 2007 - 2:45 pm UTC

it is not the wrong result, it is the result they intended and designed it to do.

when you create that branch, ws1, you freeze it - as of that point in time. that is the goal, the way it works.

1) if you want the most current data in the workspace at any point in time, you would branch the workspace right then and there. As soon as you branch, it is AS IF you copied the live data.

2) if you just need a history, you never need the workspaces, just version enable the data.

Continually Refreshed Workspaces

CT, July 10, 2007 - 2:27 pm UTC

Hi Tom,
From documenation I found if we create the workspace 'ws1'
with isrefreshed = true will solve my problem.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14253/long_ref.htm#i78729

Thanks for the original example , inspired me to get into this and for the kind reviews.

Thank you very much
CT

workspace

A reader, April 29, 2009 - 10:35 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library