Oracle LT (synonym DBMS_WM)
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Workspace Management (versioning): The ability to create separate logical workspaces for performing DML operations that, in a single workspace, would be logically inconsistent.
AUTHID CURRENT_USER
Constants
Name Data Type Value
IMPORT_ALL_DATA VARCHAR2 Undocumented
Data Types wmsys.dbms_wm.optimistic_locking
Dependencies
ALL_CONSTRAINTS KU$_DDLS OWM_MIG_PKG
ALL_INDEXES LTADM OWM_MP_PKG
ALL_MP_PARENT_WORKSPACES LTAQ OWM_VSCRIPT_PKG
ALL_TABLES LTDDL PLITBLM
ALL_TAB_PRIVS_RECD LTDTRG ROLE_SYS_PRIVS
ALL_USERS LTI ROLE_TAB_PRIVS
ALL_VERSION_HVIEW LTPRIV SESSION_ROLES
ALL_WM_VERSIONED_TABLES LTRIC UD_TRIGS
ALL_WM_VT_ERRORS LTUTIL USER_SYS_PRIVS
ALL_WORKSPACE_SAVEPOINTS LT_CTX_PKG USER_VIEWS
DBA_DATAPUMP_JOBS LT_EXPORT_PKG USER_WM_PRIVS
DBA_ERRORS OWM_ASSERT_PKG USER_WORKSPACES
DBA_WM_VT_ERRORS OWM_BULK_LOAD_PKG WM$VERSIONED_TABLES
DBMS_STANDARD OWM_DDL_PKG WM_DDL_UTIL
DBMS_UTILITY OWM_DML_PKG WM_ERROR
DBMS_WM OWM_DYNSQL_ACCESS WM_INSTALLATION
DUAL OWM_IEXP_PKG WM_PERIOD
KU$_DDL    
Documented Yes
Exceptions
Error Code Reason
ORA-20132 table '<schema_name>.<table_name>' is not version enabled

The following have been found in Oracle scripts and likely represent a class of built-in exception-related constants.

wmsys.wm_error.raiseerror(wmsys.dbms_wm.wm_error_172_no);
wmsys.wm_error.raiseerror(wmsys.dbms_wm.wm_error_192_no, err_msg);
wmsys.wm_error.raiseerror(wmsys.dbms_wm.wm_error_195_no, err_msg_full);
wmsys.wm_error.raiseerror(wmsys.dbms_wm.wm_error_383_no);
First Available 9.2
Roles WM_ADMIN_ROLE
Security Model Owned by WMSYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/owmlts.plb
WM Events
ALL_EVENTS WORKSPACE_COMPRESS WORKSPACE_REFRESH
TABLE_MERGE_W_REMOVE_DATA WORKSPACE_CREATE WORKSPACE_REMOVE
TABLE_MERGE_WO_REMOVE_DATA WORKSPACE_MERGE_W_REMOVE WORKSPACE_ROLLBACK
TABLE_REFRESH WORKSPACE_MERGE_WO_REMOVE WORKSPACE_VERSION
TABLE_ROLLBACK    
WM Lock Modes
Value Description
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. Other users in other workspaces can change the values.
WM System Parameters
ALLOW_CAPTURE_EVENTS ALLOW_NESTED_TABLE_COLUMNS FIRE_TRIGGERS_FOR_NONDML_EVENTS
ALLOW_MULTI_PARENT_WORKSPACES CR_WORKSPACE_MODE NONCR_WORKSPACE_MODE
WM Trigger Events
Event Description
DBMS_WM.DML  
DBMS_WM.TABLE_IMPORT  
DBMS_WM.TABLE_MERGE_W_REMOVE_DATA  
DBMS_WM.TABLE_MERGE_WO_REMOVE_DATA  
DBMS_WM.WORKSPACE_MERGE_W_REMOVE  
DBMS_WM.WORKSPACE_MERGE_WO_REMOVE  
WM Workspace Privileges
ACCESS_ANY_WORKSPACE MERGE_ANY_WORKSPACE ROLLBACK_ANY_WORKSPACE
CREATE_ANY_WORKSPACE REMOVE_ANY_WORKSPACE  
Subprograms
ADDASPARENTWORKSPACE GETPHYSICALTABLENAME REMOVEWORKSPACETREE
ADDUSERDEFINEDHINT GETPRIVS RENAMESAVEPOINT
ADD_TOPO_GEOMETRY_LAYER GETSESSIONINFO RENAMEWORKSPACE
ALTERSAVEPOINT GETSYSTEMPARAMETER RESOLVECONFLICTS
ALTERVERSIONEDTABLE GETVALIDFROM REVOKEGRAPHPRIV
ALTERWORKSPACE GETVALIDTILL REVOKESYSTEMPRIV
BEGINBULKLOADING GETVERSION REVOKEWORKSPACEPRIV
BEGINDDL GETWMMETADATASPACE ROLLBACKBULKLOADING
BEGINRESOLVE GETWORKSPACE ROLLBACKDDL
CHANGEWORKSPACETYPE GOTODATE ROLLBACKRESOLVE
COMMITBULKLOADING GOTOSAVEPOINT ROLLBACKTABLE
COMMITDDL GOTOWORKSPACE ROLLBACKTOSP
COMMITRESOLVE GRANTGRAPHPRIV ROLLBACKWORKSPACE
COMPRESSWORKSPACE GRANTPRIVSONPOLICY SETCAPTUREEVENT
COMPRESSWORKSPACETREE GRANTSYSTEMPRIV SETCOMPRESSWORKSPACE
COPYFORUPDATE GRANTWORKSPACEPRIV SETCONFLICTWORKSPACE
COPYWORKSPACE IMPORT SETDIFFVERSIONS
CREATESAVEPOINT IMPORT_SCHEMAS SETLOCKINGOFF
CREATEWORKSPACE INITIALIZE_AFTER_IMPORT SETLOCKINGON
DELETESAVEPOINT ISWORKSPACEOCCUPIED SETMULTIWORKSPACES
DELETE_TOPO_GEOMETRY_LAYER LOCKROWS SETSYSTEMPARAMETER
DISABLEVERSIONING MAX_TIME SETTRIGGEREVENTS
DML_ MERGETABLE SETVALIDTIME
DROPREPLICATIONSUPPORT MERGEWORKSPACE SETVALIDTIMEFILTEROFF
ENABLEVERSIONING MIN_TIME SETVALIDTIMEFILTERON
EXPORT MOVE_PROC SETWMVALIDUPDATEMODEOFF
EXPORT_SCHEMAS PURGETABLE SETWMVALIDUPDATEMODEON
FINDRICSET RECOVERALLMIGRATINGTABLES SETWOOVERWRITEOFF
FREEZEWORKSPACE RECOVERFROMDROPPEDUSER SETWOOVERWRITEON
GENERATEREPLICATIONSUPPORT RECOVERMIGRATINGTABLE SETWORKSPACELOCKMODEOFF
GETBULKLOADVERSION REFRESHTABLE SETWORKSPACELOCKMODEON
GETCONFLICTWORKSPACE REFRESHWORKSPACE SYNCHRONIZESITE
GETDIFFVERSIONS RELOCATEWRITERSITE UNFREEZEWORKSPACE
GETLOCKMODE REMOVEASPARENTWORKSPACE UNLOCKROWS
GETLTLOCKSTR REMOVEDEFERREDWORKSPACES UNTIL_CHANGED
GETMULTIWORKSPACES REMOVEUSERDEFINEDHINT USEDEFAULTVALUESFORNULLS
GETOPCONTEXT REMOVEWORKSPACE Workspace Management Demos
GETORIGINALDDL    
 
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
Undocumented dbms_wm.addAsParentWorkspace(
hint_id  IN NUMBER,
table_id IN VARCHAR2,
hint     IN VARCHAR2);
TBD
 
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');
 
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 Demo
 
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;
/
 
COPYWORKSPACE
Undocumented dbms_wm.copyWorkspace(
source_workspace IN VARCHAR2,
target_workspace IN VARCHAR2);
SELECT dbms_wm.copyWorkspace('ws1', 'ws2')
 
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.cvreateWorkspace(
workspace   IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN  DEFAULT TRUE);
See Workspace Manager Demo Below
Overload 2 vdbms_wm.createWorkspace(
workspace   IN VARCHAR2,
isrefreshed IN BOOLEAN,
description IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN  DEFAULT TRUE);
See Workspace Manager Demo Below
 
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
 
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');
 
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
 
DML
Undocumented dbms_wm.dml_(
dmlmode IN VARCHAR2,
col     IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
DROPREPLICATIONSUPPORT
Deletes replication support objects that had been created by the GenerateReplicationSupport Procedure dbms_wm.dropReplicationSupport;
exec dbms_wm.dropReplicationSupport;
 
ENABLEVERSIONING
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
 
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
 
EXPORT_SCHEMAS
Creates a dump file containing everything related to Workspace Manager using the Data Pump Export utility dbms_wm.export_schemas(
job_name          IN VARCHAR2,
alt_schema        IN VARCHAR2,
ignore_last_error IN BOOLEAN);
DECLARE
 jname VARCHAR2(128) := 'UW_EXPORT';
 dph   NUMBER;
BEGIN
  n := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, jname, 'COMPATIBLE');
  dbms_datapump.add_file(n, 'uwclass.dmp', 'CTEMP');
  dbms_datapump.add_file(n, 'uwclass.log', 'CTEMP', filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
  dbms_wm.export_schemas(jname);
  dbms_datapump.detach(dph);
EXCEPTION
  WHEN OTHERS THEN
    dbms_datapump.stop_job(dph);
END;
/
 
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.MLIB.ORG, BAKUP-SITE2.MLIB.ORG', 'OWM-GROUP', 'OWM MLIB 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 VARCHAR2 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: See Lock Mode Constants Above 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;
 
GETORIGINALDDL
Undocumented. The object must be version enabled.

Overload 1
dbms_wm.getOriginalDDL(
table_id  IN     VARCHAR2,
ddl_stmts IN OUT sys.ku$_ddls);
TBD
Overload 2 dbms_wm.getOriginalDDL(
table_id IN     VARCHAR2,
ddl_clob IN OUT CLOB);
DECLARE
 c CLOB;
BEGIN
  dbms_wm.getOriginalDDL('TAB$', c);
  dbms_output.put_line(c);
END;
/
DECLARE
*
ERROR at line 1:
ORA-20132: table 'TAB$' is not version enabled
ORA-06512: at "WMSYS.LT", line 6727
ORA-06512: at "WMSYS.LT", line 6687
ORA-06512: at line 4
 
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
 
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
Returns the current Oracle Database version dbms_wm.getVersion RETURN VARCHAR2;
SELECT dbms_wm.getVersion
FROM dual;
 
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
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-18', '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-18', 'DD-MON-RR', NULL, TRUE);
 
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', 'UWCLASS', 'YES');
 
GRANTPRIVSONPOLICY
Undocumented dbms_wm.grantPrivsOnPolicy(policy_name IN VARCHAR2);
TBD
 
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', 'UWCLASS', '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
 
IMPORT_SCHEMAS
Imports a dump file using the Data Pump Export utility dbms_wm.import_schemas(
job_name          IN VARCHAR2,
alt_schema        IN VARCHAR2,
ignore_last_error IN BOOLEAN);
DECLARE
 jname VARCHAR2(128) := 'UW_IMPORT' ;
 dph   NUMBER;
BEGIN
  n := dbms_datapump.open('IMPORT', 'SCHEMA', NULL, jname, 'COMPATIBLE');
  dbms_datapump.add_file(n, 'uwclass.dmp', 'CTEMP');
  dbms_datapump.add_file(n, 'uwclass.log', 'CTEMP', filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
  dbms_wm.import_schemas(jname);
  dbms_datapump.detach(dph);
EXCEPTION
  WHEN OTHERS THEN
    dbms_datapump.stop_job(dph);
END;
/
 
INITIALIZE_AFTER_IMPORT
Undocumented dbms_wm.initialize_after_import(
topology IN VARCHAR2,
tg_layer_owner IN VARCHAR2);
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 = ''UWCLASS''');
 
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 = ''UWCLASS''');
 
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.move_proc(dest_tablespace IN VARCHAR2 DEFAULT 'SYSAUX');
exec dbms_wm.move_proc('UWDATA');
 
PURGETABLE
Undocumented dbms_wm.purgeTable(
table_id       IN VARCHAR2,
archive_table  IN VARCHAR2,
where_clause   IN VARCHAR2,
workspace      IN VARCHAR2,
savepoint_name IN VARCHAR2,
instant        IN TIMESTAMP WITH TIME ZONE,
purgeafter     IN BOOLEAN);
TBD
 
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
Undocumented 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=''UWCLASS''');
 
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.MLIB.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');
 
REMOVEDEFERREDWORKSPACES
Undocumented dbms_wm.removeDeferredWorkspaces(auto_commit IN BOOLEAN);
BEGIN
  dbms_wm.removeDeferredWorkspaces(TRUE);
END;
/
 
REMOVEUSERDEFINEDHINT
Undocumented 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
 
RENAMESAVEPOINT
Rename a save point dbms_wm.renameSavepoint(
workspace_name     IN VARCHAR2,
savepoint_name     IN VARCHAR2,
new_savepoint_name IN VARCHAR2);
exec dbms_wm.renamesavepoint('ws1', 'SP1', 'SP_FIRST');
 
RENAMEWORKSPACE
Rename a workspace dbms_wm.renameWorkspace(
workspace_name     IN VARCHAR2,
new_workspace_name IN VARCHAR2);
exec dbms_wm.renameWorkspace('ws1', 'wsFirst');
 
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', 'UWCLASS');
 
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', 'UWCLASS');
 
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 Above
 
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 view with information about version-enabled tables that must 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-2018', 'MM-DD-YYYY'), TO_DATE('30-06-2018', 'MM-DD-YYYY'));
 
SETVALIDTIMEFILTEROFF
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');
 
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.MLIB.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=''UWCLASS''');
 
UNTIL_CHANGED
Undocumented dbms_wm.until_changed RETURN TIMESTAMP WITH TIMEZONE;
SELECT dbms_wm.until_changed FROM dual;

UNTIL_CHANGED
------------------
 
 
USEDEFAULTVALUESFORNULLS
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.useDefaultValuesForNulls(mode_var IN VARCHAR2);
exec dbms_wm.useDefaultValuesForNulls('ON');

PL/SQL procedure successfully completed.
 
Workspace Manager Demo
Setup conn sys@pdbdev 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@pdbdev

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

conn ltdemo/ltdemo@pdbdev

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

-- navigate to the 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 the 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');

-- resolve the conflict
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 sys@pdbdev 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;

Related Topics
Built-in Functions
Built-in Packages
LTAQ
LTDDL
LTDTRG
LTI
LTPRIV
LTRIC
LTUTIL
OWM_ASSERT_PKG
OWM_BULK_LOAD_PKG
OWM_CPKG_PKG
OWM_DDL_PKG
OWM_DYNSQL_ACCESS
OWM_IEXP_PKG
OWM_MIG_PKG
OWM_MP_PKG
OWM_VSCRIPT_PKG
OWM_VT_PKG
WM_DDL_UTIL
WM_ERROR
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx