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.
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.
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);
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;
/
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);
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);
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);
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);
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);
dbms_wm.createSavepoint(
workspace IN VARCHAR2,
savepoint_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE);
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');
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);
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);
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);
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);
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');
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;
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
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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)
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);
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);
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);
-- 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;
