CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle LT Built-in Package
DBMS_WM Synonym &
Workspace Manager
Version 11.1
 
General
Purpose Workspace versioning
Source owmlts.plb
First Availability 9.2

Constants
Name Data Type Value
IMPORT_ALL_DATA VARCHAR2 Undocumented

The following have been found in Oracle scripts and are representatives of a class of built-in constants

wmsys.lt.optimistic_locking
wmsys.wm_error.raiseerror(wmsys.lt.wm_error_172_no);
wmsys.wm_error.raiseerror(wmsys.lt.wm_error_192_no, err_msg);
wmsys.wm_error.raiseerror(wmsys.lt.wm_error_195_no, err_msg_full);


Dependencies
dba_workspaces all_workspace_savepoints
dba_workspace_privs all_workspaces
dba_workspace_savepoints user_workspaces
dba_workspace_sessions user_workspace_privs
all_workspaces user_workspace_savepoints
all_workspace_privs all objects in the WMSYS schema
all_wm_versioned_tables user_wm_versioned_tables

SELECT name
FROM dba_dependencies
WHERE referenced_name IN ('DBMS_WM', 'LT')
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name IN ('DBMS_WM', 'LT');
WM Administration Role WM_ADMIN_ROLE
See Workspace Manager Demo Below

WM Events
ALL_EVENTS WORKSPACE_MERGE_W_REMOVE
TABLE_MERGE_W_REMOVE_DATA WORKSPACE_MERGE_WO_REMOVE
TABLE_MERGE_WO_REMOVE_DATA WORKSPACE_REFRESH
TABLE_REFRESH WORKSPACE_REMOVE
TABLE_ROLLBACK WORKSPACE_ROLLBACK
WORKSPACE_COMPRESS WORKSPACE_VERSION
WORKSPACE_CREATE

WM Lockmodes
C (carry-forward) mode locks rows in the current workspace with the same locking mode as the corresponding rows in the parent workspace.
E Exclusive
S (shared) mode locks the rows in the parent workspace and the corresponding rows in the current workspace; however, other users in the current workspace (but no users in the parent workspace) can change values in these rows.
VE (version-exclusive) mode locks the rows in the previous version and the corresponding rows in the current version such that only the user that set the lock can change the values; no other users (in any workspace) can change the values.
WE (workspace-exclusive) mode locks the rows in the previous version and the corresponding rows in the current version such that only the user that set the lock can change the values in the current workspace; however, other users in other workspaces can change the values.
WM System Parameters
ALLOW_CAPTURE_EVENTS CR_WORKSPACE_MODE
ALLOW_MULTI_PARENT_WORKSPACES FIRE_TRIGGERS_FOR_NONDML_EVENTS
ALLOW_NESTED_TABLE_COLUMNS NONCR_WORKSPACE_MODE
WM System Privileges
ACCESS_ANY_WORKSPACE REMOVE_ANY_WORKSPACE
CREATE_ANY_WORKSPACE ROLLBACK_ANY_WORKSPACE
MERGE_ANY_WORKSPACE

WM Trigger Events
Event Description
DBMS_WM.DML Only for DML operations
DBMS_WM.TABLE_IMPORT Import table
DBMS_WM.TABLE_MERGE_W_REMOVE_DATA Merge table and remove data
DBMS_WM.TABLE_MERGE_WO_REMOVE_DATA Merge table without removing data
DBMS_WM.WORKSPACE_MERGE_W_REMOVE Merge and remove workspace
DBMS_WM.WORKSPACE_MERGE_WO_REMOVE Merge without removing workspace
WM Workspace Privileges
ACCESS_WORKSPACE REMOVE_WORKSPACE
CREATE_WORKSPACE ROLLBACK_WORKSPACE
MERGE_WORKSPACE
 
ADD_TOPO_GEOMETRY_LAYER
Add a topology geometry layer from a version-enabled feature table to a topology dbms_wm.add_topo_geometry_layer(
topology                 IN VARCHAR2,
table_name               IN VARCHAR2,
column_name              IN VARCHAR2,
topo_geometry_layer_type IN VARCHAR2);
exec dbms_wm.Add_Topo_Geometry_Layer('CITY_DATA', 'LAND_PARCELS', 'FEATURE', 'POLYGON');
 
ADDASPARENTWORKSPACE
Adds a workspace as a parent workspace to a child workspace in a multiparent workspace environment dbms_wm.AddAsParentWorkspace(
workspace        IN VARCHAR2,
parent_workspace IN VARCHAR2,
auto_commit      IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.AddAsParentWorkspace('ws1', 'ws0', TRUE);
 
ADDUSERDEFINEDHINT (new in 11g?)
? dbms_wm.AddAsParentWorkspace(
hint_id  IN NUMBER,
table_id IN VARCHAR2,
hint     IN VARCHAR2);
TBD
 
ALTERSAVEPOINT
Change Savepoint Description dbms_wm.AlterSavepoint(
workspace      IN VARCHAR2,
sp_name        IN VARCHAR2,
sp_description IN VARCHAR2);
exec dbms_wm.AlterSavepoint('ws1', 'SP1', 'First SP');
 
ALTERVERSIONEDTABLE
Alters a version-enabled table to add valid time support, rename a constraint, or rename an index dbms_wm.AlterVersionedTable(
table_name        IN VARCHAR2,
alter_option      IN VARCHAR2,
parameter_options IN VARCHAR2 DEFAULT NULL,
ignore_last_error IN BOOLEAN  DEFAULT FALSE);
exec dbms_wm.AlterVersionedTable('T', 'ADD_VALID_TIME');
 
ALTERWORKSPACE
Change Workspace Description dbms_wm.AlterWorkspace(
workspace             IN VARCHAR2,
workspace_description IN VARCHAR2);
exec dbms_wm.AlterWorkSpace('ws1', 'First Workspace');
 
BEGINBULKLOADING

Starts the bulk loading process for a version-enabled table
dbms_wm.BeginBulkLoading(
table_name           IN VARCHAR2,
workspace            IN VARCHAR2,
version              IN INTEGER,
check_for_duplicates IN BOOLEAN DEFAULT TRUE,
ignore_last_error    IN BOOLEAN DEFAULT FALSE,
single_transaction   IN BOOLEAN DEFAULT FALSE);
DECLARE
 version PLS_INTEGER;
BEGIN
  SELECT dbms_wm.GetBulkLoadVersion('ws1')
  INTO version
  FROM dual;

  dbms_wm.BeginBulkLoading('emp', 'ws1', version);
  -- bulk load here
  dbms_wm.CommitBulkLoading('emp', 'DISCARDS');
EXCEPTION
  WHEN OTHERS THEN
    dbms_wm.RollbackBulkLoading('emp');
END;
/
 
BEGINDDL
Start DDL For A Specified Table dbms_wm.BeginDDL(table_name IN VARCHAR2);
exec dbms_wm.BeginDDL('emp');
 
BEGINRESOLVE
Start Conflict Resolution Session dbms_wm.BeginResolve(workspace IN VARCHAR2);
See Workspace Manager Demo Below
 
CHANGEWORKSPACETYPE
Changes a workspace from not continually refreshed to continually refreshed dbms_wm.ChangeWorkspaceType(
workspace      IN VARCHAR2,
workspace_type IN VARCHAR2 DEFAULT dbms_wm.cr_workspace_type,
auto_commit    IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.ChangeWorkspaceType('ws3');
 
COMMITBULKLOADING

Ends the bulk loading process for a version-enabled table by committing the bulk load changes
dbms_wm.CommitBulkLoading(
table_name           IN VARCHAR2,
discards_table       IN VARCHAR2,
check_for_duplicates IN BOOLEAN DEFAULT TRUE,
enforceUCFlag        IN BOOLEAN DEFAULT TRUE,
enforceRICFlag       IN BOOLEAN DEFAULT TRUE,
ignore_last_error    IN BOOLEAN DEFAULT FALSE,
single_transaction   IN BOOLEAN DEFAULT FALSE);
See BeginBulkLoading
 
COMMITDDL

Commit DDL Session Changes
dbms_wm.CommitDDL(
table_name                 IN VARCHAR2,
ignore_last_error          IN BOOLEAN DEFAULT FALSE,
enforce_unique_constraints IN BOOLEAN DEFAULT FALSE,
enforce_RICs               IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.BeginDDL('EMP_TABLE');

ALTER TABLE emp ADD (emp_comments VARCHAR2(100));

exec dbms_wm.CommitDDL('EMP_TABLE');
 
COMMITRESOLVE
End Conflict Resolution Session And Save Workspace Changes dbms_wm.CommitResolve(workspace IN VARCHAR2);
See Workspace Manager Demo Below
 
COMPRESSWORKSPACE

Delete Removable Savepoints And Minimizes Workspace Manager Metadata Structures

Overload 1
dbms_wm.CompressWorkspace(
workspace                  IN VARCHAR2,
firstSP                    IN VARCHAR2 DEFAULT NULL,
secondSP                   IN VARCHAR2 DEFAULT NULL,
auto_commit                IN BOOLEAN DEFAULT TRUE,
commit_in_batches          IN BOOLEAN DEFAULT FALSE,
batch_size                 IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE',
remove_latest_deleted_rows IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.CompressWorkspace('ws1');

Overload 2
dbms_wm.CompressWorkspace(
workspace                  IN VARCHAR2,
compress_view_wo_overwrite IN BOOLEAN

firstSP                    IN VARCHAR2 DEFAULT NULL,
secondSP                   IN VARCHAR2 DEFAULT NULL,
auto_commit                IN BOOLEAN DEFAULT TRUE,
commit_in_batches          IN BOOLEAN DEFAULT FALSE,
batch_size                 IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE',
remove_latest_deleted_rows IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.CompressWorkspace('ws1', TRUE);
 
COMPRESSWORKSPACETREE
Deletes removable savepoints in a workspace and all its descendant workspaces dbms_wm.CompressWorkspaceTree(
workspace                  IN VARCHAR2,
compress_view_wo_overwrite IN  BOOLEAN DEFAULT FALSE,
auto_commit                IN BOOLEAN  DEFAULT TRUE,
commit_in_batches          IN BOOLEAN  DEFAULT FALSE,
batch_size                 IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE',
remove_latest_deleted_rows IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.CompressWorkspaceTree('ws1');
 
COPYFORUPDATE

Allows LOB columns in version-enabled tables to be modified
dbms_wm.CopyForUpdate(
table_name   IN VARCHAR2,
where_clause IN VARCHAR2 DEFAULT '');
/*
This procedure copies the LOB columns if necessary, that is,
if the row with doc_id = 1 has not been versioned in the current version
*/


DECLARE
 lob_var CLOB;
BEGIN
  dbms_wm.CopyForUpdate('table1', 'doc_id = 1');

  SELECT source_clob
  INTO clob_var
  FROM table1
  WHERE doc_id = 1 FOR UPDATE;

  dbms_lob.write(clob_var, <amount>, <offset>, buff);
END;
/
 
CREATEFASTLIVE
Undocumented dbms_wm.CreateFastLive(table_name IN VARCHAR2);
TBD
 
CREATESAVEPOINT
Create a workspace savepoint dbms_wm.CreateSavepoint(
workspace      IN VARCHAR2,
savepoint_name IN VARCHAR2,
description    IN VARCHAR2 DEFAULT NULL,
auto_commit    IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
CREATEWORKSPACE
Creates A New Workspace

Overload 1
dbms_wm.CreateWorkspace(
workspace   IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
Overload 2 dbms_wm.CreateWorkspace(
workspace   IN VARCHAR2,
isrefreshed IN BOOLEAN,
description IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
DELETE_TOPO_GEOMETRY_LAYER
Deletes a topology geometry layer from a topology dbms_wm.delete_topo_geometry_layer(
topology    IN VARCHAR2,
table_name  IN VARCHAR2,
column_name IN VARCHAR2);
exec dbms_wm.Delete_Topo_Geometry_Layer('CITY_DATA', 'LAND_PARCELS', 'FEATURE');
 
DELETESAVEPOINT
Deletes A Savepoint And Associated Rows In   Version-enabled Tables dbms_wm.DeleteSavepoint(
workspace                  IN VARCHAR2,
savepoint_name             IN VARCHAR2,
compress_view_wo_overwrite IN BOOLEAN DEFAULT FALSE,
auto_commit                IN BOOLEAN DEFAULT TRUE,
commit_in_batches          IN BOOLEAN DEFAULT FALSE,
batch_size                 IN VARCHAR2 DEFAULT 'PRIMARY_KEY_RANGE');
See Workspace Manager Demo Below
 
DISABLEREPLICATIONSUPPORT
Disables replication support dbms_wm.DisableReplicationSupport;
exec dbms_wm.DisableReplicationSupport;
 
DISABLEVERSIONING
Deletes all support structures that were created to enable the table to support versioned rows dbms_wm.DisableVersioning(
table_name        IN VARCHAR2,
force             IN BOOLEAN DEFAULT FALSE,
ignore_last_error IN BOOLEAN DEFAULT FALSE,
isTopology        IN BOOLEAN DEFAULT FALSE,
keepWMValid       IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
DISABLEVERSIONING_REPLN
Disable versioning replication

Undocumented
dbms_wm.DisbleVersioning_Repln(
table_name          IN VARCHAR2,
force               IN VARCHAR2,
ignore_last_error_v IN VARCHAR2,
islocalsite         IN VARCHAR2,
hasadminrole        IN BOOLEAN,
istopology          IN BOOLEAN,
keepwmvalid         IN BOOLEAN);
TBD
 
DROPFASTLIVE
Undocumented dbms_wm.DropFastLive(table_name IN VARCHAR2);
TBD
 
DROPREPLICATIONSUPPORT
Deletes replication support objects that had been created by the GenerateReplicationSupport Procedure dbms_wm.DropReplicationSupport;
exec dbms_wm.DropReplicationSupport;
 
ENABLEREPLICATIONSUPPORT (new in 11g?)
? dbms_wm.EnableReplicationSupport;
exec dbms_wm.EnableReplicationSupport;
 
ENABLEVERSIONING (new 11g parameter)

Version-enables a table, creating the objects required to support multiple versions of rows
dbms_wm.EnableVersioning(
table_name     IN VARCHAR2,
hist           IN VARCHAR2 DEFAULT 'NONE',
isTopology     IN BOOLEAN  DEFAULT FALSE,
validTime      IN BOOLEAN  DEFAULT FALSE,
undo_space     IN VARCHAR2 DEFAULT NULL,
validtimerange IN wmsys.wm_period);

CREATE OR REPLACE TYPE wmsys.wm_period AS OBJECT (
validfrom TIMESTAMP WITH TIME ZONE,
validtill TIMESTAMP WITH TIME ZONE)
See Workspace Manager Demo Below
 
ENABLEVERSIONING_REPLN (new 11g parameter)

Enable versioning replication

Undocumented
dbms_wm.EnableVersioning_Repln(
table_name     IN VARCHAR2,
hist           IN VARCHAR2,
islocalsite    IN VARCHAR2,
hasadminrole   IN BOOLEAN,
istopology     IN BOOLEAN,
validtime      IN BOOLEAN,
validtimerange IN VARCHAR2,
undo_space     IN VARCHAR2);
TBD
 
EXPORT

Exports data from a version-enabled table (all rows, or as limited by any combination of several parameters) to a staging table
dbms_wm.export(
table_name              IN VARCHAR2,
staging_table           IN VARCHAR2,
workspace               IN VARCHAR2,
where_clause            IN VARCHAR2 DEFAULT NULL,
export_scope            IN VARCHAR2 DEFAULT
  dbms_wm.export_modified_data_only,
after_savepoint_name    IN VARCHAR2 DEFAULT NULL,
as_of_savepoint_name    IN VARCHAR2 DEFAULT NULL,
after_instant           IN DATE DEFAULT NULL,
as_of_instant           IN DATE DEFAULT NULL,
versioned_db            IN BOOLEAN DEFAULT TRUE,
overwrite_existing_data IN BOOLEAN DEFAULT FALSE,
auto_commit             IN BOOLEAN DEFAULT TRUE);
TBD
 
FINDRICSET
Finds tables that need to be version-enabled along with a specified table, due to referential integrity constraint relationships -- if the result table does not exist it is created.

dbms_wm.FindRicSet(table_name IN VARCHAR2, result_table IN VARCHAR2);
See Workspace Manager Demo Below
 
FREEZEWORKSPACE

Freezes A Workspace Restricting Access And Changes

Overload 1
dbms_wm.FreezeWorkspace(
workspace        IN VARCHAR2,
session_duration IN BOOLEAN,
freezemode       IN VARCHAR2 DEFAULT 'NO_ACCESS',
freezewriter     IN VARCHAR2 DEFAULT NULL,
force            IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.FreezeWorkspace('ws1', TRUE);

Overload 2
dbms_wm.FreezeWorkspace(
workspace    IN VARCHAR2,
freezemode   IN VARCHAR2 DEFAULT 'NO_ACCESS',
freezewriter IN VARCHAR2 DEFAULT NULL,
force        IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.FreezeWorkspace('ws1');
 
GENERATEREPLICATIONSUPPORT
Creates necessary structures for multimaster replication of Workspace Manager objects, and starts the master activity for the newly created master group dbms_wm.GenerateReplicationSupport(
mastersites      IN VARCHAR2,
groupname        IN VARCHAR2,
groupdescription IN VARCHAR2 DEFAULT 'Replication Group for OWM');
exec dbms_wm.GenerateReplicationSupport(
'BAKUP-SITE1.PSOUG.ORG, BAKUP-SITE2.PSOUG.ORG', 'OWM-GROUP', 
'OWM PSOUG Replication');
 
GETBULKLOADVERSION

Returns a version number to be specified in the call to the BeginBulkLoading procedure and in the SQL*Loader control file
dbms_wm.GetBulkLoadVersion(
workspace     IN VARCHAR2,
savepoint_var IN DEFAULT LATEST)
RETURN INTEGER;
DECLARE
 blv PLS_INTEGER;
BEGIN
  SELECT dbms_wm.GetBulkLoadVersion('ws1')
  INTO blv
  FROM dual;

  dbms_wm.BeginBulkLoading('emp', 'ws1', blv);
END;
/
 
GETCONFLICTWORKSPACE
Returns the name of the workspace on which the session has performed the SetConflictWorkspace Procedure dbms_wm.GetConflictWorkspace RETURN VARCHAR2;
SELECT dbms_wm.GetConflictWorkspace FROM dual;
 
GETDIFFVERSIONS
Returns  (workspace, savepoint) pairs on which the session has performed the SetDiffVersions Procedure operation dbms_wm.GetDiffVersions RETURN VARCHAR2;
SELECT dbms_wm.GetDiffVersions FROM dual;
 
GETLOCKMODE
Returns current session locking mode dbms_wm.GetLockMode RETURN VARCHAR2;
See Workspace Manager Demo Below
 
GETLTLOCKSTR

Returns current locking string
dbms_wm.GetLTLockStr(
curlock    IN VARCHAR2,
curnextver IN VARCHAR2,
state_name IN VARCHAR2,
lock_mode  IN VARCHAR2,
curversion IN NUMBER)
RETURN VARCHAR2;
TBD
 
GETMULTIWORKSPACES
Returns the names of workspaces visible in the multiworkspace views for version-enabled tables dbms_wm.GetMultiWorkspaces RETURN VARCHAR2;
SELECT dbms_wm.GetMultiWorkspaces FROM dual;
 
GETOPCONTEXT
Returns the context of the current operation for the current session dbms_wm.GetOpContext RETURN VARCHAR2;
SELECT dbms_wm.GetOpContext FROM dual;
 
GETPHYSICALTABLENAME
Returns the name (<table_name>_LT form) of the physical table for a version-enabled table dbms_wm.GetPhysicalTableName(
table_owner IN VARCHAR2,
table_name  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_wm.GetPhysicalTableName('LTDEMO', 'emp') FROM dual;
 
GETPRIVS
Returns a comma-delimited list of current user privs for the specified workspace dbms_wm.GetPrivs(workspace IN VARCHAR2) RETURN VARCHAR2;
See Workspace Manager Demo Below
 
GETSESSIONINFO
Retrieves information about the current workspace and session context. dbms_wm.GetSessionInfo(
workspace    OUT VARCHAR2,
context      OUT VARCHAR2,
context_type OUT VARCHAR2);
See Workspace Manager Demo Below
 
GETSID
Undocumented dbms_wm.GetSID RETURN VARCHAR2;
SELECT dbms_wm.getsid FROM dual;
 
GETSYSTEMPARAMETER

Returns the value of a WM system parameter
dbms_wm.GetSystemParameter(name IN VARCHAR2) RETURN VARCHAR2;
Name Values
ALLOW_CAPTURE_EVENTS
ALLOW_MULTI_PARENT_WORKSPACES
ALLOW_NESTED_TABLE_COLUMNS
CR_WORKSPACE_MODE
FIRE_TRIGGERS_FOR_NONDML_EVENTS
NONCR_WORKSPACE_MODE
SELECT dbms_wm.GetSystemParameter('ALLOW_CAPTURE_EVENTS')
FROM dual;
 
GETVALIDFROM
Returns the ValidFrom attribute of the current session valid time dbms_wm.GetValidFrom RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_wm.GetValidFrom FROM dual;
 
GETVALIDTILL
Returns the ValidTill attribute of the current session valid time dbms_wm.GetValidTill RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_wm.GetValidTill FROM dual;
 
GETVERSION
Undocumented dbms_wm.GetVersion RETURN VARCHAR2;
See Workspace Manager Demo Below
 
GETWMMETADATASPACE
Returns the number of bytes currently used to store the Workspace Manager metadata dbms_wm.GetWMMetaDataSpace RETURN NUMBER
SELECT dbms_wm.GetWMMetaDataSpace FROM dual;
 
GETWORKSPACE
Returns the current workspace for the session dbms_wm.GetWorkspace RETURN VARCHAR2;
See Workspace Manager Demo Below
 
GOTODATE (new 11g overload?)
Goes to a point at or near the specified date and time in the current workspace

Overload 1
dbms_wm.GotoDate(
in_date  IN VARCHAR2);
exec dbms_wm.GotoDate('12-JAN-09', 'DD-MON-RR');
Overload 2 dbms_wm.GotoDate(
in_date  IN VARCHAR2,
fmt      IN VARCHAR2 DEFAULT 'mmddyyyyhh24miss',
nlsparam IN VARCHAR2 DEFAULT NULL,
tsWtz    IN BOOLEAN  DEFAULT FALSE);
exec dbms_wm.GotoDate('12-JAN-08', 'DD-MON-RR');
 
GOTOSAVEPOINT
Goes to the specified savepoint in the current workspace. dbms_wm.GotoSavePoint(savepoint_name IN VARCHAR2 DEFAULT 'LATEST');
exec dbms_wm.GotoSavepoint('SP1');
 
GOTOWORKSPACE
Moves the current session to the specified workspace. dbms_wm.GotoWorkspace(workspace IN VARCHAR2);
See Workspace Manager Demo Below
 
GRANTGRAPHPRIV

Grants privileges on multiparent graph workspaces to users and roles
dbms_wm.GrantGraphPriv(
priv_types     IN VARCHAR2,
leaf_workspace IN VARCHAR2,
grantee        IN VARCHAR2,
node_types     IN VARCHAR2 DEFAULT '(''R'',''I'',''L'')',
grant_option   IN VARCHAR2 DEFAULT 'NO',
auto_commit    IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.GrantGraphPriv('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith', 'YES');
 
GRANTPRIVSONPOLICY (new in 11g?)

?
dbms_wm.GrantPrivsOnPolicy(policy_name IN VARCHAR2);
TBD
 
GRANTPRIVSTOREPADMINONSYSPKGS
Undocumented dbms_wm.GrantPrivsToRepAdminOnSysPkgs;
exec dbms_wm.GrantPrivsToRepAdminOnSysPkgs;
 
GRANTSYSTEMPRIV
Grants system-level privileges (not restricted to a particular workspace) to users and roles dbms_wm.GrantSystemPriv(
priv_types   IN VARCHAR2,
grantee      IN VARCHAR2,
grant_option IN VARCHAR2 DEFAULT 'NO',
auto_commit  IN BOOLEAN  DEFAULT TRUE);
See Workspace Manager Demo Below
 
GRANTWORKSPACEPRIV
Grants workspace-level privileges to users and roles dbms_wm.GrantWorkspacePriv(
priv_types   IN VARCHAR2,
workspace    IN VARCHAR2,
grantee      IN VARCHAR2,
grant_option IN VARCHAR2 DEFAULT 'NO',
auto_commit  IN BOOLEAN  DEFAULT TRUE);
exec dbms_wm.GrantWorkspacePriv('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith', 'YES');
 
IMPORT

Imports data from a staging table (all rows, or as limited by any combination of several parameters) into a version-enabled table in a specified workspace
dbms_wm.import(
staging_table  IN VARCHAR2,
to_table       IN VARCHAR2,
to_workspace   IN VARCHAR2,
from_workspace IN VARCHAR2 DEFAULT NULL,
where_clause   IN VARCHAR2 DEFAULT NULL,
import_scope   IN VARCHAR2 DEFAULT dbms_wm.import_all_data,
ancestor_savepoint_workspace IN VARCHAR2 DEFAULT NULL,
ancestor_savepoint_name      IN VARCHAR2 DEFAULT NULL,
apply_locks    IN BOOLEAN DEFAULT FALSE,
enforceUCFlag  IN BOOLEAN DEFAULT TRUE,
enforceRICFlag IN BOOLEAN DEFAULT TRUE,
auto_commit    IN BOOLEAN DEFAULT TRUE);
TBD
 
ISWORKSPACEOCCUPIED
Checks whether or not a workspace has any active sessions dbms_wm.IsWorkspaceOccupied(workspace IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_wm.IsWorkspaceOccupied('ws1') FROM dual;
 
LOCKROWS

Controls access to versioned rows in a specified table and to corresponding rows in the parent workspace
dbms_wm.LockRows(
workspace    IN VARCHAR2,
table_name   IN VARCHAR2,
where_clause IN VARCHAR2 DEFAULT '',
lock_mode    IN VARCHAR2 DEFAULT 'E',
Xmin         IN NUMBER DEFAULT NULL,
Ymin         IN NUMBER DEFAULT NULL,
Xmax         IN NUMBER DEFAULT NULL,
Ymax         IN NUMBER DEFAULT NULL);
exec dbms_wm.LockRows('NEWWORKSPACE', 'employees', 'last_name = ''Smith''');
 
MAX_TIME
Undocumented dbms_wm.Max_Time RETURN TIMESTAMP WITH TIMEZONE;
SELECT dbms_wm.max_time FROM dual;
 
MERGETABLE

Applies changes to a table (all rows or as specified in the WHERE clause) in a workspace to its parent workspace
dbms_wm.MergeTable(
workspace        IN VARCHAR2,
table_id         IN VARCHAR2,
where_clause     IN VARCHAR2 DEFAULT '',
create_savepoint IN BOOLEAN DEFAULT FALSE,
remove_data      IN BOOLEAN DEFAULT FALSE,
auto_commit      IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.MergeTable('NEWWORKSPACE', 'user3.emp', 'last_name = ''Smith''');
 
MERGEWORKSPACE
Applies all changes in a workspace to its parent workspace, and optionally removes the workspace dbms_wm.MergeWorkspace(
workspace        IN VARCHAR2,
create_savepoint IN BOOLEAN DEFAULT FALSE,
remove_workspace IN BOOLEAN DEFAULT FALSE,
auto_commit      IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
MIN_TIME
Undocumented dbms_wm.Min_Time RETURN TIMESTAMP WITH TIMEZONE;
SELECT dbms_wm.min_time FROM dual;
 
MOVE_PROC
Moves the Workspace Manager metadata to a specified tablespace dbms_wm.MoveProc(dest_tablespace IN VARCHAR2 DEFAULT 'SYSAUX');
exec dbms_wm.MoveProc('UWDATA');
got this far
RECOVERALLMIGRATINGTABLES
Attempts to complete the migration process on all tables that were left in an inconsistent state after the WM migration procedure failed dbms_wm.RecoverAllMigratingTables(
ignore_last_error IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.RecoverAllMigratingTables(TRUE);
 
RECOVERFROMDROPPEDUSER
? dbms_wm.RecoverFromDroppedUser(
ignore_last_error IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.RecoverFromDroppedUser;
 
RECOVERMIGRATINGTABLE
Attempts to complete the migration process on a table that was left in an inconsistent state after the WM migration procedure failed dbms_wm.RecoverMigratingTable(
table_name        IN VARCHAR2,
ignore_last_error IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.RecoverMigratingTable('TEST_TAB');
 
REFRESHTABLE
Applies to a workspace all changes made to a table (all rows or as specified in the WHERE clause) in its parent workspace dbms_wm.RefreshTable(
workspace    IN VARCHAR2,
table_id     IN VARCHAR2,
where_clause IN VARCHAR2 DEFAULT '',
auto_commit  IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RefreshTable('ws1', 'emp', 'last_name=''Smith''');
 
REFRESHWORKSPACE
Applies to a workspace all changes made in its parent workspace dbms_wm.RefreshWorkspace(
workspace   IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE,
copy_data   IN BOOLEAN DEFAULT FALSE);
exec dbms_wm.RefreshWorkspace('ws1');
 
RELOCATEWRITERSITE
Makes one of the nonwriter sites the new writer site in a  Workspace Manager replication environment dbms_wm.RelocateWriterSite(
newwritersite          IN VARCHAR2,
oldwritersiteavailable IN BOOLEAN);
exec dbms_wm.RelocateWriterSite('BAKUP-SITE1.PSOUG.ORG', TRUE);
 
REMOVEASPARENTWORKSPACE
Removes a workspace as a parent workspace in a multiparent workspace environment dbms_wm.RemoveAsParentWorkspace(
mp_leafworkspace IN VARCHAR2,
parent_workspace IN VARCHAR2,
auto_commit      IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RemoveAsParentWorkspace('ws0', 'ws2');
 
REMOVEUSERDEFINEDHINT (new in 11g)
? dbms_wm.RemoveUserDefinedHint(
hint_id  IN NUMBER,
table_id IN VARCHAR2);
TBD
 
REMOVEWORKSPACE
Discards all row versions associated with a workspace and deletes the workspace dbms_wm.RemoveWorkspace(
workspace   IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
REMOVEWORKSPACETREE
Discards all row versions associated with a workspace and its descendant workspaces, and deletes the affected workspace dbms_wm.RemoveWorkspaceTree(
workspace   IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
RESOLVECONFLICTS

Resolves conflicts between workspaces
dbms_wm_ResolveConflicts(
workspace    IN VARCHAR2,
table_name   IN VARCHAR2,
where_clause IN VARCHAR2,
keep         IN VARCHAR2);
See Workspace Manager Demo Below
 
REVOKEGRAPHPRIV

Revokes privileges on multiparent graph workspaces from users and roles for a specified leaf workspace
dbms_wm.RevokeGraphPriv(
priv_types     IN VARCHAR2,
leaf_workspace IN VARCHAR2,
grantee        IN VARCHAR2.
node_types     IN VARCHAR2 DEFAULT '(''R'',''I'',''L'')',
auto_commit    IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RevokeWorkspacePriv('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith');
 
REVOKESYSTEMPRIV

Revokes system-level privileges from users and roles
dbms_wm.RevokeSystemPriv(
priv_types  IN VARCHAR2,
grantee     IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
REVOKEWORKSPACEPRIV

Revokes workspace-level privileges from users and roles for a specified workspace
DBMS_WM.RevokeWorkspacePriv(
priv_types  IN VARCHAR2,
workspace   IN VARCHAR2,
grantee     IN VARCHAR2.
auto_commit IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RevokeWorkspacePriv('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith');
 
ROLLBACKBULKLOADING
Rolls back changes made to a version-enabled table during a bulk load operation dbms_wm.RollbackBulkLoading(
table_name        IN VARCHAR2,
ignore_last_error IN BOOLEAN DEFAULT FALSE);
See BeginBulkLoading
 
ROLLBACKDDL
Rolls back DDL changes made during a DDL session for a specified table, and ends the DDL session dbms_wm.RollbackDDL(table_name IN VARCHAR2);
exec dbms_wm.RollbackDDL('emp');
 
ROLLBACKRESOLVE
Quit conflict resolution session,   discard changes since the BeginResolve was started dbms_wm.RollbackResolve(workspace IN VARCHAR2);
exec dbms_wm.RollbackResolve('ws1');
 
ROLLBACKTABLE

Discard workspace changes to   specified table (all rows or as specified in the WHERE clause)
dbms_wm.RollbackTable(
workspace    IN VARCHAR2,
table_id     IN VARCHAR2,
sp_name      IN VARCHAR2 DEFAULT '',
where_clause IN VARCHAR2 DEFAULT '',
remove_locks IN BOOLEAN DEFAULT TRUE,
auto_commit  IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RollbackTable('ws1', 'emp');
 
ROLLBACKTOSP
Discards all data changes made in the workspace to version-enabled tables since the specified savepoint dbms_wm.RollbackToSP(
workspace      IN VARCHAR2,
savepoint_name IN VARCHAR2,
auto_commit    IN BOOLEAN DEFAULT TRUE);
See Workspace Manager Demo Below
 
ROLLBACKWORKSPACE
Discards all data changes made in the workspace to version-enabled tables dbms_wm.RollbackWorkspace(
workspace   IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.RollbackWorkspace('ws1');
 
SETCAPTUREEVENT
Enables or disables the capture of all Workspace Manager events or events of a specific type dbms_wm.SetCaptureEvent(
event_name IN VARCHAR2,
capture    IN VARCHAR2 DEFAULT 'ON');
exec dbms_wm.SetCaptureEvent('WORKSPACE_VERSION');
 
SETCOMPRESSWORKSPACE
Creates rows in the WM_COMPRESSIBLE_TABLES metadata view with information about version-enabled tables that need to be compressed if workspace compression operations are performed dbms_wm.SetCompressWorkspace(
workspace IN VARCHAR2,
firstSP   IN VARCHAR2 DEFAULT NULL,
secondSP  IN VARCHAR2 DEFAULT NULL);
exec dbms_wm.SetCompressWorkspace('ws1');
 
SETCONFLICTWORKSPACE
Determines whether or not conflicts exist between a workspace and its parent dbms_wm.SetConflictWorkspace(workspace IN VARCHAR2);
exec dbms_wm.SetConflictWorkspace('ws1');
 
SETDIFFVERSIONS
Finds differences in values in version-enabled tables for two savepoints and their common ancestor (base). It modifies the contents of the differences views that describe these differences

Overload 1
dbms_wm.SetDiffVersions(
workspace1 IN VARCHAR2,
savepoint1 IN VARCHAR2,
workspace2 IN VARCHAR2,
savepoint2 IN VARCHAR2);
exec dbms_wm.SetDiffVersions('ws1', 'SP1', 'ws7', 'SP3');
Overload 2 dbms_wm.SetDiffVersions(
workspace1 IN VARCHAR2,
workspace2 IN VARCHAR2);
exec dbms_wm.SetDiffVersions('ws1', 'ws7');
 
SETLOCKINGOFF
Disables Workspace Manager locking for the current session dbms_wm.SetLockingOff;
exec dbms_wm.SetLockingOff;
 
SETLOCKINGON
Enables Workspace Manager locking for the current session dbms_wm.SetLockingOn(lockmode IN VARCHAR2);
exec dbms_wm.SetLockingOn('E');
 
SETMULTIWORKSPACES
Makes the specified workspace(s) visible in the multiworkspace views for version-enabled tables dbms_wm.SetMultiWorkspaces(workspaces IN VARCHAR2);
exec dbms_wm.SetMultiworkspaces('ws1');
 
SETSYSTEMPARAMETER
Sets the value of a Workspace Manager system parameter dbms_wm.SetSystemParameter(name IN VARCHAR2, value IN VARCHAR2);
exec dbms_wm.SetSystemParameter('ALLOW_CAPTURE_EVENTS', 'ON');
 
SETTRIGGEREVENTS
Enables the execution of a trigger for a specified set of triggering events dbms_wm.SetSystemParameter(
triggerName   IN VARCHAR2,
triggerEvents IN VARCHAR2);
exec dbms_wm.SetTriggerEvents('UWCLASS.Insert and Import', dbms_wm.ddl, || ',' || dbms_wm.table_import);
 
SETVALIDTIME
Sets the session valid time period dbms_wm.SetValidTime(
validFrom IN TIMESTAMP WITH TIME ZONE DEFAULT dbms_wm.current_time,
validTill IN TIMESTAMP WITH TIME ZONE DEFAULT dbms_wm.until_changed);
exec dbms_wm.SetValidTime(TO_DATE('01-05-2005', 'MM-DD-YYYY'), TO_DATE('30-06-2005', 'MM-DD-YYYY'));
 
SETVALIDTIMEFILTEROFF (new in 11g???)
Removes a time filter for the current session dbms_wm.SetValidTimeFilterOff;
exec dbms_wm.SetValidTimeFilterOff;
 
SETVALIDTIMEFILTERON
Sets a valid time filter for the current session dbms_wm.SetValidTimeFilterOn(
filtertime IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);
exec dbms_wm.SetValidTimeFilterOn(TRUNC(SYSDATE-10));
 
SETWMVALIDUPDATEMODEOFF
Disables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support dbms_wm.SetWMValidUpdateModeOff;
exec dbms_wm.SetWMValidUpdateModeOff;
 
SETWMVALIDUPDATEMODEON
Enables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support dbms_wm.SetWMValidUpdateModeOn;
exec dbms_wm.SetWMValidUpdateModeOn;
 
SETWOOVERWRITEOFF
Disables the VIEW_WO_OVERWRITE history option that had been enabled by the EnableVersioning Procedure or SetWoOverwriteON Procedure, changing the option to VIEW_W_OVERWRITE (with overwrite). dbms_wm.SetWOOverwriteOff;
exec dbms_wm.SetWOOverwriteOff;
 
SETWOOVERWRITEON
Enables the VIEW_WO_OVERWRITE history option that had been disabled by SetWoOverwriteOff dbms_wm.SetWoOverwriteOn;
exec dbms_wm.SetWoOverwriteOn;
 
SETWORKSPACELOCKMODEOFF
Disables Workspace Manager locking for the specified workspace dbms_wm.SetWorkspaceLockModeOFF(
workspace   IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.SetWorkspaceLockModeOff('ws1');
 
SETWORKSPACELOCKMODEON
Enables Workspace Manager locking for the specified workspace dbms_wm.SetWorkspaceLockModeON(
workspace   IN VARCHAR2,
lockmode    IN VARCHAR2,
override    IN BOOLEAN DEFAULT FALSE,
auto_commit IN BOOLEAN DEFAULT TRUE);
exec dbms_wm.SetWorkspaceLockModeOn('ws1', 'E');
 
SYNCFASTLIVE (new in 11g?)
? dbms_wm.SyncFastLive(table_name IN VARCHAR2);
TBD
 
SYNCHRONIZESITE
Brings the local site (the old writer site) up to date in the Workspace Manager replication environment after the writer site was moved using the RelocateWriterSite dbms_wm.SynchronizeSite(NewWriterSite IN VARCHAR2);
exec dbms_wm.SynchronizeSite('BAKUP-SITE1.PSOUG.ORG');
 
UNFREEZEWORKSPACE
Enables access and changes to a workspace, reversing the effect of  FreezeWorkspace dbms_wm.UnfreezeWorkspace(workspace IN VARCHAR2);
exec dbms_wm.UnfreezeWorkspace('ws1');
 
UNLOCKROWS

Enables access to versioned rows in a specified table and to corresponding rows in the parent workspace
dbms_wm.UnlockRows(
workspace    IN VARCHAR2,
table_name   IN VARCHAR2,
where_clause IN VARCHAR2 DEFAULT '',
all_or_user  IN VARCHAR2 DEFAULT 'USER',
lock_mode    IN VARCHAR2 DEFAULT 'ES',
Xmin         IN NUMBER DEFAULT NULL,
Ymin         IN NUMBER DEFAULT NULL,
Xmax         IN NUMBER DEFAULT NULL,
Ymax         IN NUMBER DEFAULT NULL);
exec dbms_wm.UnlockRows('emp','ws1','last_name=''Smith''');
 
UNTIL_CHANGED (new in 11g)
? dbms_wm.Until_Changed RETURN TIMESTAMP WITH TIMEZONE;
SELECT dbms_wm.until_changed FROM dual;
 
USERDEFAULTVALUESFORNULLS
Determines whether or not Workspace Manager, for the current session, uses the default value for a column when the user specifies a null value for the column in an insert operation on a version-enabled table dbms_wm.UserDefaultValuesForNulls(mode_var IN VARCHAR2);
exec dbms_wm.UserDefaultValuesForNulls('ON');
 
Workspace Manager Demo

Setup
conn / AS SYSDBA

CREATE USER ltdemo
IDENTIFIED BY ltdemo
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA 20M ON uwdata;

GRANT create session TO ltdemo;
GRANT create table TO ltdemo;
GRANT create procedure TO ltdemo; -- not required for LT
GRANT wm_admin_role TO ltdemo;
BEGIN
  dbms_wm.GrantSystemPriv('ACCESS_ANY_WORKSPACE, ' || 
  'CREATE_ANY_WORKSPACE, FREEZE_ANY_WORKSPACE, ' ||
  'MERGE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ' ||
  'ROLLBACK_ANY_WORKSPACE', 'LTDEMO', 'YES');
END;
/

conn scott/tiger

GRANT select ON dept TO ltdemo;
GRANT select ON emp TO ltdemo;

conn ltdemo/ltdemo

CREATE TABLE dept AS
SELECT * FROM scott.dept;

ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY (deptno)
USING INDEX
PCTFREE 0;

CREATE TABLE emp AS
SELECT * FROM scott.emp;

ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY (empno)
USING INDEX
PCTFREE 0;

ALTER TABLE emp
ADD CONSTRAINT fk_emp_deptno
FOREIGN KEY (deptno)
REFERENCES dept(deptno);

ALTER TABLE emp
ADD CONSTRAINT fk_emp_emp
FOREIGN KEY (mgr)
REFERENCES emp(empno);

col object_name format a30

SELECT object_name, object_type
FROM user_objects;

SELECT table_name, constraint_name, constraint_type
FROM user_constraints;

SELECT * FROM dept;

SELECT * FROM emp;

CREATE OR REPLACE PROCEDURE whereami IS
 current_workspace    VARCHAR2(30);
 current_context      VARCHAR2(30);
 current_context_type VARCHAR2(30);
BEGIN
  dbms_wm.GetSessionInfo(current_workspace,
  current_context, current_context_type);

  dbms_output.put_line('Session currently in workspace: '
  || current_workspace);

  dbms_output.put_line('Session context is: ' ||
  current_context);

  dbms_output.put_line('Session context is on: ' ||
  current_context_type);
END whereami;
/

set serveroutput on

exec whereami


Enable Versioning
BEGIN
  dbms_wm.EnableVersioning('emp', 'VIEW_WO_OVERWRITE');
  dbms_wm.EnableVersioning('dept', 'VIEW_WO_OVERWRITE');
END;
/

SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;

SELECT object_name, object_type
FROM user_objects;

SELECT table_name, constraint_type, constraint_type
FROM user_constraints;

desc user_wm_versioned_tables

col history format a20

SELECT table_name, state, history
FROM user_wm_versioned_tables;

SELECT dbms_wm.GetPhysicalTableName('LTDEMO', 'dept')
FROM dual;

SELECT dbms_wm.GetPhysicalTableName('LTDEMO', 'emp')
FROM dual;

desc ricset -- does not exist

-- created if does not exist
exec dbms_wm.FindRicSet('emp', 'ricset');

desc ricset

SELECT * FROM ricset;

exec dbms_wm.FindRicSet('dept', 'ricset');

SELECT * FROM ricset;

Create Workspaces
-- create two workspaces
BEGIN
  dbms_wm.CreateWorkspace('ws1');
  dbms_wm.CreateWorkspace('ws2');
END;
/

desc user_workspaces

SELECT owner, workspace, parent_workspace
FROM user_workspaces;

-- additional information
SELECT dbms_wm.getversion
FROM dual;

set serveroutput on
DECLARE
 ws       VARCHAR2(30);
 ctx      VARCHAR2(30);
 ctx_type VARCHAR2(30);
BEGIN
  dbms_wm.GetSessionInfo(ws, ctx, ctx_type);
  dbms_output.put_line('Workspace: ' || ws);
  dbms_output.put_line('Context: ' || ctx);
  dbms_output.put_line('Context Type: ' || ctx_type);
END;
/

SELECT dbms_wm.GetLockMode FROM dual;

SELECT dbms_wm.GetPrivs('ws1') FROM dual;

SELECT dbms_wm.GetPrivs('ws2') FROM dual;

SELECT dbms_wm.GetSystemParameter('ALLOW_CAPTURE_EVENTS')
FROM dual;

SELECT dbms_wm.IsWorkspaceOccupied('ws1')
FROM dual;

SELECT dbms_wm.IsWorkspaceOccupied('ws2')
FROM dual;


Add and Remove Parent Workspace
exec dbms_wm.SetSystemParameter( 'ALLOW_MULTI_PARENT_WORKSPACES', 'ON');

exec dbms_wm.SetSystemParameter('
NONCR_WORKSPACE_MODE', 'PESSIMISTIC_LOCKING');

exec dbms_wm.AddAsParentWorkspace('ws1', 'ws0', TRUE);

-- create the workspace
exec  dbms_wm.CreateWorkspace('ws0');

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

exec dbms_wm.AddAsParentWorkspace('ws1', 'ws0', TRUE);

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

exec dbms_wm.RemoveAsParentWorkspace('ws1', 'ws0');

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

exec dbms_wm.AddAsParentWorkspace('ws2', 'ws0', TRUE);

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

exec dbms_wm.RemoveWorkspaceTree('ws0');

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

-- recreate workspaces
BEGIN
  dbms_wm.CreateWorkspace('ws1');
  dbms_wm.CreateWorkspace('ws2');
END;
/

SELECT workspace, parent_workspace, mp_root_workspace
FROM user_workspaces;

col description format a40

SELECT workspace, parent_workspace, description
FROM user_workspaces;

exec dbms_wm.AlterWorkspace('ws2', 'Child Workspace');

SELECT workspace, parent_workspace, description
FROM user_workspaces;

exec dbms_wm.RemoveWorkspaceTree('ws0');

SELECT workspace, parent_workspace, description
FROM user_workspaces;

exec dbms_wm.CreateWorkspace('ws2');

SELECT workspace, parent_workspace
FROM user_workspaces;

exec dbms_wm.SetSystemParameter('
NONCR_WORKSPACE_MODE', 'OPTIMISTIC_LOCKING');

Alter versioned table
desc emp
desc emp_lt

exec dbms_wm.AlterVersionedTable('emp', 'ADD_VALID_TIME');

desc emp
desc emp_lt

Workspace Demo
exec whereami

-- Change to ws1 workspace
exec dbms_wm.GotoWorkspace('ws1');

exec whereami

SELECT ename, sal
FROM emp;

UPDATE emp
SET sal = sal * 1.10;

COMMIT;

SELECT ename, sal
FROM emp;

col workspace format a5

-- view history
SELECT ename, sal, comm, 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 emp_hist
WHERE ename = 'KING';

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

-- perform another mass update setting a commission value
UPDATE emp
SET comm = 0.1 * (GREATEST(0,sal-1000));

COMMIT;

-- Note retired history records
SELECT ename, sal, comm, 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 emp_hist
WHERE ename = 'KING';

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

-- See how LIVE cannot see these changes as yet
exec dbms_wm.GotoWorkspace('LIVE');

exec whereami

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

-- nor can ws2
exec dbms_wm.GotoWorkspace('ws2');

exec whereami

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

-- now as ws2 update a previously updated row
UPDATE emp
SET sal = sal * 1.5
WHERE ename = 'KING';

UPDATE emp
SET comm = 0.1 * (GREATEST(0,sal-1000))
WHERE ename = 'KING';

COMMIT;

-- notice the history for King across rows
SELECT ename, sal, comm, 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 emp_hist
WHERE ename = 'KING';

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

Savepoint Demo
exec dbms_wm.CreateSavepoint('ws2', 'ws2_SP1');

desc user_workspace_savepoints

SELECT savepoint, workspace, implicit, position,
createtime, canrollbackto, removable
FROM user_workspace_savepoints;

exec dbms_wm.DeleteSavepoint('ws2', 'ws2_SP1');

SELECT savepoint, workspace, implicit, position,
createtime, canrollbackto, removable
FROM user_workspace_savepoints;

exec dbms_wm.CreateSavepoint('ws2', 'ws2_SP1');

SELECT  savepoint, description
FROM user_workspace_savepoints;

exec dbms_wm.AlterSavepoint('ws2', 'ws2_SP1', 'This is our first savepoint');

SELECT  savepoint, description
FROM user_workspace_savepoints;

Workspace Management Demo Continued
-- lets try a what-if scenario with a Blake take-over

UPDATE emp
SET mgr = (SELECT empno FROM emp WHERE ename = 'BLAKE')
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');

DELETE FROM emp
WHERE ename = 'KING';

DELETE FROM dept
WHERE deptno = 40;

COMMIT;

-- changes are made and committed ... King is gone
SELECT empno, ename, mgr, sal, comm
FROM emp;

-- as is department 40
SELECT *
FROM dept;

-- but King isn't about to be out there flipping burgers
BEGIN
  dbms_wm.GotoWorkspace('LIVE');
  dbms_wm.RollbackToSP('ws2', 'ws2_SP1');
  dbms_wm.GotoWorkspace('ws2');
END;
/

SELECT ename, sal, comm, 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 emp_hist
WHERE ename = 'KING';

SELECT ename, sal, comm
FROM emp
WHERE ename = 'KING';

Merge Workspaces and Resolve Conflict
-- merge the changes from ws1 into live
exec dbms_wm.GotoWorkspace('LIVE');

-- King is back
SELECT ename, sal, comm
FROM emp;

exec dbms_wm.MergeWorkspace('ws1');

SELECT ename, sal, comm
FROM emp;

-- is there a conflict to resolve
exec dbms_wm.MergeWorkspace('ws2');

-- lets go fix it
exec dbms_wm.GotoWorkspace('ws2');

SELECT *
FROM emp_conf;
BEGIN
  dbms_wm.BeginResolve('ws2');
  dbms_wm.ResolveConflicts('ws2', 'emp','empno=7839',
  'CHILD');
  commit;
  dbms_wm.CommitResolve('ws2');
END;
/

exec dbms_wm.GotoWorkspace('LIVE');

exec dbms_wm.MergeWorkspace('ws2');

SELECT ename, sal, comm
FROM emp;


Workspace Management Clean-up
BEGIN
  dbms_wm.RemoveWorkspace('ws1');
  dbms_wm.RemoveWorkspace('ws2');
  dbms_wm.DisableVersioning('ltdemo.dept');
  dbms_wm.DisableVersioning('ltdemo.emp');
END;
/

SELECT object_name, object_type
FROM user_objects;

conn / AS SYSDBA

BEGIN
  dbms_wm.revokesystempriv('ACCESS_ANY_WORKSPACE, ' || 
  'CREATE_ANY_WORKSPACE, FREEZE_ANY_WORKSPACE, ' ||
  'MERGE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ' ||
  'ROLLBACK_ANY_WORKSPACE', 'LTDEMO');
END;
/

DROP USER ltdemo CASCADE;
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [249 users online]    © 2010 psoug.org