Workspace Manager includes PL/SQL subprograms (procedures and functions), in a package named DBMS_WM
, that perform the available features of the product. This chapter provides reference information on each subprogram.
Note: Most Workspace Manager subprograms are procedures, but a few are functions. (A function returns a value; a procedure does not return a value.)Most functions have names starting with Get (such as GetConflictWorkspace and GetWorkspace). |
The subprograms are presented in alphabetical order. For a brief description of subprograms according to their logical groupings, see Section 1.16.
Errors (exceptions) that can occur with Workspace Manager subprograms are documented in Appendix D, including the cause and suggested user action for each error.
Syntax notes:
The DBMS_WM
public synonym for the Workspace Manager PL/SQL package must be used with the subprogram name. The DBMS_WM
public synonym is included in the format and in any examples.
Subprogram calls are not case sensitive, except for any quoted literal values. For example, the following code line excerpts are valid and semantically identical:
EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE'); EXECUTE dbms_wm.createworkspace ('NEWWORKSPACE'); EXECUTE dBms_Wm.cReatEwoRksPace ('NEWWORKSPACE');
Adds 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,
tg_layer_type IN VARCHAR2);
Table 4-1 Add_Topo_Geometry_Layer Procedure Parameters
Parameter | Description |
---|---|
topology |
Topology to which to add the topology geometry layer containing the topology geometries in the specified column. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure. |
table_name |
Name of the topology geometry layer table containing the column specified in |
column_name |
Name of the column (of type |
tg_layer_type |
Type of topology geometry layer: |
This procedure has the same format and meaning as the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure, which is documented in Oracle Spatial Topology and Network Data Models. However, you must use DBMS_WM.Add_Topo_Geometry_Layer, and not SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER, to add a topology geometry layer from a version-enabled feature table to a topology. For information about Workspace Manager support for topologies, see Section 1.14.
The first call to this procedure for a given topology creates the <topology-name>_RELATION$ table, which is described in Oracle Spatial Topology and Network Data Models.
An exception is raised if topology
, table_name
, or column_name
does not exist, if topology
or table_name
is not version-enabled, or if tg_layer_type
is not one of the supported values.
The following example adds a topology geometry layer to the CITY_DATA
topology. The topology geometry layer consists of polygon geometries in the FEATURE
column of the LAND_PARCELS
table.
EXECUTE DBMS_WM.Add_Topo_Geometry_Layer('CITY_DATA', 'LAND_PARCELS', 'FEATURE', 'POLYGON');
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);
Table 4-2 AddAsParentWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace to which to add the parent workspace. The name is case sensitive. |
parent_workspace |
Name of the workspace to add as a parent workspace of |
auto_commit |
A Boolean value (
|
This procedure is part of the support for the multiparent workspaces feature, which is described in Section 1.1.10. If workspace
has only one parent workspace, this procedure makes workspace
a multiparent workspace. If workspace
is already a multiparent workspace, this procedure adds another parent workspace to workspace
.
An exception is raised if one or more of the following apply:
The value of the Workspace Manager system parameter ALLOW_MULTI_PARENT_WORKSPACES
is OFF
.
The value of the Workspace Manager system parameter CR_WORKSPACE_MODE
or NONR_WORKSPACE_MODE
(whichever is applicable, depending on whether or not workspace
is a continually refreshed workspace) is OPTIMISTIC_LOCKING
.
workspace
or parent_workspace
does not exist.
parent_workspace
is already in the ancestor hierarchy of workspace
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
There is a violation of a primary key constraint, referential integrity constraint, or unique constraint in the view of the data in a version-enabled table in workspace
.
The following example adds Workspace4
as a parent workspace of Workspace3
. (See the hierarchy illustration in Figure 1-3 in Section 1.1.10.)
-- Allow multiparent workspaces. (Required for AddAsParentWorkspace) EXECUTE DBMS_WM.SetSystemParameter ('ALLOW_MULTI_PARENT_WORKSPACES', 'ON'); -- Make Workspace3 multiparent by adding Workspace4 as a parent. EXECUTE DBMS_WM.AddAsParentWorkspace ('Workspace3', 'Workspace4');
Modifies the description of a savepoint.
DBMS_WM.AlterSavepoint( workspace IN VARCHAR2, sp_name IN VARCHAR2, sp_description IN VARCHAR2);
Table 4-3 AlterSavepoint Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace in which the savepoint was created. The name is case sensitive. |
sp_name |
Name of the savepoint. The name is case sensitive. |
sp_description |
Description of the savepoint. |
To see the current description of the savepoint, examine the DESCRIPTION
column value for the savepoint in the ALL_WORKSPACE_SAVEPOINTS metadata view, which is described in Section 5.15.
An exception is raised if the user is not the workspace owner or savepoint owner or does not have the WM_ADMIN_ROLE
role.
The following example modifies the description of savepoint SP1
in the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.AlterSavepoint ('NEWWORKSPACE', 'SP1', 'First set of changes for scenario');
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);
Table 4-4 AlterVersionedTable Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table to which to add valid time support. The name is not case sensitive. |
alter_option |
One of the following values: |
parameter_options |
A quoted string (in the general format 'keyword=value, keyword2=value2, ...') containing keywords valid for the specified |
ignore_last_error |
A Boolean value (
|
Use this procedure to add valid time support, rename a constraint, or rename an index for an existing version-enabled table. For more information about adding valid time support, see Section 3.11.
If the alter_option
value is ADD_VALID_TIME
, you can specify none, one, or more of the following parameter_options
keywords:
validFrom
: Starting time period to be set in the WM_VALID column of all existing rows. The default value is the current timestamp.
validTill
: Ending time period to be set in the WM_VALID column of all existing rows. The default value is UNTIL_CHANGED
.
fmt
: Date format. The default value is 'mmddyyyyhh24miss'
. The options are the same as for the TO_TIMESTAMP_TZ function, which is described in Oracle Database SQL Reference.
nlsparam
: Globalization support options. The options and default are the same as for the nlsparam
argument to the TO_CHAR function for date conversion, which is described in Oracle Database SQL Reference.
If the alter_option
value is RENAME_CONSTRAINT
, you must specify both of the following parameter_options
keywords:
constraint_name
: The current name of the constraint to be renamed. The name is not case sensitive.
new_constraint_name
: The new name for the constraint. The name is not case sensitive.
If the alter_option
value is RENAME_INDEX
, you must specify all of the following parameter_options
keywords:
index_owner
: The name of the schema that owns the index to be renamed. The schema name is not case sensitive.
index_name
: The current name of the index to be renamed. The name is not case sensitive.
new_index_name
: The new name for the index. The name is not case sensitive.
If the name of a constraint or index on a version-enabled table is longer than 26 characters, you must use the AlterVersionedTable procedure if you want to rename the constraint or index; you cannot use the ALTER TABLE (for a constraint) or ALTER INDEX (for an index) statement with the RENAME clause. If you use the AlterVersionedTable procedure, you do not need to include it between calls to the BeginDDL and CommitDDL procedures.
If the name of the constraint or index on a version-enabled table is 26 or fewer characters long, you can do either of the following to rename the constraint or index: use the AlterVersionedTable procedure, or use the ALTER TABLE (for a constraint) or ALTER INDEX (for an index) statement with the RENAME clause between calls to the BeginDDL and CommitDDL procedures (as explained in Section 1.8).
If a call to the AlterVersionedTable procedure fails, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. Fix the cause of the error, and then call the AlterVersionedTable procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the AlterVersionedTable procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
An exception is raised if one or more of the following apply:
table_name
does not exist.
alterOptions
is not ADD_VALID_TIME
.
The following example creates a table named MY_TABLE
, version-enables it without valid time support, and then adds valid time support. After the valid time support is added, the WM_VALID
column contains the default valid time period.
CREATE TABLE my_table (id NUMBER PRIMARY KEY); EXECUTE DBMS_WM.EnableVersioning ('my_table'); INSERT INTO my_table VALUES (1); SELECT * FROM my_table; ID ---------- 1 EXECUTE DBMS_WM.AlterVersionedTable('my_table', 'ADD_VALID_TIME'); SELECT * FROM my_table; ID ---------- WM_VALID(VALIDFROM, VALIDTILL) -------------------------------------------------------------------------------- 1 WM_PERIOD('09-JUN-2003 10:04:13 -04:00', NULL)
The following example creates a table named SCOTT.MY_TABLE
, creates an index named MY_INDEX
on the VALUE
column in that table, version-enables the table, and then renames the index to MY_NEW_INDEX
.
CREATE TABLE scott.my_table (id NUMBER PRIMARY KEY, value INTEGER); CREATE INDEX scott.my_index on scott.my_table(value); EXECUTE DBMS_WM.EnableVersioning ('scott.my_table'); EXECUTE DBMS_WM.AlterVersionedTable ('scott.my_table', 'RENAME_INDEX', 'index_owner=scott, index_name=my_index, new_index_name=my_new_index');
Modifies the description of a workspace.
DBMS_WM.AlterWorkspace( workspace IN VARCHAR2, workspace_description IN VARCHAR2);
Table 4-5 AlterWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
workspace_description |
Description of the workspace. |
To see the current description of the workspace, examine the DESCRIPTION
column value for the savepoint in the ALL_WORKSPACES metadata view, which is described in Section 5.16.
An exception is raised if the user is not the workspace owner or does not have the WM_ADMIN_ROLE
role.
The following example modifies the description of the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.AlterWorkspace ('NEWWORKSPACE', 'Testing proposed scenario B');
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);
Table 4-6 BeginBulkLoading Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table into which data will be bulk loaded. The name is not case sensitive. |
workspace |
Name of the workspace in which bulk loading will be performed. The name is case sensitive. |
version |
Version number returned by the GetBulkLoadVersion function. |
check_for_duplicates |
A Boolean value (
|
ignore_last_error |
A Boolean value (
|
single_transaction |
A Boolean value (
See the Usage Notes for more information about this parameter. |
Before you can begin bulk loading data into a version-enabled table, you must call the GetBulkLoadVersion and BeginBulkLoading procedures. You must end the bulk loading session by calling either the CommitBulkLoading procedure (to commit changes made when the data was loaded) or the RollbackBulkLoading procedure (to roll back changes made when the data was loaded). For more information about bulk loading with Workspace Manager, see Section 1.7.
If single_transaction
is FALSE
(the default), the BeginBulkLoading procedure drops some internal Workspace Manager views on the table, to prevent DML operations and certain Workspace Manager operations on the table; however, this also prevents any queries from being made using the specified version-enabled table. Regardless of the single_transaction
parameter value, and especially if it is FALSE
, you should complete the bulk loading as quickly as possible and at a time when applications and users will not need to access the table. The value of the single_transaction
parameter must be the same for both the BeginBulkLoading and CommitBulkLoading procedures for a bulk loading session with a specified table.
A TRUE
value for the check_for_duplicates
parameter does not cause any existing data in the version-enabled table to be checked. If an existing row in the version in which data is being bulk loaded (which could be the latest version of a workspace or the root version) has the same primary key values as a row in the data to be bulk loaded, the behavior depends on the history option setting for the table: if VIEW_WO_OVERWRITE
is set, the newly loaded row is chained to the existing row that has the same primary key values; if VIEW_WO_OVERWRITE
is not set, the new data is not bulk loaded but is instead moved to the discards table.
If a call to the BeginBulkLoading procedure fails, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. Fix the cause of the error, and then call the BeginBulkLoading procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the BeginBulkLoading procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
If performance is an issue, carefully consider whether or not you need to check for duplicate records, because a check_for_duplicates
value of TRUE
(the default) causes Workspace Manager to perform additional internal processing.
An exception is raised if one or more of the following apply:
table_name
does not exist.
table_name
is not version-enabled.
The user does not own the table or does not have the WM_ADMIN_ROLE
role.
The following example gets a bulk load version number for the W1
workspace, and starts the bulk load operation into the EMP
table in that workspace.
DECLARE version INTEGER; BEGIN SELECT DBMS_WM.GetBulkLoadVersion ('W1') INTO version FROM DUAL; DBMS_WM.BeginBulkLoading ('EMP', 'W1', version); END; /
Starts a DDL (data definition language) session for a specified table.
DBMS_WM.BeginDDL( table_name IN VARCHAR2);
Table 4-7 BeginDDL Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table. The name is not case sensitive. |
This procedure starts a DDL session, and it creates a special table whose name is the same as table_name
but with _LTS added to the table name. After calling this procedure, you can perform one or more DDL operations on the table or any indexes or triggers that are based on the table, and then call either the CommitDDL or RollbackDDL procedure.
In addition to creating the special <table-name>_LTS table, the procedure creates other objects:
The <table-name>_LTS table has the same triggers, columns, and indexes as the <table-name> table.
For each parent table with which the <table-name> table has a referential integrity constraint, the same constraint is defined for the <table-name>_LTS table.
Triggers, columns, and referential integrity constraints on the <table-name>_LTS table have the same names as the corresponding ones on the <table-name> table.
For each index on the <table-name> table, the corresponding index on the <table-name>_LTS table has a name in the form <index-name>_LTS.
The primary key constraint on the <table-name>_LTS table has a name in the form <primary-key>_LTS.
All unique constraints on the <table-name>_LTS table have a name in the form <unique-constraint-name>_LTS.
For detailed information about performing DDL operations related to version-enabled tables, see Section 1.8; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.
An exception is raised if one or more of the following apply:
table_name
does not exist or is not version-enabled.
table_name
has a domain index defined on it, and the user has not been directly granted the CREATE TABLE
and CREATE SEQUENCE
privileges.
An open DDL session exists for table_name
. (That is, the BeginDDL procedure has already been called specifying this table, and the CommitDDL or RollbackDDL procedure has not been called specifying this table.)
The following example begins a DDL session, adds a column named COMMENTS
to the COLA_MARKETING_BUDGET
table by using the special table named COLA_MARKETING_BUDGET_LTS
, and ends the DDL session by committing the change.
EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET'); ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100)); EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');
Starts a conflict resolution session.
DBMS_WM.BeginResolve( workspace IN VARCHAR2);
Table 4-8 BeginResolve Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
This procedure starts a conflict resolution session. While this procedure is executing, the workspace is frozen in 1WRITER
mode, as explained in Section 1.1.5.
After calling this procedure, you can execute the ResolveConflicts procedure as needed for various tables that have conflicts, and then call either the CommitResolve or RollbackResolve procedure. For more information about conflict resolution, see Section 1.1.4.
An exception is raised if one or more of the following apply:
There are one or more open database transactions in workspace
.
The user executing the BeginResolve procedure does not have the privilege to access workspace
and its parent workspace.
The following example starts a conflict resolution session in Workspace1
.
EXECUTE DBMS_WM.BeginResolve ('Workspace1');
Changes a workspace from not continually refreshed to continually refreshed. (Continually refreshed workspaces are explained in Section 1.1.9.)
DBMS_WM.ChangeWorkspaceType( workspace IN VARCHAR2, workspace_type IN VARCHAR2 DEFAULT DBMS_WM.CR_WORKSPACE_TYPE, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-9 ChangeWorkspaceType Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
workspace_type |
Must be |
auto_commit |
A Boolean value (
|
For this release, you can only change a workspace that is not continually refreshed to continually refreshed; you cannot change a continually refreshed workspace to not continually refreshed.
An exception is raised if one or more of the following occur:
The user is not the owner of workspace
, and the user does not have the WM_ADMIN_ROLE
role.
workspace_type
is not valid.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The workspace type cannot be changed. For example, the change cannot be made if the Workspace Manager system parameter CR_WORKSPACE_MODE
is set to PESSIMISTIC_LOCKING
, but the NONCR_WORKSPACE_MODE
parameter is set to OPTIMISTIC_LOCKING
and there is versioned data in any continually refreshed workspace.
The following example changes the NEWWORKSPACE
workspace type from not continually refreshed to continually refreshed.
EXECUTE DBMS_WM.ChangeWorkspaceType ('NEWWORKSPACE');
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);
Table 4-10 CommitBulkLoading Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table into which data has been bulk loaded. The name is not case sensitive. |
discards_table |
Name of the table into which discard records are inserted. The name is not case sensitive. If the table does not already exist, it is created. |
check_for_duplicates |
A Boolean value (
|
enforceUCFlag |
A Boolean value (
|
enforceRICFlag |
A Boolean value (
|
ignore_last_error |
A Boolean value (
|
single_transaction |
A Boolean value (
The value of this parameter must be the same as when you called the BeginBulkLoading procedure specifying the table in |
For information about the requirements for bulk loading data into version-enabled tables, see Section 1.7.
This procedure generates versioning metadata for newly loaded data and synchronizes the newly loaded data with the existing versioned data in the table. It can also enforce unique and referential constraints on the newly loaded data. It re-creates all the views that were dropped by the BeginBulkLoading procedure.
A TRUE
value for the check_for_duplicates
parameter does not cause any existing data in the version-enabled table to be checked. If an existing row in the version in which data is being bulk loaded (which could be the latest version of a workspace or the root version) has the same primary key values as a row in the data to be bulk loaded, the behavior depends on the history option setting for the table: if VIEW_WO_OVERWRITE
is set, the newly loaded row is chained to the existing row that has the same primary key values; if VIEW_WO_OVERWRITE
is not set, the new data is not bulk loaded but is instead moved to the discards table.
If a call to the CommitBulkLoading procedure fails, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. Fix the cause of the error, and then call the CommitBulkLoading procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the CommitBulkLoading procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
Note the following performance considerations:
A TRUE
value for check_for_duplicates
requires additional processing time, and a TRUE
value for enforceUCFlag
or enforceRICFlag
may require additional processing time.
If performance is an issue, carefully consider whether or not you need to check for duplicate records.
If the table does not have unique or referential constraints, setting the enforceUCFlag
or enforceRICFlag
parameter to TRUE
does not have a significant effect on performance.
An exception is raised if one or more of the following apply:
table_name
does not exist.
table_name
is not version-enabled.
The BeginBulkLoading procedure has not been called on the table.
The user does not own the table or does not have the WM_ADMIN_ROLE
role.
The following example commits changes made to the EMP
table during a bulk load operation, and specifies DISCARDS
as the table to hold discard records.
EXECUTE DBMS_WM.CommitBulkLoading ('EMP', 'DISCARDS');
Commits DDL (data definition language) changes made during a DDL session for a specified table, and ends the DDL session.
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);
Table 4-11 CommitDDL Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table. The name is not case sensitive. |
ignore_last_error |
A Boolean value (
|
enforce_unique_constraints |
A Boolean value (
|
enforce_RICs |
A Boolean value (
|
This procedure commits changes that were made to a version-enabled table and to any indexes, triggers, and referential integrity constraints based on the version-enabled table during a DDL session. It also deletes the special <table-name>_LTS table that was created by the BeginDDL procedure.
For detailed information about performing DDL operations related to version-enabled tables, see Section 1.8; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.
The enforce_unique_constraints
and enforce_RICs
parameter settings apply only to existing versioned data, and do not affect whether or not existing constraints are enforced for future DML operations on the table.
If a call to the CommitDDL procedure fails, the table is left in an inconsistent state. If this occurs, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. For example, the CommitDDL procedure might have failed because the tablespace was not large enough to add a column. Fix the cause of the error, and then call the CommitDDL procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the CommitDDL procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
An exception is raised if one or more of the following apply:
table_name
does not exist or is not version-enabled.
table_name
has a domain index defined on it, and the user has not been directly granted the CREATE TABLE
and CREATE SEQUENCE
privileges.
An open DDL session does not exist for table_name
. (That is, the BeginDDL procedure has not been called specifying this table, or the CommitDDL or RollbackDDL procedure was already called specifying this table.)
Some invalid DDL operations also cause an exception when CommitDDL procedure is called. See Section 1.8 for information about DDL operations that are supported.
The following example begins a DDL session, adds a column named COMMENTS
to the COLA_MARKETING_BUDGET
table by using the special table named COLA_MARKETING_BUDGET_LTS
, and ends the DDL session by committing the change.
EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET'); ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100)); EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');
Ends a conflict resolution session and saves (makes permanent) any changes in the workspace since the BeginResolve procedure was executed.
DBMS_WM.CommitResolve( workspace IN VARCHAR2);
Table 4-12 CommitResolve Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
This procedure ends the current conflict resolution session (started by the BeginResolve procedure), and saves all changes in the workspace since the start of the conflict resolution session. Contrast this procedure with the RollbackResolve procedure, which discards all changes.
For more information about conflict resolution, see Section 1.1.4.
An exception is raised if one or more of the following apply:
There are one or more open database transactions in workspace
.
The procedure was called by a user that does not have the WM_ADMIN_ROLE
role or that did not execute the BeginResolve procedure on workspace
.
The following example ends the conflict resolution session in Workspace1
and saves all changes.
EXECUTE DBMS_WM.CommitResolve ('Workspace1');
Deletes removable savepoints in a workspace and minimizes the Workspace Manager metadata structures for the workspace. (Removable savepoints are explained in Section 1.1.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);
or
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);
Table 4-13 CompressWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
compress_view_wo_overwrite |
A Boolean value (
|
firstSP |
First savepoint. Savepoint names are case sensitive. If only If If only |
secondSP |
Second savepoint. All removable savepoints from However, if Savepoint names are case sensitive. |
auto_commit |
A Boolean value (
|
commit_in_batches |
A Boolean value (
|
batch_size |
Batch size for internal commit operations if
|
remove_latest_deleted_rows |
A Boolean value (
|
You can compress a workspace when the explicit savepoints (all or some of them) in the workspace are no longer needed. The compression operation is useful for the following reasons:
You can reuse savepoint names after they are deleted. (You cannot create a savepoint that has the same name as an existing savepoint.)
Runtime performance for Workspace Manager operations is improved.
Less disk storage is used for Workspace Manager structures.
While this procedure is executing, the current workspace is frozen in NO_ACCESS
mode, as explained in Section 1.1.5.
A workspace cannot be compressed if there are any sessions in the workspace (except for the LIVE
workspace), or if any user has executed a GotoDate operation or a GotoSavepoint operation specifying a savepoint in the workspace.
If the procedure format without the compress_view_wo_overwrite
parameter is used, a value of FALSE
is assumed for the parameter.
For information about VIEW_WO_OVERWRITE
and other history options, see the information about the EnableVersioning procedure.
If you expect to purge a subset of your historical data periodically, such as removing historical data older than one year, plan to create a savepoint at each expected deletion point on the day it occurs. For example, if you plan to purge 2005 historical data when it is a year old, you need to create a savepoint on January 1, 2006. Then, on January 1, 2007 you can call the CompressWorkspace procedure, specifying the workspace name and the January 1, 2006 savepoint, to delete all history that occurred before 2006.
To see if a version-enabled table can be compressed in primary key range batches, check the value of the BATCH_SIZE
column in the WM_COMPRESS_BATCH_SIZES metadata view, which is described in Section 5.37.
To specify a batch_size
value of PRIMARY_KEY_RANGE
, you must first generate either histogram statistics (for columns of type NUMBER
, INTEGER
, DATE
, TIMESTAMP
, CHAR
, or VARCHAR2
) or general statistics (for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
) on the first column of the primary key. The statement ANALYZE TABLE ... COMPUTE STATISTICS
generates general statistics. If general but not histogram statistics are available for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
, the Workspace Manager system parameter NUMBER_OF_COMPRESS_BATCHES
is used to compute the number of batches when batch_size
is specified as PRIMARY_KEY_RANGE
. For more information about statistics, see Oracle Database Performance Tuning Guide.
An exception is raised if auto_commit
is TRUE
and an open transaction exists, or if the user does not have the privilege to access and merge changes in workspace
.
To compress a workspace and all its descendant workspaces, use the CompressWorkspaceTree procedure.
The following example compresses NEWWORKSPACE
.
EXECUTE DBMS_WM.CompressWorkspace ('NEWWORKSPACE');
The following example compresses NEWWORKSPACE
, deleting all explicit savepoints between the creation of the workspace and the savepoint SP1
.
EXECUTE DBMS_WM.CompressWorkspace ('NEWWORKSPACE', 'SP1');
The following example compresses NEWWORKSPACE
, deleting the explicit savepoint SP1
and all explicit savepoints up to but not including SP2
.
EXECUTE DBMS_WM.CompressWorkspace ('NEWWORKSPACE', 'SP1', 'SP2');
The following example compresses B_focus_1
, accepts the default values for the firstSP
and secondSP
parameters (that is, deletes all explicit savepoints), and specifies FALSE
for the auto_commit
parameter.
EXECUTE DBMS_WM.CompressWorkspace ('B_focus_1', auto_commit => FALSE);
The following example analyzes the COLA_MARKETING_BUDGET_LT
table to generate the necessary histogram statistics for the next statement, and then it compresses B_focus_1
. The call to the CompressWorkspace
procedure accepts the default values for the firstSP
, secondSP
, and auto_commit
parameters; specifies TRUE
for the commit_in_batches
parameter; and specifies PRIMARY_KEY_RANGE
for the batch_size
parameter.
ANALYZE TABLE cola_marketing_budget_lt ESTIMATE STATISTICS FOR COLUMNS SIZE 50 product_id; EXECUTE DBMS_WM.CompressWorkspace ('B_focus_1', NULL, NULL, NULL, TRUE, 'PRIMARY_KEY_RANGE');
Deletes removable savepoints in a workspace and all its descendant workspaces. (Removable savepoints are explained in Section 1.1.2.) It also minimizes the Workspace Manager metadata structures for the affected workspaces, and eliminates any redundant data that might arise from the deletion of the savepoints.
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);
Table 4-14 CompressWorkspaceTree Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
compress_view_wo_overwrite |
A Boolean value (
|
auto_commit |
A Boolean value (
|
commit_in_batches |
A Boolean value (
|
batch_size |
Batch size for internal commit operations if
|
remove_latest_deleted_rows |
A Boolean value (
|
You can compress a workspace and all its descendant workspaces when the explicit savepoints in the affected workspaces are no longer needed (for example, if you will not need to go to or roll back to any of these savepoints). For example, in the hierarchy shown in Figure 1-1 in Section 1.1.1, a CompressWorkspaceTree operation specifying Workspace1 compresses Workspace1, Workspace2, and Workspace3. (For an explanation of database workspace hierarchy, see Section 1.1.1.)
The compression operation is useful for the following reasons:
You can reuse savepoint names after they are deleted. (You cannot create a savepoint that has the same name as an existing savepoint.)
Runtime performance for Workspace Manager operations is improved.
Less disk storage is used for Workspace Manager structures.
While this procedure is executing, the current workspace is frozen in NO_ACCESS
mode, as explained in Section 1.1.5.
A workspace cannot be compressed if there are any sessions in the workspace (except for the LIVE
workspace), or if any user has executed a GotoDate operation or a GotoSavepoint operation specifying a savepoint in the workspace.
To see if a version-enabled table can be compressed in primary key range batches, check the value of the BATCH_SIZE
column in the WM_COMPRESS_BATCH_SIZES metadata view, which is described in Section 5.37.
To specify a batch_size
value of PRIMARY_KEY_RANGE
, you must first generate either histogram statistics (for columns of type NUMBER
, INTEGER
, DATE
, TIMESTAMP
, CHAR,
or VARCHAR2
) or general statistics (for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
) on the first column of the primary key. The statement ANALYZE TABLE ... COMPUTE STATISTICS
generates general statistics. If general but not histogram statistics are available for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
, the Workspace Manager system parameter NUMBER_OF_COMPRESS_BATCHES
is used to compute the number of batches when batch_size
is specified as PRIMARY_KEY_RANGE
. For more information about statistics, see Oracle Database Performance Tuning Guide.
An exception is raised if auto_commit
is TRUE
and an open transaction exists, or if the user does not have the privilege to access and merge changes in workspace
.
If the CompressWorkspaceTree operation fails in any affected workspace, the entire operation is rolled back, and no workspaces are compressed.
To compress a single workspace (deleting all explicit savepoints or just some of them), use the CompressWorkspace procedure.
The following example compresses NEWWORKSPACE
and all its descendant workspaces.
EXECUTE DBMS_WM.CompressWorkspaceTree ('NEWWORKSPACE');
The following example compresses NEWWORKSPACE
and all its descendant workspaces, accepts the default value for the compress_view_wo_overwrite
parameter, and specifies FALSE
for the auto_commit
parameter.
EXECUTE DBMS_WM.CompressWorkspaceTree ('NEWWORKSPACE', auto_commit => FALSE);
The following example compresses NEWWORKSPACE
and all its descendant workspaces; accepts the default value for the compress_view_wo_overwrite
and auto_commit
parameters; specifies TRUE
for the commit_in_batches
parameter; and specifies PRIMARY_KEY_RANGE
for the batch_size
parameter.
EXECUTE DBMS_WM.CompressWorkspaceTree ('NEWWORKSPACE', NULL, NULL, TRUE, 'PRIMARY_KEY_RANGE');
Allows LOB columns (BLOB, CLOB, or NCLOB) in version-enabled tables to be modified. Use this procedure only if a version-enabled table has any LOB columns.
DBMS_WM.CopyForUpdate( table_name IN VARCHAR2, where_clause IN VARCHAR2 DEFAULT '');
Table 4-15 CopyForUpdate Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the table containing one or more LOB columns. The name is not case sensitive. |
where_clause |
The Only primary key columns can be specified in the If the |
This procedure is intended for use only with version-enabled tables containing one or more large object (LOB) columns. The CopyForUpdate procedure must be used because updates performed using the DBMS_LOB package do not fire INSTEAD OF
triggers on the versioning views. Workspace Manager creates INSTEAD OF
triggers on the versioning views to implement the copy-on-write semantics. (For non-LOB columns, you can directly perform the update operation, and the triggers work.)
The following example updates the SOURCE_CLOB
column of TABLE1
for the document with DOC_ID = 1
.
Declare clob_var Begin /* 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 */ 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;
Creates a savepoint for the current version.
DBMS_WM.CreateSavepoint( workspace IN VARCHAR2, savepoint_name IN VARCHAR2, description IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-16 CreateSavepoint Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace in which to create the savepoint. The name is case sensitive. |
savepoint_name |
Name of the savepoint to be created. The name is case sensitive. |
description |
Description of the savepoint to be created. |
auto_commit |
A Boolean value (
|
There are no explicit privileges associated with savepoints; any user who can access a workspace can create a savepoint in the workspace.
This procedure can be performed while there are users in the workspace; there can be open database transactions.
While this procedure is executing, the current workspace is frozen in READ_ONLY
mode, as explained in Section 1.1.5.
An exception is raised if one or more of the following apply:
The user is not in the latest version in the workspace (for example, if the user has called the GotoDate procedure).
workspace
does not exist.
savepoint_name
already exists.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The user does not have the privilege to go to the specified workspace.
The following example creates a savepoint named Savepoint1
in the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.CreateSavepoint ('NEWWORKSPACE', 'Savepoint1');
Creates a new workspace in the database.
DBMS_WM.CreateWorkspace( workspace IN VARCHAR2, description IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE);
or
DBMS_WM.CreateWorkspace( workspace IN VARCHAR2, isrefreshed IN BOOLEAN, description IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-17 CreateWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive, and it must be unique (no other workspace of the same name). |
isrefreshed |
A Boolean value (
If you use the syntax without the |
description |
Description of the workspace. |
auto_commit |
A Boolean value (
|
The new workspace is a child of the current workspace. If the session has not explicitly entered a workspace, it is in the LIVE
database workspace, and the new workspace is a child of the LIVE
workspace. For an explanation of database workspace hierarchy, see Section 1.1.1.
An implicit savepoint is created in the current version of the current workspace. (The current version does not have to be the latest version in the current workspace.) For an explanation of savepoints (explicit and implicit), see Section 1.1.2.
While this procedure is executing, the current workspace is frozen in READ_ONLY
mode, as explained in Section 1.1.5.
This procedure does not implicitly go to the workspace created. To go to the workspace, use the GotoWorkspace procedure.
The following rules apply to continually refreshed workspaces (isrefreshed
value of TRUE
):
The session must be on the latest version in order to create a continually refreshed workspace.
You cannot turn off locking using the SetLockingOFF or SetWorkspaceLockModeOFF procedure for a continually refreshed workspace.
An exception is raised if one or more of the following apply:
workspace
already exists.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The user does not have the privilege to create a workspace.
The following example creates a workspace named NEWWORKSPACE
in the database.
EXECUTE DBMS_WM.CreateWorkspace ('NEWWORKSPACE');
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);
Table 4-18 Delete_Topo_Geometry_Layer Procedure Parameters
Parameter | Description |
---|---|
topology |
Topology from which to delete the topology geometry layer containing the topology geometries in the specified column. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure. |
table_name |
Name of the topology geometry layer table containing the column specified in |
column_name |
Name of the column (of type |
This procedure has the same format and meaning as the SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER procedure, which is documented in Oracle Spatial Topology and Network Data Models. However, you must use DBMS_WM.Delete_Topo_Geometry_Layer, and not SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER, to delete a topology geometry layer from a version-enabled feature table from a topology. For information about Workspace Manager support for topologies, see Section 1.14.
This procedure deletes data associated with the specified topology geometry layer from the edge, node, and face tables (described in Oracle Spatial Topology and Network Data Models).
An exception is generated if topology
or table_name
is not version-enabled, or if table_name
is the only feature table in topology
.
The following example deletes the topology geometry layer that is based on the geometries in the FEATURE
column of the LAND_PARCELS
table from the topology named CITY_DATA
.
EXECUTE DBMS_WM.Delete_Topo_Geometry_Layer('CITY_DATA', 'LAND_PARCELS', 'FEATURE');
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');
Table 4-19 DeleteSavepoint Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace in which the savepoint was created. The name is case sensitive. |
savepoint_name |
Name of the savepoint to be deleted. The name is case sensitive. |
compress_view_wo_overwrite |
A Boolean value (
|
auto_commit |
A Boolean value (
|
commit_in_batches |
A Boolean value (
|
batch_size |
Batch size for internal commit operations if
|
You can delete a savepoint when it is no longer needed (for example, you will not need to go to it or roll back to it).
Deleting a savepoint is useful for the following reasons:
You can reuse a savepoint name after it is deleted. (You cannot create a savepoint that has the same name as an existing savepoint.)
Runtime performance for Workspace Manager operations is improved.
Less disk storage is used for Workspace Manager structures.
While this procedure is executing, the current workspace is frozen in NO_ACCESS
mode, as explained in Section 1.1.5.
To delete a savepoint, you must have the WM_ADMIN_ROLE
role or be the owner of the workspace or the savepoint.
This procedure cannot be executed if there are any sessions with an open database transaction, or if any user has executed a GotoDate operation or a GotoSavepoint operation specifying a savepoint in the workspace.
To specify a batch_size
value of PRIMARY_KEY_RANGE
, you must first generate either histogram statistics (for columns of type NUMBER
, INTEGER
, DATE
, TIMESTAMP
, CHAR
, or VARCHAR2
) or general statistics (for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
) on the first column of the primary key. The statement ANALYZE TABLE ... COMPUTE STATISTICS
generates general statistics. If general but not histogram statistics are available for columns of type NUMBER
, INTEGER
, DATE
, or TIMESTAMP
, the Workspace Manager system parameter NUMBER_OF_COMPRESS_BATCHES
is used to compute the number of batches when batch_size
is specified as PRIMARY_KEY_RANGE
. For more information about statistics, see Oracle Database Performance Tuning Guide.
An exception is raised if one or more of the following apply:
One or more sessions are already in workspace
(unless the workspace is LIVE
).
workspace
does not exist.
savepoint_name
does not exist.
savepoint_name
is not a removable savepoint. (Removable savepoints are explained in Section 1.1.2.)
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The user does not have the privilege to go to the specified workspace.
The following example deletes a savepoint named Savepoint1
in the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.DeleteSavepoint ('NEWWORKSPACE', 'Savepoint1');
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);
Table 4-20 DisableVersioning Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the table or (if |
force |
A Boolean value (
|
ignore_last_error |
A Boolean value (
|
isTopology |
A Boolean value (
|
keepWMValid |
A Boolean value (
|
This procedure is used to reverse the effect of the EnableVersioning procedure. It deletes the Workspace Manager infrastructure (support structures) for versioning of rows, but does not affect any user data in the LIVE
workspace. The workspace hierarchy and any savepoints still exist, but all rows are the same as in the LIVE
workspace. (If there are multiple versions in the LIVE
workspace of a row in the table for which versioning is disabled, only the most recent version of the row is kept.)
If table_name
has valid time support (described in Chapter 3), this procedure deletes the WM_VALID
column and all data in that column. If deleting the WM_VALID
column would cause a primary key constraint violation, only the row valid at the current time is retained.
If a call to the DisableVersioning procedure fails, the table is left in an inconsistent state. If this occurs, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message), and then call the DisableVersioning procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the DisableVersioning procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
Some causes for the failure of the DisableVersioning procedure include the following:
The table contains much data in workspaces and the size of the undo tablespace required for the DisableVersioning procedure is not sufficient.
A compilation error occurred while transferring user-defined triggers from the version-enabled table to the version-disabled table.
The DisableVersioning operation fails if the force
value is FALSE
and any of the following apply:
The table is being modified by any user in any workspace other than the LIVE
workspace.
There are versioned rows of the table in any workspace other than the LIVE
workspace.
Only the owner of a table or a user with the WM_ADMIN_ROLE
role can disable versioning on the table.
Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.
An exception is raised if the table is not version-enabled.
If you want to disable versioning on a table in an Oracle replication environment, see Section C.2 for guidelines and other information.
For information about Workspace Manager support for tables in an Oracle Spatial topology, see Section 1.14.
The following example disables the EMPLOYEE
table for versioning.
EXECUTE DBMS_WM.DisableVersioning ('employee');
The following example disables the EMPLOYEE
table for versioning and ignores the last error that occurred during the previous call to the DisableVersioning procedure.
EXECUTE DBMS_WM.DisableVersioning ('employee', ignore_last_error => true);
The following example disables the EMPLOYEE
, DEPARTMENT
, and LOCATION
tables (which have multilevel referential integrity constraints) for versioning.
EXECUTE DBMS_WM.DisableVersioning('employee,department,location');
Deletes replication support objects that were created by the GenerateReplicationSupport procedure.
DBMS_WM.DropReplicationSupport();
None.
To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle Database Advanced Replication and Oracle Database Advanced Replication Management API Reference.
You must execute this procedure as the replication administrator user at the writer site.
This procedure drops replication support for any version-enabled tables at the nonwriter sites; however, it does not version-disable any version-enabled tables.
The following example drops replication support that had previously been enabled using the GenerateReplicationSupport procedure.
DBMS_WM.DropReplicationSupport();
Version-enables a table, creating the necessary structures to enable the table 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);
Table 4-21 EnableVersioning Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the table or (if |
hist |
History option, for tracking modifications to
|
isTopology |
A Boolean value (
|
validTime |
A Boolean value (
|
undo_space |
A string containing |
The table that is being version-enabled must have a primary key defined. The primary key can be a composite (multicolumn) primary key.
Only the owner of a table or a user with the WM_ADMIN
role can enable versioning on the table.
Tables that are version-enabled and users that own version-enabled tables cannot be deleted. You must first disable versioning on the relevant table or tables.
Tables owned by SYS
cannot be version-enabled.
An exception is raised if one or more of the following apply:
table_name
is already version-enabled.
table_name
contains a list of tables and any of the tables has a referential integrity constraint with a table that is not in the list.
table_name
contains any columns whose names start with WM_ or WM$.
If the table is version-enabled with the VIEW_WO_OVERWRITE
hist option specified, this option can later be disabled and re-enabled by calling the SetWoOverwriteOFF and SetWoOverwriteON procedures.
The history option enables you to log and audit modifications.
The history option affects the behavior of the GotoDate procedure. See the Usage Notes for that procedure.
If you expect to purge a subset of your historical data periodically, such as removing historical data older than one year, plan to create a savepoint at each expected deletion point on the day it occurs. For example, if you plan to purge 2005 historical data when it is a year old, you need to create a savepoint on January 1, 2006. Then, on January 1, 2007 you can call the CompressWorkspace procedure, specifying the workspace name and the January 1, 2006 savepoint, to delete all history that occurred before 2006
If you want to version-enable a table in an Oracle replication environment, see Section C.2 for guidelines and other information.
For information about Workspace Manager support for tables in an Oracle Spatial topology, see Section 1.14.
Current notes and restrictions include the following:
If you have referential integrity constraints on version-enabled tables, note the considerations and restrictions in Section 1.9.1.
If you have triggers defined on version-enabled tables, note the considerations and restrictions in Section 1.10.
Constraints and privileges defined on the table are carried over to the version-enabled table.
DDL operations on version-enabled tables are subject to the procedures and restrictions described in Section 1.8.
Index-organized tables cannot be version-enabled.
Object tables cannot be version-enabled.
A table with one or more columns of LONG data type cannot be version-enabled.
A table with one or more nested table columns cannot be version-enabled unless the ALLOW_NESTED_TABLE_COLUMNS
Workspace Manager system parameter is set to ON
.
The following example enables versioning on the EMPLOYEE
table.
EXECUTE DBMS_WM.EnableVersioning('employee');
The following example enables versioning on the EMPLOYEE
, DEPARTMENT
, and LOCATION
tables, which have multilevel referential integrity constraints.
EXECUTE DBMS_WM.EnableVersioning('employee,department,location');
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);
Table 4-22 Export Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the table containing the data to be exported. The name is not case sensitive. |
staging_table |
Name of the table to hold the exported data. The name is not case sensitive. If the table does not exist, a new table with this name is created, with a structure suitable for Workspace Manager export and import operations. (See the Usage Notes for more information about the staging table.) |
workspace |
Name of the workspace from which to export the data. The name is case sensitive. |
where_clause |
The Only primary key columns can be specified in the If the |
export_scope |
The scope (amount of data) for the export operation.
|
after_savepoint_name |
Name of a savepoint: only data inserted, updated, or deleted after this savepoint is exported. If you do not specify See the Usage Notes for guidelines relating to the savepoint-related and instant-related parameters. |
as_of_savepoint_name |
Name of a savepoint: only data in the workspace at the time the savepoint was created is exported. If you do not specify See the Usage Notes for guidelines relating to the savepoint-related and instant-related parameters. |
after_instant |
Date/time specification: only data inserted, updated, or deleted after this time is exported. If you do not specify See the Usage Notes for guidelines relating to the savepoint-related and instant-related parameters. |
as_of_instant |
Date/time specification: only data that was in the workspace at this time is exported. If you do not specify See the Usage Notes for guidelines relating to the savepoint-related and instant-related parameters. |
versioned_db |
A Boolean value (
|
overwrite_existing_data |
A Boolean value (
|
auto_commit |
A Boolean value (
|
All data that satisfies the where_clause
in the version-enabled table table_name
, the export_scope
parameter, and any parameters relating to a time or a savepoint in workspace
is exported to the staging table (staging_table
parameter).
Each row of data to be exported is considered to be one of the following: inserted, updated, or deleted in workspace
(that is, modified data); or data that was not modified in workspace
but can be seen in it (that is, ancestor data). If data is exported from the LIVE
workspace, it is all modified data. If a workspace is created and no data has yet been versioned in it, and the Export procedure is called, all the data is ancestor data.
The first time you export data from a version-enabled table, the staging table should not exist; that is, do not try to create a staging table, but let the procedure create one for you using the name specified for the staging_table
parameter. The staging table will contain all columns in the original table (table_name
parameter), plus some columns for use by Workspace Manager.
After the staging table is created, you can use it for subsequent export operations from the original table, as long as you have not done any of the following DDL operations on the original table: altered any column names or data types, or modified or deleted the primary key constraint. If you have made any of these alterations to the original table, drop the staging table before you call the Export procedure, so that Workspace Manager can create a new staging table. (If you want to overwrite data in an existing staging table, you must also specify overwrite_existing_data
as TRUE
.)
The staging table must be in the current user's schema; or if it is in another schema, the current user must have the CREATE ANY TABLE
and INSERT ANY TABLE
privileges.
It is recommended that you specify no more than one of the following savepoint-related and instant-related parameters: after_savepoint_name
, as_of_savepoint_name
, after_instant
, as_of_instant
. If you specify after_savepoint_name
and after_instant
, the interaction of the two parameters can have complex results. You cannot specify the following parameter combinations: after_savepoint_name
and as_of_savepoint_name
, after_instant
and as_of_instant
, or as_of_savepoint_name
and as_of_instant
.
An exception is raised if one or more of the following apply:
A specified table, workspace, or savepoint does not exist.
table_name
contains a nested table column or a column named WM_VALID of type WM_PERIOD.
staging_table
exists but is not in a valid format for the export operation.
staging_table
is not in the current user's schema and the current user does not have the CREATE TABLE
and INSERT TABLE
privileges.
The user does not have the ACCESS_WORKSPACE
privilege for workspace
or the ACCESS_ANY_WORKSPACE
privilege.
overwrite_existing_data
is FALSE
and data that needs to be exported already exists in staging_table
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example exports all data from the COLA_MARKETING_BUDGET
table in workspace B_Focus_2
into the staging table COLA_MARKETING_BUDGET_STG
. (The EXECUTE
statement is actually on a single line.)
EXECUTE DBMS_WM.Export(table_name => 'COLA_MARKETING_BUDGET', staging_table => 'COLA_MARKETING_BUDGET_STG', workspace => 'B_focus_2');
Finds tables that need to be version-enabled along with a specified table, due to referential integrity constraint relationships.
DBMS_WM.FindRICSet( table_name IN VARCHAR2, result_table IN VARCHAR2);
Table 4-23 FindRICSet Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the table for which to find all other tables that will need to be version-enabled along with it, because of referential integrity constraint relationships. The name is not case sensitive. |
result_table |
Name of the table to hold the results. The name is not case sensitive. This table must have two columns, |
Workspace Manager has several considerations relating to referential integrity constraints, as explained in Section 1.9.1. Sometimes, before you can version-enable a table, you must version-enable other tables that are in referential integrity constraints affecting the table. The FindRICSet
procedure lets you find all these other tables.
To display the results, use the SET SERVEROUTPUT ON
statement before calling this procedure.
If the result table is not in the current user's schema, the following requirements apply:
If the result table does not exist, the current user must have the CREATE ANY TABLE
privilege.
If the result table already exists, the current user must have the required privileges to insert into the table.
An exception is raised if one or more of the following apply:
table_name
does not exist.
result_table
exists but is not in a valid format.
result_table
exists and the current user does not have the required privileges to insert into the table.
result_table
does not exist, is specified for a schema other than the current user's schema, and the current user does not have the CREATE ANY TABLE
privilege.
The following example creates two tables, EMPLOYEES
and DEPARTMENTS
, where DEPARTMENTS.MANAGER_ID
has a foreign key relationship referencing EMPLOYEES.EMPLOYEE_ID
. The example then finds all tables that would need to be version-enabled if EMPLOYEES
and DEPARTMENTS
were version-enabled.
The results show that is you want to version-enable the EMPLOYEES
table, you must version-enable both the EMPLOYEES
and DEPARTMENTS
tables; but if you want to version-enable the DEPARTMENTS
table, you do not need to version-enable any other tables.
create table employees (employee_id number primary key, employee_name varchar2(30)); create table departments (dept_id number primary key, manager_id number references employees(employee_id)); -- Check RICs; result table does not already exist. EXECUTE DBMS_WM.FindRICSet('EMPLOYEES', 'EMPLOYEES_RESULTS'); SELECT * FROM employees_results; TABLE_OWNER TABLE_NAME ------------------------------ ------------------------------ WM_DEVELOPER EMPLOYEES WM_DEVELOPER DEPARTMENTS EXECUTE DBMS_WM.FindRICSet('DEPARTMENTS', 'DEPARTMENTS_RESULTS'); SELECT * FROM departments_results; TABLE_OWNER TABLE_NAME ------------------------------ ------------------------------ WM_DEVELOPER DEPARTMENTS
Restricts access to a workspace and the ability of users to make changes in the workspace.
DBMS_WM.FreezeWorkspace( workspace IN VARCHAR2, freezemode IN VARCHAR2 DEFAULT 'NO_ACCESS', freezewriter IN VARCHAR2 DEFAULT NULL, force IN BOOLEAN DEFAULT FALSE);
or
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);
Table 4-24 FreezeWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
session_duration |
A Boolean value (
|
freezemode |
Mode for the frozen workspace. Must be one of the following values:
|
freezewriter |
The user that is allowed to make changes in the workspace. Can be specified only if |
force |
A Boolean value (
|
If you specify the procedure syntax that does not include the session_duration
parameter, it is equivalent to specifying FALSE
for that parameter: that is, the workspace is not unfrozen when the session that called the FreezeWorkspace procedure disconnects from the database.
The operation fails if one or more of the following apply:
workspace
is already frozen (unless force
is TRUE
).
Any sessions are in workspace
and freezemode
is NO_ACCESS
(specified or defaulted).
session_duration
is FALSE and freezemode
is 1WRITER_SESSION
.
If freezemode
is READ_ONLY
or 1WRITER
, the workspace cannot be frozen if there is an active database transaction.
You can freeze a workspace only if one or more of the following apply:
You are the owner of the specified workspace.
You have the WM_ADMIN_ROLE
, the FREEZE_ANY_WORKSPACE
privilege, or the FREEZE_WORKSPACE
privilege for the specified workspace.
The LIVE
workspace can be frozen only if freezemode
is READ_ONLY
or 1WRITER
.
To reverse the effect of FreezeWorkspace, use the UnfreezeWorkspace procedure.
The following example freezes the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.FreezeWorkspace ('NEWWORKSPACE');
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');
Table 4-25 GenerateReplicationSupport Procedure Parameters
Parameter | Description |
---|---|
mastersites |
Comma-delimited list of nonwriter site names (database links) to be added to the Workspace Manager replication environment. Do not include the local site (the writer site) in the list. |
groupname |
Name of the master group to be created. This group will appear as a regular replication master group, and it can be managed from all the Oracle replication interfaces, including Oracle Enterprise Manager. |
groupdescription |
Description of the new master group. The default is |
To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle Database Advanced Replication and Oracle Database Advanced Replication Management API Reference.
You must execute this procedure as the replication administrator user at the writer site.
Before executing this procedure, ensure that the following are true:
There are no workspaces, savepoints, or version-enabled tables on any of the remote sites specified in the mastersites
list.
All the remote sites and the local site have the same version of Workspace Manager installed. You can check the Workspace Manager version number in the WM_INSTALLATION metadata view.
If there are version-enabled tables on the local site, these tables must exist and must not be version-enabled on each of the remote sites.
This procedure performs the following operations:
Verifies that the local site and all the sites specified in the mastersites
list are running the same version of Workspace Manager.
Verifies that there are no workspaces, savepoints, or version-enabled tables on any of the remote sites specified in the mastersites
list.
Creates a master group, having the name specified in the groupname
parameter, with the local site as the master definition site and the writer site.
Adds the Workspace Manager metadata tables to this group.
Disables Workspace Manager operations at all the nonwriter sites (the remote sites specified in the mastersites
list).
If there are any version-enabled tables at the local site, version-enables these tables at each of the remote sites specified in the mastersites
list and sets them up for replication.
Starts the master activity for the newly created master group.
To drop replication support for the Workspace Manager environment, use the DropReplicationSupport procedure.
The following example generates replication support for the Workspace Manager environment at a hypothetical company.
DBMS_WM.GenerateReplicationSupport( mastersites => 'BACKUP-SITE1.ACME.COM, BACKUP-SITE2.ACME.COM'); groupname => 'OWM-GROUP', groupdescription => 'OWM Replication group for Acme Corp.');
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;
Table 4-26 GetBulkLoadVersion Function Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace for which to return the list of bulk load version. The name is case sensitive. |
savepoint_var |
The version in the workspace in which data will be bulk loaded. Must be one of the following:
|
Before you can begin bulk loading data into a version-enabled table, you must call the GetBulkLoadVersion and BeginBulkLoading procedures. You must end the bulk loading session by calling either the CommitBulkLoading procedure (to commit changes made when the data was loaded) or the RollbackBulkLoading procedure (to roll back changes made when the data was loaded). For more information about bulk loading with Workspace Manager, see Section 1.7.
An exception is raised if one or more of the following apply:
workspace
does not exist.
savepoint_var
is not a valid value.
savepoint_var
is ROOT_VERSION
but workspace
is not LIVE
.
The following example gets a bulk load version number for the W1
workspace, and starts the bulk load operation into the EMP
table in that workspace.
DECLARE version INTEGER; BEGIN SELECT DBMS_WM.GetBulkLoadVersion ('W1') INTO version FROM DUAL; DBMS_WM.BeginBulkLoading ('EMP', 'W1', version); END; /
Returns the name of the workspace on which the session has performed the SetConflictWorkspace procedure.
DBMS_WM.GetConflictWorkspace() RETURN VARCHAR2;
None.
If the SetConflictWorkspace procedure has not been executed, the name of the current workspace is returned.
The following example displays the name of the workspace on which the session has performed the SetConflictWorkspace procedure.
SELECT DBMS_WM.GetConflictWorkspace FROM DUAL; GETCONFLICTWORKSPACE ----------------------------------------------------------------------------- B_focus_2
Returns the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions operation.
DBMS_WM.GetDiffVersions() RETURN VARCHAR2;
None.
The returned string is in the format '(WS1,SP1), (WS2,SP2)'
. This format, including the parentheses, is intended to help you if you later want to use parts of the returned string in a call to the SetDiffVersions procedure.
The following example displays the names of the (workspace, savepoint) pairs on which the session has performed the SetDiffVersions operation.
SELECT DBMS_WM.GetDiffVersions FROM DUAL; GETDIFFVERSIONS -------------------------------------------------------------------------------- (B_focus_1, LATEST), (B_focus_2, LATEST)
Returns the locking mode for the current session, which determines whether or not access is enabled to versioned rows and corresponding rows in the previous version.
DBMS_WM.GetLockMode() RETURN VARCHAR2;
None.
This function returns E
, S
, C
, or NULL
.
For explanations of E
(exclusive), S
(shared), and C
(carry-forward), see the description of the lockmode
parameter of the SetLockingON procedure.
NULL
indicates that locking is not in effect. (Calling the SetLockingOFF procedure results in this setting.)
For an explanation of Workspace Manager locking, see Section 1.3. See also the descriptions of the SetLockingON and SetLockingOFF procedures.
The following example displays the locking mode in effect for the session.
SELECT DBMS_WM.GetLockMode FROM DUAL; GETLOCKMODE -------------------------------------------------------------------------------- C
Returns the names of workspaces visible in the multiworkspace views for version-enabled tables.
DBMS_WM.GetMultiWorkspaces() RETURN VARCHAR2;
None.
This procedure returns the names of workspaces visible in the multiworkspace views, which are described in Section 5.46.
If no workspaces are visible in the multiworkspace views, NULL
is returned. If more than one workspace name is returned, names are separated by a comma (for example: workspace1,workspace2,workspace3
).
To make a workspace visible in the multiworkspace views, use the SetMultiWorkspaces procedure.
The following example displays the names of workspaces visible in the multiworkspace views.
SELECT DBMS_WM.GetMultiWorkspaces FROM DUAL;
Returns the context of the current operation for the current session.
DBMS_WM.GetOpContext() RETURN VARCHAR2;
None.
This function returns one of the following values:
DML
: The current operation is driven by data manipulation language (DML) initiated by the user.
MERGE_REMOVE
: The current operation was initiated by a MergeWorkspace procedure call with the remove_workspace
parameter set to TRUE
or a MergeTable procedure call with the remove_data
parameter set to TRUE
.
MERGE_NOREMOVE
: The current operation was initiated by a MergeWorkspace procedure call with the remove_workspace
parameter set to FALSE
or a MergeTable procedure call with the remove_data
parameter set to FALSE
.
The returned value can be used in user-defined triggers to take appropriate action based on the current operation.
The following example displays the context of the current operation.
SELECT DBMS_WM.GetOpContext FROM DUAL; GETOPCONTEXT -------------------------------------------------------------------------------- DML
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;
Table 4-27 GetPhysicalTableName Function Parameters
Parameter | Description |
---|---|
table_owner |
Name of the schema that owns |
table_name |
Name of the version-enabled table for which to return the name of its associated physical table. |
If table_name
is a version-enabled table, this function returns the name of the table, whose name is in the form <table_name>_LT, that was created by Workspace Manager when the EnableVersioning procedure was called. For information about these <table_name>_LT tables, see Section 1.1.11.
If table_name
is a not a version-enabled table, this function returns table_name
. Thus, you can also use this function to check whether or not a table is version-enabled (that is, by checking whether a name in the form <table_name>_LT or the original table name is returned).
The following example displays the physical table name associated with the COLA_MARKETING_BUDGET
table after that table is version-enabled.
SELECT DBMS_WM.GetPhysicalTableName('wm_developer', 'cola_marketing_budget') FROM DUAL; DBMS_WM.GETPHYSICALTABLENAME('WM_DEVELOPER','COLA_MARKETING_BUDGET') -------------------------------------------------------------------------------- COLA_MARKETING_BUDGET_LT
Returns a comma-delimited list of all privileges that the current user has for the specified workspace.
DBMS_WM.GetPrivs( workspace IN VARCHAR2) RETURN VARCHAR2;
Table 4-28 GetPrivs Function Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace for which to return the list of privileges. The name is case sensitive. |
For information about Workspace Manager privileges, see Section 1.4.
The following example displays the privileges that the current user has for the B_focus_2
workspace.
SELECT DBMS_WM.GetPrivs ('B_focus_2') FROM DUAL; DBMS_WM.GETPRIVS('B_FOCUS_2') -------------------------------------------------------------------------------- ACCESS,MERGE,CREATE,REMOVE,ROLLBACK
Retrieves information about the current workspace and session context.
DBMS_WM.GetSessionInfo( workspace OUT VARCHAR2, context OUT VARCHAR2, context_type OUT VARCHAR2);
Table 4-29 GetSessionInfo Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace that the current session is in. |
context |
The context of the current session in the workspace, expressed as one of the following: |
context_type |
The type of context for the current session in the workspace. Specifically, one of the following values: |
This procedure is useful if you need to know where a session is (workspace and context) -- for example, after you have performed a combination of GotoWorkspace, GotoSavepoint, and GotoDate operations.
After the procedure successfully executes, the context
parameter contains one of the following values:
LATEST
: The session is currently on the LATEST
logical savepoint (explained in Section 1.1.2), and it can see changes as they are made in the workspace. The context is automatically set to LATEST
when the session enters the workspace (using the GotoWorkspace procedure).
A savepoint name: The session is currently on a savepoint in the workspace. The session cannot see changes as they are made in the latest version of the workspace, but instead sees a static view of the data as of the savepoint creation time. The session context is set to the savepoint name after a call to the GotoSavepoint procedure.
An instant (a point in time): The session is currently on a specific point in time. The session cannot see changes as they are made in the latest version of the workspace, but instead sees a static view of the data as of the specific time. The session context is set to an instant after a call to the GotoDate procedure.
For detailed information about the session context, see Section 1.2.
The following example retrieves and displays information about the current workspace and context in the session.
DECLARE 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; / Session currently in workspace: B_focus_2 Session context is: LATEST Session context is on: LATEST PL/SQL procedure successfully completed.
Returns the value of a Workspace Manager system parameter.
DBMS_WM.GetSytstemParameter( name IN VARCHAR2) RETURN VARCHAR2;
Table 4-30 GetSystemParameter Procedure Parameters
Parameter | Description |
---|---|
name |
Name of the Workspace Manager system parameter for which to set the value. The name must be one of the following: |
For information about Workspace Manager system parameters, see Section 1.5.
An exception is raised if the name
value is not valid.
The following checks if multiparent workspaces (described in Section 1.1.10) are allowed.
SELECT DBMS_WM.GetSystemParameter ('ALLOW_MULTI_PARENT_WORKSPACES') FROM DUAL; DBMS_WM.GETSYSTEMPARAMETER('ALLOW_MULTI_PARENT_WORKSPACES') -------------------------------------------------------------------------------- ON
Returns the ValidFrom
attribute of the current session valid time. (Valid time support is described in Chapter 3.)
DBMS_WM.GetValidFrom() RETURN TIMESTAMP WITH TIME ZONE;
None.
To set the session valid time period, use the SetValidTime procedure.
To get the ValidTill
attribute of the current session valid time, use the GetValidTill function.
The following example displays the ValidFrom
attribute of the current session valid time.
SELECT DBMS_WM.GetValidFrom FROM DUAL; GETVALIDFROM --------------------------------------------------------------------------- 01-JAN-1995 12:00:00 -04:00
Returns the ValidTill
attribute of the current session valid time. (Valid time support is described in Chapter 3.)
DBMS_WM.GetValidTill() RETURN TIMESTAMP WITH TIME ZONE;
None.
To set the session valid time period, use the SetValidTime procedure.
To get the ValidFrom
attribute of the current session valid time, use the GetValidFrom function.
The following example displays the ValidTill
attribute of the current session valid time.
SELECT DBMS_WM.GetValidTill FROM DUAL; GETVALIDTILL --------------------------------------------------------------------------- 01-JAN-1996 12:00:00 -04:00
Returns the number of bytes currently used to store the Workspace Manager metadata.
DBMS_WM.GetWMMetadataSpace() RETURN NUMBER;
None.
The Workspace Manager metadata (views, internal tables, and other objects) is by default stored in the default tablespace of the WMSYS
user. You cannot directly control the size of the Workspace Manager metadata, but you can control its placement by using the Move_Proc procedure to move the metadata to a different tablespace. You can use the GetWMMetadataSpace function to determine the approximate minimum space that you will need to have available in the tablespace into which you are considering moving the Workspace Manager metadata.
The following example displays the number of bytes currently used to store the Workspace Manager metadata.
SELECT DBMS_WM.GetWMMetadataSpace FROM DUAL; GETWMMETADATASPACE ------------------ 6750208
Returns the current workspace for the session.
DBMS_WM.GetWorkspace() RETURN VARCHAR2;
None.
None.
The following example displays the current workspace for the session.
SELECT DBMS_WM.GetWorkspace FROM DUAL; GETWORKSPACE -------------------------------------------------------------------------------- B_focus_2
Goes to a point at or near the specified date and time in the current workspace.
DBMS_WM.GotoDate( in_date IN VARCHAR2, fmt IN VARCHAR2 DEFAULT 'mmddyyyyhh24miss', nlsparam IN VARCHAR2 DEFAULT NULL, tsWtz IN BOOLEAN DEFAULT FALSE);
Table 4-31 GotoDate Procedure Parameters
Parameter | Description |
---|---|
in_date |
Date and time for the read-only view of the workspace. (See the Usage Notes for details.) If |
fmt |
Date format. The options are the same as for the Default: |
nlsparam |
Globalization support options. The options are the same as for the |
tsWtz |
Timestamp with time zone flag. A Boolean value (
|
You are presented a read-only view of the current workspace at or near the specified date and time. The exact time point depends on the history option for tracking changes to data in version-enabled tables, as set by the EnableVersioning procedure or modified by the SetWoOverwriteOFF or SetWoOverwriteON procedure:
NONE
: The read-only view reflects the first savepoint after in_date
.
VIEW_W_OVERWRITE
: The read-only view reflects the data values in effect at in_date
, except if in_date
is between two savepoints and data was changed between the two savepoints. In this case, data that had been changed between the savepoints might be seen as empty or as having a previous value. To ensure the most complete and accurate view of the data, specify the VIEW_WO_OVERWRITE
history option when version-enabling a table.
VIEW_WO_OVERWRITE
: The read-only view reflects the data values in effect at in_date
.
For an explanation of the history options, see the description of the hist
parameter for the EnableVersioning procedure.
The following example scenario shows the effect of the VIEW_WO_OVERWRITE
setting. Assume the following sequence of events:
The MANAGER_NAME
value in a row is Adams
.
Savepoint SP1
is created.
The MANAGER_NAME
value is changed to Baxter
.
The time point that will be specified as in_date
(in step 7) occurs.
The MANAGER_NAME
value is changed to Chang
. (Thus, the value has been changed both before and after in_date
since the first savepoint and before the second savepoint.)
Savepoint SP2
is created.
A GotoDate operation is executed, specifying the time point in step 4 as in_date
.
In the preceding scenario:
If the history option in effect is VIEW_WO_OVERWRITE
, the MANAGER_NAME
value after step 7 is Baxter
. After step 5, the versioned table has three rows, each with a different MANAGER_NAME
value (Adams
, Baxter
, Chang
), because each change is made in a new copy of the row.
If the history option in effect is VIEW_W_OVERWRITE
, no value is seen after step 7. The updates in steps 3 and 5 are made in the same copy of the row, and the update in step 5 overwrites the update in step 3. As a result, after step 5 the versioned table has two rows, with MANAGER_NAME
values Adams
and Chang
. Because the MANAGER_NAME
value (Baxter
) that was in effect at the specified instant has been overwritten, no row is visible.
If the history option in effect is NONE
, the MANAGER_NAME
value after step 7 is Chang
, because the first savepoint after the specified instant is SP2
. After step 5, the versioned table has two rows, with MANAGER_NAME
values Adams
and Chang
.
The GotoDate procedure should be executed while users exist in the workspace. There are no explicit privileges associated with this procedure.
The following example goes to a point at or near midnight at the start of 08-Jun-2004, depending on the history option currently in effect.
EXECUTE DBMS_WM.GotoDate ('08-JUN-04', 'DD-MON-YY');
Goes to the specified savepoint in the current workspace.
DBMS_WM.GotoSavePoint( savepoint_name IN VARCHAR2 DEFAULT 'LATEST');
Table 4-32 GotoSavepoint Procedure Parameters
Parameter | Description |
---|---|
savepoint_name |
Name of the savepoint. The name is case sensitive. If |
You are presented a read-only view of the workspace at the time of savepoint creation. This procedure is useful for examining the workspace from different savepoints before performing a rollback to a specific savepoint by calling the RollbackToSP procedure to delete all rows from that savepoint forward.
This operation can be executed while users exist in the workspace. There are no explicit privileges associated with this operation.
If you do not want to roll back to the savepoint, you can call the GotoSavepoint procedure with a null parameter to go to the currently active version in the workspace. (This achieves the same result as calling the GotoWorkspace procedure and specifying the workspace.)
For more information about savepoints, including the LATEST
savepoint, see Section 1.1.2.
The following example goes to the savepoint named Savepoint1
.
EXECUTE DBMS_WM.GotoSavepoint ('Savepoint1');
Moves the current session to the specified workspace.
DBMS_WM.GotoWorkspace( workspace IN VARCHAR2);
Table 4-33 GotoWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
After a user goes to a workspace, modifications to data can be made there.
To go to the live database, specify workspace
as LIVE
. Because many operations are prohibited when any users (including you) are in the workspace, it is often convenient to go to the LIVE
workspace before performing operations on created workspaces.
An exception is raised if one or more of the following apply:
workspace
does not exist.
The user does not have ACCESS_WORKSPACE
privilege for workspace
.
workspace
has been frozen in NO_ACCESS
mode (see the FreezeWorkspace procedure).
The following example includes the user in the NEWWORKSPACE
workspace. The user will begin to work in the latest version in that workspace.
EXECUTE DBMS_WM.GotoWorkspace ('NEWWORKSPACE');
The following example includes the user in the LIVE
database workspace. By default, when users connect to a database, they are placed in this workspace.
EXECUTE DBMS_WM.GotoWorkspace ('LIVE');
Grants privileges on multiparent graph workspaces to users and roles. The grant_option
parameter enables the grantee to grant the specified privileges to other 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);
Table 4-34 GrantGraphPriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
leaf_workspace |
Name of the leaf workspace in the directed acyclic graph. (Leaf workspaces, directed acyclic graphs, and other concepts related to multiparent workspaces are explained in Section 1.1.10.) The name is case sensitive. |
grantee |
Name of the user (can be the |
node_types |
List of letters (in parentheses and comma-delimited) representing the types of nodes on which to grant the privileges: |
grant_option |
Specify |
auto_commit |
A Boolean value (
|
Contrast this procedure with GrantWorkspacePriv, which grants workspace-level Workspace Manager privileges on workspaces other than multiparent graph workspaces.
If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT
has been granted the ACCESS_WORKSPACE
privilege with grant_option
as NO
, but that the PUBLIC
user group has been granted the ACCESS_WORKSPACE
privilege with grant_option
as YES
. Because user SCOTT
is a member of PUBLIC
, user SCOTT
has the ACCESS_WORKSPACE
privilege with the grant option.
The WM_ADMIN_ROLE
role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE
role is automatically given to the DBA
role.
The ACCESS_WORKSPACE
or ACCESS_ANY_WORKSPACE
privilege is needed for all other Workspace Manager privileges.
To revoke workspace-level privileges on multiparent graph workspaces, use the RevokeGraphPriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You do not have the privilege to grant priv_types
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example enables user Smith
to access all types of nodes in the directed acyclic graph in which the NEWWORKSPACE
workspace is the leaf workspace and to merge changes in these workspaces, and it allows Smith
to grant the two specified privileges on the leaf workspace to other users.
DBMS_WM.GrantGraphPriv ('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith', 'YES');
Grants system-level privileges (not restricted to a particular workspace) to users and roles. The grant_option
parameter enables the grantee to grant the specified privileges to other 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);
Table 4-35 GrantSystemPriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
grantee |
Name of the user (can be the |
grant_option |
Specify |
auto_commit |
A Boolean value (
|
Contrast this procedure with GrantWorkspacePriv, which grants workspace-level Workspace Manager privileges with keywords that do not contain ANY
and which has a workspace
parameter.
If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT
has been granted the ACCESS_ANY_WORKSPACE
privilege with grant_option
as NO
, but that the PUBLIC
user group has been granted the ACCESS_ANY_WORKSPACE
privilege with grant_option
as YES
. Because user SCOTT
is a member of PUBLIC
, user SCOTT
has the ACCESS_ANY_WORKSPACE
privilege with the grant option.
The WM_ADMIN_ROLE
role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE
role is automatically given to the DBA
role.
The ACCESS_WORKSPACE
or ACCESS_ANY_WORKSPACE
privilege is needed for all other Workspace Manager privileges.
To see which users have been granted Workspace Manager system-level privileges, examine the DBA_WM_SYS_PRIVS metadata view, which is described in Section 5.17.
To revoke system-level privileges, use the RevokeSystemPriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You do not have the privilege to grant priv_types
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example enables user Smith
to access any workspace in the database, but does not allow Smith
to grant the ACCESS_ANY_WORKSPACE
privilege to other users.
EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE', 'Smith', 'NO');
Grants workspace-level privileges to users and roles. The grant_option
parameter enables the grantee to grant the specified privileges to other 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);
Table 4-36 GrantWorkspacePriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
workspace |
Name of the workspace. The name is case sensitive. |
grantee |
Name of the user (can be the |
grant_option |
Specify |
auto_commit |
A Boolean value (
|
Contrast this procedure with GrantSystemPriv, which grants system-level Workspace Manager privileges with keywords in the form xxx_ANY_WORKSPACE (ACCESS_ANY_WORKSPACE
, MERGE_ANY_WORKSPACE
, and so on). Contrast this procedure also with GrantGraphPriv, which grants privileges on multiparent graph workspaces to users and roles.
If a user gets a privilege from more than one source and if any of those sources has the grant option for that privilege, the user has the grant option for the privilege. For example, assume that user SCOTT
has been granted the ACCESS_WORKSPACE
privilege with grant_option
as NO
, but that the PUBLIC
user group has been granted the ACCESS_WORKSPACE
privilege with grant_option
as YES
. Because user SCOTT
is a member of PUBLIC
, user SCOTT
has the ACCESS_WORKSPACE
privilege with the grant option.
The WM_ADMIN_ROLE
role has all Workspace Manager privileges with the grant option. The WM_ADMIN_ROLE
role is automatically given to the DBA
role.
The ACCESS_WORKSPACE
or ACCESS_ANY_WORKSPACE
privilege is needed for all other Workspace Manager privileges.
To revoke workspace-level privileges, use the RevokeWorkspacePriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You do not have the privilege to grant priv_types
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example enables user Smith
to access the NEWWORKSPACE
workspace and merge changes in that workspace, and allows Smith
to grant the two specified privileges on NEWWORKSPACE
to other users.
DBMS_WM.GrantWorkspacePriv ('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith', 'YES');
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);
Table 4-37 Import Procedure Parameters
Parameter | Description |
---|---|
staging_table |
Name of the table that holds the data that had previously been exported using the Export procedure. The name is not case sensitive. |
to_table |
Name of the table into which to import the data. The name is not case sensitive. |
to_workspace |
Name of the workspace in which to import the data. The name is case sensitive. |
from_workspace |
Name of the workspace from which to import the data. The name is case sensitive. If the staging table contains versioning information, you must specify |
where_clause |
The Only primary key columns can be specified in the If the |
import_scope |
The scope (amount of data) for the import operation.
|
ancestor_savepoint_workspace |
Name of the workspace containing the ancestor savepoint specified in If you specify this parameter, you must also specify |
ancestor_savepoint_name |
Name of a savepoint in If you specify this parameter, you must also specify |
apply_locks |
A Boolean value (
|
enforceUCFlag |
A Boolean value (
|
enforceRICFlag |
A Boolean value (
|
auto_commit |
A Boolean value (
|
All data that satisfies the where_clause
parameter value in the staging table named staging_table
and the import_scope
parameter value is imported into the version-enabled table named to_table
.
The data must have been previously exported to the staging table using the Export procedure.
Each row of data to be imported is considered to be one of the following: inserted, updated, or deleted in from_workspace
(that is, modified data); or data that was not modified in from_workspace
but can be seen in it (that is, ancestor data). If data is exported from the LIVE
workspace, it is all modified data.
An exception is raised if one or more of the following apply:
A specified table or workspace does not exist.
staging_table
is not in a valid format for the import operation.
to_table
is not a version-enabled table, or does not have an appropriate definition (for example, contains columns not in the staging table).
from_workspace
is null and staging_table
contains versioning information.
ancestor_savepoint_name
is not a valid savepoint in ancestor_savepoint_workspace
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example imports modified data from the staging table COLA_MARKETING_BUDGET_STG
in workspace B_focus_2
into the COLA_MARKETING_BUDGET
table in workspace B_Focus_1
. (The EXECUTE
statement is actually on a single line.)
EXECUTE DBMS_WM.Import(staging_table => 'COLA_MARKETING_BUDGET_STG', to_table => 'COLA_MARKETING_BUDGET', to_workspace => 'B_focus_1', from_workspace => 'B_focus_2');
Checks whether or not a workspace has any active sessions.
DBMS_WM.IsWorkspaceOccupied( workspace IN VARCHAR2) RETURN VARCHAR2;
Table 4-38 IsWorkspaceOccupied Function Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
This function returns YES
if the workspace has any active sessions, and it returns NO
if the workspace has no active sessions.
An exception is raised if the LIVE
workspace is specified or if the user does not have the privilege to access the workspace.
The following example checks if any sessions are in the B_focus_2
workspace.
SELECT DBMS_WM.IsWorkspaceOccupied('B_focus_2') FROM DUAL; DBMS_WM.ISWORKSPACEOCCUPIED('B_FOCUS_2') -------------------------------------------------------------------------------- YES
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);
Table 4-39 LockRows Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The latest versions of rows visible from the workspace are locked. If a row has not been modified in this workspace, the locked version could be in an ancestor workspace. The name is case sensitive. A value of |
table_name |
Name of the table or (if |
where_clause |
The Only primary key columns can be specified in the If Do not specify the |
lock_mode |
Mode with which to set the locks:
|
Xmin, Ymin |
For Oracle Spatial topologies only (see Section 1.14.1), the X and Y coordinate values, respectively, of the lower-left corner of the window containing the rows to be locked.You must specify these parameters if you specified a topology name for |
Xmax, Ymax |
For Oracle Spatial topologies only (see Section 1.14.1), the X and Y coordinate values, respectively, of the upper-right corner of the window containing the rows to be locked.You must specify these parameters if you specified a topology name for |
This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle database locking. For an explanation of Workspace Manager locking, see Section 1.3.
This procedure does not affect whether Workspace Manager locking is set on or off (determined by the SetLockingON and SetLockingOFF procedures).
To unlock rows, use the UnlockRows procedure.
For information about Workspace Manager locking for tables in an Oracle Spatial topology, see Section 1.14.1.
The following example locks rows in the EMPLOYEES
table where last_name = 'Smith'
in the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.LockRows ('NEWWORKSPACE', 'employees', 'last_name = ''Smith''');
Applies changes to a table (all rows or as specified in the WHERE
clause) in a workspace to its parent workspace.
For a multiparent workspace (explained in Section 1.1.10), applies changes to a table (all rows or as specified in the WHERE
clause) from all non-root workspaces in the directed acyclic graph to the multiparent root 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);
Table 4-40 MergeTable Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
table_id |
Name of the table containing rows to be merged into the parent workspace. The name is not case sensitive. |
where_clause |
The Only primary key columns can be specified in the If the |
create_savepoint |
A Boolean value (
|
remove_data |
A Boolean value (
|
auto_commit |
A Boolean value (
|
All data that satisfies the where_clause
parameter value in the version-enabled table named table_name
in workspace
is applied to the parent workspace of workspace
.
Any locks that are held by rows being merged are released.
If there are conflicts between the workspace being merged and its parent workspace, the merge operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)
A table cannot be merged in the LIVE
workspace (because that workspace has no parent workspace).
A table cannot be merged or refreshed if there is an open database transaction affecting the table.
An exception is raised if one or more of the following apply:
The user does not have access to table_id
.
The user does not have the MERGE_WORKSPACE
privilege for workspace
or the MERGE_ANY_WORKSPACE
privilege.
remove_data
is TRUE
and there are any child workspaces of any workspace to be removed.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The merge involving a multiparent workspace would cause the violation of a referential integrity constraint or unique constraint in any continually refreshed child workspace of the multiparent root workspace.
The following example merges changes to the EMP
table (in the USER3
schema) where last_name = 'Smith'
in NEWWORKSPACE
to its parent workspace.
EXECUTE DBMS_WM.MergeTable ('NEWWORKSPACE', 'user3.emp', 'last_name = ''Smith''');
Applies all changes in a workspace to its parent workspace, and optionally removes the workspace.
For a multiparent workspace (explained in Section 1.1.10), applies all changes in the workspace to all other workspaces in the directed acyclic graph, and optionally removes the non-root workspaces in the directed acyclic graph.
DBMS_WM.MergeWorkspace( workspace IN VARCHAR2, create_savepoint IN BOOLEAN DEFAULT FALSE, remove_workspace IN BOOLEAN DEFAULT FALSE, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-41 MergeWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
create_savepoint |
A Boolean value (
|
remove_workspace |
A Boolean value (
|
auto_commit |
A Boolean value (
|
All data in all version-enabled tables in workspace
is merged to the parent workspace of workspace
, and workspace
is removed if remove_workspace
is TRUE
.
While this procedure is executing, the current workspace is frozen in NO_ACCESS
mode and the parent workspace is frozen in READ_ONLY
mode, as explained in Section 1.1.5.
If there are conflicts between the workspace being merged and its parent workspace, the merge operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)
If the remove_workspace
parameter value is TRUE
, the workspace to be merged must be a leaf workspace, that is, a workspace with no descendant workspaces. (For an explanation of workspace hierarchy, see Section 1.1.1.)
To update rows in the child workspace and merge those changes into the parent workspace in the same transaction, you must specify autocommit=FALSE
and ensure that no other session (that is, other than the one performing the update transaction) is in the child workspace.
An exception is raised if one or more of the following apply:
The user does not have the MERGE_WORKSPACE
privilege for workspace
or the MERGE_ANY_WORKSPACE
privilege.
auto_commit
is TRUE
and there is an open database transaction in any workspace under workspace
in the workspace hierarchy.
remove_workspace
is TRUE
and there are any sessions in any workspaces under workspace
in the workspace hierarchy.
remove_workspace
is TRUE
and there are any child workspaces of any workspace to be removed.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The merge of a multiparent workspace would cause the violation of a referential integrity constraint or unique constraint in any continually refreshed child workspace of the multiparent root workspace.
The following example merges changes in NEWWORKSPACE
to its parent workspace.
EXECUTE DBMS_WM.MergeWorkspace ('NEWWORKSPACE');
Moves the Workspace Manager metadata to a specified tablespace.
DBMS_WM.Move_Proc( dest_tablespace IN VARCHAR2 DEFAULT 'SYSAUX');
Table 4-42 Move_Proc Procedure Parameters
Parameter | Description |
---|---|
dest_tablespace |
The table space to which to move the Workspace Manager metadata. The default value is the |
The Workspace Manager metadata (views, internal tables, and other objects) is by default stored in the default tablespace of the WMSYS user. You cannot directly control the size of the Workspace Manager metadata, but you can control its placement by using this procedure to move the metadata from its current tablespace to a different tablespace. If you call this procedure without specifying the dest_tablespace
parameter, the Workspace manager metadata is moved to the SYSAUX tablespace.
Before you move the metadata, you can use the GetWMMetadataSpace function to determine the approximate minimum space that you will need to have available in the tablespace into which you are considering moving the Workspace Manager metadata.
The following example moves the Workspace Manager metadata to the TBLSP_1
tablespace.
EXECUTE DBMS_WM.Move_proc('TBLSP_1');
Attempts to complete the migration process on all tables that were left in an inconsistent state after the Workspace Manager migration procedure failed.
DBMS_WM.RecoverAllMigratingTables( ignore_last_error IN BOOLEAN DEFAULT FALSE);
Table 4-43 RecoverAllMigratingTables Procedure Parameters
Parameter | Description |
---|---|
ignore_last_error |
A Boolean value (
|
If an error occurs while you are upgrading (migrating) to the current Workspace Manager release, one or more version-enabled tables can be left in an inconsistent state. (For information about upgrading to the current release, see Section B.1.) If the upgrade procedure fails, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS or ALL_WM_VT_ERRORS metadata view to see the SQL statement and error message), and then call the RecoverMigratingTable procedure (for a single table) or RecoverAllMigratingTables procedure (for all tables) with the default ignore_last_error
parameter value of FALSE
, to try to complete the upgrade process.
However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the RecoverMigratingTable or RecoverAllMigratingTables procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
The following example attempts to recover all version-enabled tables that were left in an inconsistent state when the upgrade procedure failed.
EXECUTE DBMS_WM.RecoverAllMigratingTables;
The following example attempts to recover all version-enabled tables that were left in an inconsistent state when the upgrade procedure failed, and it ignores the last error that caused the upgrade procedure to fail.
EXECUTE DBMS_WM.RecoverAllMigratingTables(TRUE);
Performs necessary operations after the dropping of one or more database users that owned one or more version-enabled tables.
DBMS_WM.RecoverFromDroppedUser( ignore_last_error IN BOOLEAN DEFAULT FALSE);
Table 4-44 RecoverFromDroppedUser Procedure Parameters
Parameter | Description |
---|---|
ignore_last_error |
A Boolean value (
|
If a database user with one or more version-enabled tables is dropped, you must execute this procedure as soon as possible. This procedure removes any foreign key constraints in existing tables that depended on any of the version-enabled tables that were dropped as a result of dropping the user that owned these tables. This procedure also fixes any invalid database metadata.
If a call to the RecoverFromDroppedUser procedure fails, the table is left in an inconsistent state. If this occurs, you should try to fix the cause of the error (examine the DBA_WM_VT_ERRORS metadata view to see the SQL statement and error message), and then call the RecoverFromDroppedUser procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the RecoverFromDroppedUser procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
To execute this procedure, you must connect to the database instance as a user with SYSDBA privileges.
The following drops a user named HERMAN
that owns one or more version-enabled tables, and then performs the necessary operations after the drop operation.
DROP USER herman CASCADE; EXECUTE DBMS_WM.RecoverFromDroppedUser;
Attempts to complete the migration process on a table that was left in an inconsistent state after the Workspace Manager migration procedure failed.
DBMS_WM.RecoverMigratingTable( table_name IN VARCHAR2, ignore_last_error IN BOOLEAN DEFAULT FALSE);
Table 4-45 RecoverMigratingTable Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table to be recovered from the migration error. The name is not case sensitive. |
ignore_last_error |
A Boolean value (
|
If an error occurs while you are upgrading to the current Workspace Manager release, one or more version-enabled tables can be left in an inconsistent state. (For information about upgrading to the current release, see Section B.1.) If the upgrade procedure fails, you should try to fix the cause of the error (examine the USER_WM_VT_ERRORS or ALL_WM_VT_ERRORS metadata view to see the SQL statement and error message), and then call the RecoverMigratingTable procedure (for a single table) or RecoverAllMigratingTables procedure (for all tables) with the default ignore_last_error
parameter value of FALSE
, to try to complete the upgrade process.
However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the RecoverMigratingTable or RecoverAllMigratingTables procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
An exception is raised if table_name
does not exist or is not version-enabled.
The following example attempts to recover the COLA_MARKETING_BUDGET
table from the error that caused the upgrade procedure to fail.
EXECUTE DBMS_WM.RecoverMigratingTable('COLA_MARKETING_BUDGET');
The following example attempts to recover the COLA_MARKETING_BUDGET
table and ignores the last error that caused the upgrade procedure to fail.
EXECUTE DBMS_WM.RecoverMigratingTable('COLA_MARKETING_BUDGET', TRUE);
Applies to a workspace all changes made to a table (all rows or as specified in the WHERE
clause) in its parent workspace.
For a multiparent workspace (explained in Section 1.1.10), applies changes from the non-leaf workspaces in the directed acyclic graph to the specified leaf workspace for a specified table. (The table data in the intermediate workspaces is not changed.)
DBMS_WM.RefreshTable( workspace IN VARCHAR2, table_id IN VARCHAR2, where_clause IN VARCHAR2 DEFAULT '', auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-46 RefreshTable Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
table_id |
Name of the table containing the rows to be refreshed using values from the parent workspace. The name is not case sensitive. |
where_clause |
The Only primary key columns can be specified in the If |
auto_commit |
A Boolean value (
|
This procedure applies to workspace
all changes in rows that satisfy the where_clause
parameter value in the version-enabled table named table_id
in the parent workspace since the time when workspace
was created or last refreshed.
If there are conflicts between the workspace being refreshed and its parent workspace, the refresh operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)
This procedure is ignored if workspace
is a continually refreshed workspace.
A table cannot be refreshed in the LIVE
workspace (because that workspace has no parent workspace).
A table cannot be merged or refreshed if there is an open database transaction affecting the table.
An exception is raised if the user does not have access to table_id
, if the user does not have the MERGE_WORKSPACE
privilege for workspace
or the MERGE_ANY_WORKSPACE
privilege, or if auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example refreshes NEWWORKSPACE
by applying changes made to the EMPLOYEES
table where last_name = 'Smith'
in its parent workspace.
EXECUTE DBMS_WM.RefreshTable ('NEWWORKSPACE', 'employees', 'last_name = ''Smith''');
Applies to a workspace all changes made in its parent workspace.
For a multiparent workspace (explained in Section 1.1.10), applies changes from the non-leaf workspaces in the directed acyclic graph to the specified leaf workspace. The changes are propagated beginning with the multiparent root workspace and continuing with the intermediate workspaces.
DBMS_WM.RefreshWorkspace( workspace IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE, copy_data IN BOOLEAN DEFAULT FALSE);
Table 4-47 RefreshWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
auto_commit |
A Boolean value (
|
copy_data |
A Boolean value (
|
This procedure applies to workspace
all changes made to version-enabled tables in the parent workspace since the time when workspace
was created or last refreshed.
If there are conflicts between the workspace being refreshed and its parent workspace, the refresh operation fails and the user must manually resolve conflicts using the <table_name>_CONF view. (Conflict resolution is explained in Section 1.1.4.)
The specified workspace and the parent workspace are frozen in READ_ONLY
mode, as explained in Section 1.1.5.
The LIVE
workspace cannot be refreshed (because it has no parent workspace).
This procedure is ignored if workspace
is a continually refreshed workspace.
An exception is raised if the user does not have the MERGE_WORKSPACE
privilege for workspace
or the MERGE_ANY_WORKSPACE
privilege, or if auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example refreshes NEWWORKSPACE
by applying changes made in its parent workspace.
EXECUTE DBMS_WM.RefreshWorkspace ('NEWWORKSPACE');
Makes one of the nonwriter sites the new writer site in a Workspace Manager replication environment. (The old writer site becomes one of the nonwriter sites.)
DBMS_WM.RelocateWriterSite( newwritersite IN VARCHAR2, oldwritersiteavailable IN BOOLEAN);
Table 4-48 RelocateWriterSite Procedure Parameters
Parameter | Description |
---|---|
newwritersite |
Name of a current nonwriter site (database link) to be made the new writer site in the Workspace Manager replication environment. |
oldwritersiteavailable |
A Boolean value (
|
To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle Database Advanced Replication and Oracle Database Advanced Replication Management API Reference.
You must execute this procedure as the replication administrator user. You can execute it at any master site.
You should specify the oldwritersiteavailable
parameter as TRUE
if the old writer site is currently available. If you specify the oldwritersiteavailable
parameter as FALSE
, you must execute the SynchronizeSite procedure after the old writer site becomes available, to bring that site up to date.
This procedure performs the following operations:
If oldwritersiteavailable
is TRUE
, disables workspace operations and DML and DDL operations for all version-enabled tables on the old writer site.
Enables workspace operations and DML and DDL operations for all version-enabled tables on the new writer site.
Invokes replication API procedures to relocate the master definition site to newwritersite
for the main master group and for the master groups for all the version-enabled tables.
The following example relocates the writer site for the Workspace Manager environment to BACKUP-SITE1
at a hypothetical company.
DBMS_WM.RelocateWriterSite( newwritersite => 'BACKUP-SITE1.ACME.COM'); oldwritersiteavailable => TRUE);
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);
Table 4-49 RemoveAsParentWorkspace Procedure Parameters
Parameter | Description |
---|---|
mp_leaf_workspace |
Name of the child workspace (multiparent leaf workspace) from which to remove |
parent_workspace |
Name of the workspace to remove as a parent workspace of |
auto_commit |
A Boolean value (
|
This procedure is part of the support for the multiparent workspaces feature, which is described in Section 1.1.10. This procedure must be used only on a parent workspace that was previously added to the child workspace using the AddAsParentWorkspace procedure.
This procedure does not remove any workspaces. It only makes parent_workspace
no longer a parent workspace of mp_leaf_workspace
.
An exception is raised if one or more of the following apply:
mp_leaf_workspace
or parent_workspace
does not exist.
mp_leaf_workspace
has versioned any data in parent_workspace
or an ancestor of parent_workspace
, and this workspace would no longer be an ancestor of mp_leaf_workspace
if the operation were to be performed.
There are any sessions with open database transactions in mp_leaf_workspace
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example removes Workspace4
as a parent workspace of Workspace3
. (See the hierarchy illustration in Figure 1-3 in Section 1.1.10.)
EXECUTE DBMS_WM.RemoveAsParentWorkspace ('Workspace3', 'Workspace4');
Discards all row versions associated with a workspace and deletes the workspace.
DBMS_WM.RemoveWorkspace( workspace IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-50 RemoveWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
auto_commit |
A Boolean value (
|
The RemoveWorkspace operation can only be performed on leaf workspaces (the bottom-most workspaces in a branch in the hierarchy). For an explanation of database workspace hierarchy, see Section 1.1.1.
There must be no other users in the workspace being removed.
An exception is raised if the user does not have the REMOVE_WORKSPACE
privilege for workspace
or the REMOVE_ANY_WORKSPACE
privilege, or if auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example removes the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.RemoveWorkspace('NEWWORKSPACE');
Discards all row versions associated with a workspace and its descendant workspaces, and deletes the affected workspaces.
DBMS_WM.RemoveWorkspaceTree( workspace IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-51 RemoveWorkspaceTree Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
auto_commit |
A Boolean value (
|
The RemoveWorkspaceTree operation should be used with extreme caution, because it removes support structures and rolls back changes in a workspace and all its descendants down to the leaf workspace or workspaces. For example, in the hierarchy shown in Figure 1-1 in Section 1.1.1, a RemoveWorkspaceTree operation specifying Workspace1
removes Workspace1
, Workspace2
, and Workspace3
. (For an explanation of database workspace hierarchy, see Section 1.1.1.)
There must be no other users in workspace
or any of its descendant workspaces.
An exception is raised if the user does not have the REMOVE_WORKSPACE
privilege for workspace
or any of its descendant workspaces, or if auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example removes the NEWWORKSPACE
workspace and all its descendant workspaces.
EXECUTE DBMS_WM.RemoveWorkspaceTree('NEWWORKSPACE');
Resolves conflicts between workspaces.
DBMS_WM.ResolveConflicts( workspace IN VARCHAR2, table_name IN VARCHAR2, where_clause IN VARCHAR2, keep IN VARCHAR2);
Table 4-52 ResolveConflicts Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace to check for conflicts with other workspaces. The name is case sensitive. |
table_name |
Name of the table to check for conflicts. The name is not case sensitive. |
where_clause |
The Only primary key columns can be specified in the |
keep |
Workspace in favor of which to resolve conflicts:
|
This procedure checks the condition identified by the table_name
and where_clause
parameters, and it finds any conflicts between row values in workspace
and its parent workspace. This procedure resolves conflicts by using the row values in the parent or child workspace, as specified in the keep
parameter; however, the conflict resolution is not actually merged until you commit the transaction (standard database commit operation) and call the CommitResolve procedure to end the conflict resolution session. (For more information about conflict resolution, including an overall view of the process, see Section 1.1.4.)
For example, assume that for Department 20 (DEPARTMENT_ID = 20
), the MANAGER_NAME
in the LIVE
and Workspace1
workspaces is Tom
. Then, the following operations occur:
The manager_name
for Department 20 is changed in the LIVE
database workspace from Tom
to Mary
.
The change is committed (a standard database commit operation).
The manager_name
for Department 20 is changed in Workspace1
from Tom
to Franco
.
The MergeWorkspace procedure is called to merge Workspace1
changes to the LIVE
workspace.
At this point, however, a conflict exists with respect to MANAGER_NAME
for Department 20 in Workspace1
(Franco
, which conflicts with Mary
in the LIVE
workspace), and therefore the call to MergeWorkspace does not succeed.
The ResolveConflicts procedure is called with the following parameters: ('Workspace1'
, 'department'
, 'department_id = 20'
, 'child'
).
After the MergeWorkspace operation in step 7, the MANAGER_NAME
value will be Franco
in both the Workspace1
and LIVE
workspaces.
The change is committed (a standard database commit operation).
The MergeWorkspace procedure is called to merge Workspace1
changes to the LIVE
workspace.
The following considerations apply during a conflict resolution session:
A ResolveConflicts operation prevents other workspace operations (such as a merge, refresh, or removal) on the target workspace or table until after the CommitResolve or RollbackResolve procedure is executed.
Multiple sessions can perform ResolveConflicts operations and perform insert, update, and delete operations on the same table. However, during such operations, the target rows are locked. If more than one session attempts to perform an insert, update, or delete operation on the same row or to resolve a conflict affecting the same row, the first session is allowed to continue; and after that session executes the CommitResolve or RollbackResolve procedure, another session is allowed to proceed.
For more information about conflict resolution, see Section 1.1.4.
The following example resolves conflicts involving rows in the DEPARTMENT
table in Workspace1
where DEPARTMENT_ID
is 20, and uses the values in the child workspace to resolve all such conflicts. It then merges the results of the conflict resolution by first committing the transaction (standard commit) and then calling the MergeWorkspace procedure.
EXECUTE DBMS_WM.BeginResolve ('Workspace1'); EXECUTE DBMS_WM.ResolveConflicts ('Workspace1', 'department', 'department_id = 20', 'child'); COMMIT; EXECUTE DBMS_WM.CommitResolve ('Workspace1');
Revokes (removes) 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);
Table 4-53 RevokeGraphPriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
leaf_workspace |
Name of the leaf workspace in the directed acyclic graph. (Leaf workspaces, directed acyclic graphs, and other concepts related to multiparent workspaces are explained in Section 1.1.10.) The name is case sensitive. |
grantee |
Name of the user (can be the |
node_types |
List of letters (in parentheses and comma-delimited) representing the types of nodes on which to revoke the privileges: |
auto_commit |
A Boolean value (
|
Contrast this procedure with RevokeWorkspacePriv, which grants workspace-level Workspace Manager privileges on workspaces other than multiparent graph workspaces.
To grant workspace-level privileges on multiparent graph workspaces, use the GrantGraphPriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You were not the grantor of priv_types
to grantee
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example disallows user Smith
from accessing all types of nodes in the directed acyclic graph in which the NEWWORKSPACE
workspace is the leaf workspace and from merging changes in these workspaces.
EXECUTE DBMS_WM.RevokeWorkspacePriv ('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith');
Revokes (removes) system-level privileges from users and roles.
DBMS_WM.RevokeSystemPriv( priv_types IN VARCHAR2, grantee IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-54 RevokeSystemPriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
grantee |
Name of the user (can be the |
auto_commit |
A Boolean value (
|
Contrast this procedure with RevokeWorkspacePriv, which revokes workspace-level Workspace Manager privileges with keywords in the form xxx_WORKSPACE (ACCESS_WORKSPACE
, MERGE_WORKSPACE
, and so on).
To grant system-level privileges, use the GrantSystemPriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You were not the grantor of priv_types
to grantee
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example disallows user Smith
from accessing workspaces and merging changes in workspaces.
EXECUTE DBMS_WM.RevokeSystemPriv ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE', 'Smith');
Revokes (removes) 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);
Table 4-55 RevokeWorkspacePriv Procedure Parameters
Parameter | Description |
---|---|
priv_types |
A string of one or more keywords representing privileges. (Section 1.4 discusses Workspace Manager privileges.) Use commas to separate privilege keywords. The available keywords are |
workspace |
Name of the workspace. The name is case sensitive. |
grantee |
Name of the user (can be the |
auto_commit |
A Boolean value (
|
Contrast this procedure with RevokeSystemPriv, which revokes system-level Workspace Manager privileges with keywords in the form xxx_ANY_WORKSPACE (ACCESS_ANY_WORKSPACE
, MERGE_ANY_WORKSPACE
, and so on). Also contrast this procedure with RevokeGraphPriv, which grants workspace-level Workspace Manager privileges on multiparent graph workspaces
To grant workspace-level privileges, use the GrantWorkspacePriv procedure.
An exception is raised if one or more of the following apply:
grantee
is not a valid user or role in the database.
You were not the grantor of priv_types
to grantee
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example disallows user Smith
from accessing the NEWWORKSPACE
workspace and merging changes in that workspace.
EXECUTE DBMS_WM.RevokeWorkspacePriv ('ACCESS_WORKSPACE, MERGE_WORKSPACE', 'NEWWORKSPACE', 'Smith');
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);
Table 4-56 RollbackBulkLoading Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table into which data will be bulk loaded. The name is not case sensitive. |
ignore_last_error |
A Boolean value (
|
For information about the requirements for bulk loading data into version-enabled tables, see Section 1.7.
This procedure re-creates all the views that were dropped by the BeginBulkLoading procedure.
If a call to the RollbackBulkLoading procedure fails, you should try to fix the cause of the error. Examine the USER_WM_VT_ERRORS and ALL_WM_VT_ERRORS metadata views to see the SQL statement and error message. Fix the cause of the error, and then call the RollbackBulkLoading procedure again with the default ignore_last_error
parameter value of FALSE
. However, if the call still fails and you cannot fix the cause of the error, and if you are sure that it is safe and appropriate to ignore this error, then you have the option to ignore the error by calling the RollbackBulkLoading procedure with the ignore_last_error
parameter value of TRUE
. Note that you are responsible for ensuring that it is safe and appropriate to ignore the error.
An exception is raised if one or more of the following apply:
table_name
does not exist.
table_name
is not version-enabled.
The BeginBulkLoading procedure has not been called on the table.
The user does not own the table or does not have the WM_ADMIN_ROLE
role.
The following example rolls back changes made to EMP
table during a bulk load operation.
EXECUTE DBMS_WM.RollbackBulkLoading ('EMP');
Rolls back (cancels) DDL (data definition language) changes made during a DDL session for a specified table, and ends the DDL session.
DBMS_WM.RollbackDDL( table_name IN VARCHAR2);
Table 4-57 RollbackDDL Procedure Parameters
Parameter | Description |
---|---|
table_name |
Name of the version-enabled table. The name is not case sensitive. |
This procedure rolls back (cancels) changes that were made to a version-enabled table and to any indexes and triggers based on the version-enabled table during a DDL session. It also deletes the <table-name>_LTS skeleton table that was created by the BeginDDL procedure.
For detailed information about performing DDL operations related to version-enabled tables, see Section 1.8; and for DDL operations on version-enabled tables in an Oracle replication environment, see also Section C.3.
An exception is raised if one or more of the following apply:
table_name
does not exist or is not version-enabled.
An open DDL session does not exist for table_name
. (That is, the BeginDDL procedure has not been called specifying this table, or the CommitDDL or RollbackDDL procedure was already called specifying this table.)
The following example begins a DDL session, adds a column named COMMENTS
to the COLA_MARKETING_BUDGET
table by using the skeleton table named COLA_MARKETING_BUDGET_LTS
, and ends the DDL session by canceling the change.
EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET'); ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100)); EXECUTE DBMS_WM.RollbackDDL('COLA_MARKETING_BUDGET');
Quits a conflict resolution session and discards all changes in the workspace since the BeginResolve procedure was executed.
DBMS_WM.RollbackResolve( workspace IN VARCHAR2);
Table 4-58 RollbackResolve Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
This procedure quits the current conflict resolution session (started by the BeginResolve procedure), and discards all changes in the workspace since the start of the conflict resolution session. Contrast this procedure with CommitResolve, which saves all changes.
While the conflict resolution session is being rolled back, the workspace is frozen in 1WRITER
mode, as explained in Section 1.1.5.
For more information about conflict resolution, see Section 1.1.4.
An exception is raised if one or more of the following apply:
There are one or more open database transactions in workspace
.
The procedure was called by a user that does not have the WM_ADMIN_ROLE
role or that did not execute the BeginResolve procedure on workspace
.
The following example quits the conflict resolution session in Workspace1
and discards all changes.
EXECUTE DBMS_WM.RollbackResolve ('Workspace1');
Discards all changes made in the workspace to a 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);
Table 4-59 RollbackTable Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
table_id |
Name of the table containing rows to be discarded. The name is not case sensitive. |
sp_name |
Name of the savepoint to which to roll back. The name is case sensitive. The default is to discard all changes (that is, ignore any savepoints). |
where_clause |
The Only primary key columns can be specified in the If |
remove_locks |
A Boolean value (
|
auto_commit |
A Boolean value (
|
You cannot roll back to a savepoint if any implicit savepoints were created since the specified savepoint, unless you first merge or remove the descendant workspaces that caused the implicit savepoints to be created. For example, referring to Figure 1-2 in Section 1.1.2, the user in Workspace1 cannot roll back to savepoint SP1 until Workspace3 (which caused implicit savepoint SPc to be created) is merged or removed.
An exception is raised if one or more of the following apply:
workspace
does not exist.
You do not have the privilege to roll back workspace
or any affected table.
A database transaction affecting table_id
is open in workspace
.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
The following example rolls back all changes made to the EMP
table (in the USER3
schema) in the NEWWORKSPACE
workspace since that workspace was created.
EXECUTE DBMS_WM.RollbackTable ('NEWWORKSPACE', 'user3.emp');
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);
Table 4-60 RollbackToSP Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
savepoint_name |
Name of the savepoint to which to roll back changes. The name is case sensitive. |
auto_commit |
A Boolean value (
|
While this procedure is executing, the workspace is frozen in NO_ACCESS
mode.
Contrast this procedure with RollbackWorkspace, which rolls back all changes made since the creation of the workspace.
You cannot roll back to a savepoint if any implicit savepoints were created since the specified savepoint, unless you first merge or remove the descendant workspaces that caused the implicit savepoints to be created. For example, referring to Figure 1-2 in Section 1.1.2, the user in Workspace1
cannot roll back to savepoint SP1
until Workspace3
(which caused implicit savepoint SPc
to be created) is merged or removed.
An exception is raised if one or more of the following apply:
workspace
does not exist.
savepoint_name
does not exist.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
One or more implicit savepoints were created in workspace
after savepoint_name
, and the descendant workspaces that caused the implicit savepoints to be created still exist.
You do not have the privilege to roll back workspace
or any affected table.
Any sessions are in workspace
.
The following example rolls back any changes made in the NEWWORKSPACE
workspace to all tables since the creation of Savepoint1
.
EXECUTE DBMS_WM.RollbackToSP ('NEWWORKSPACE', 'Savepoint1');
Discards all data changes made in the workspace to version-enabled tables.
DBMS_WM.RollbackWorkspace( workspace IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-61 RollbackWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
auto_commit |
A Boolean value (
|
Only leaf workspaces can be rolled back. That is, a workspace cannot be rolled back if it has any descendant workspaces. (For an explanation of workspace hierarchy, see Section 1.1.1.)
Contrast this procedure with RollbackToSP, which rolls back changes to a specified savepoint.
Like the RemoveWorkspace procedure, RollbackWorkspace deletes the data in the workspace; however, unlike the RemoveWorkspace procedure, RollbackWorkspace does not delete the Workspace Manager workspace structure.
While this procedure is executing, the specified workspace is frozen in NO_ACCESS
mode, as explained in Section 1.1.5.
An exception is raised if one or more of the following apply:
workspace
has any descendant workspaces.
workspace
does not exist.
auto_commit
is TRUE
and an open transaction exists in a parent or child workspace of any table that needs to be modified.
You do not have the privilege to roll back workspace
or any affected table.
Any sessions are in workspace
.
The following example rolls back any changes made in the NEWWORKSPACE
workspace since that workspace was created.
EXECUTE DBMS_WM.RollbackWorkspace ('NEWWORKSPACE');
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');
Table 4-62 SetCaptureEvent Procedure Parameters
Parameter | Description |
---|---|
event_name |
One of the following values:
|
capture |
|
For information about Workspace Manager events, see Chapter 2.
This procedure requires that the Workspace Manager system parameter ALLOW_CAPTURE_EVENTS
be set to ON
. To check the value of a Workspace Manager system parameter, use the GetSystemParameter procedure; to set a Workspace Manager system parameter, use the SetSystemParameter procedure.
You can use this procedure to control which types of events are captured. For example, you can enable the capture of all events, and then disable the capture of a few types of events; or you can disable the capture of all events, and then enable the capture of a few types of events.
To see which types of events are currently being captured, examine the WM_EVENTS_INFO metadata view, which is described in Section 5.39.
If this procedure completes successfully, it commits the caller's open database transaction.
An exception is raised if one or more of the following apply:
You do not have WM_ADMIN_ROLE
role.
The value of the ALLOW_CAPTURE_EVENTS
system parameter is OFF
and you are trying to set event_name
to ON
(the default value for that parameter).
event_name
is not valid.
The following example captures all Workspace Manager events except workspace compression events, by first specifying that all events are to be captured, and then excluding workspace compression events.
-- Allow Workspace Manager events to be captured. (Required for SetCaptureEvent) EXECUTE DBMS_WM.SetSystemParameter ('ALLOW_CAPTURE_EVENTS', 'ON'); -- Start capturing all Workspace Manager events. EXECUTE DBMS_WM.SetCaptureEvent ('ALL_EVENTS','ON'); -- Exclude workspace compression events. EXECUTE DBMS_WM.SetCaptureEvent ('WORKSPACE_COMPRESS','OFF');
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);
Table 4-63 SetCompressWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
firstSP |
Savepoint on the first version of the compression range. Savepoint names are case sensitive. If only If If only |
secondSP |
Savepoint on the first version of the compression range. All rows in version-enabled tables from |
You can (but do not need to) use this procedure before calling the CompressWorkspace or CompressWorkspaceTree procedure.
This procedure creates rows in the WM_COMPRESSIBLE_TABLES metadata view (described in WM_COMPRESSIBLE_TABLES) only for version-enabled tables that would need to be compressed during a workspace compression operation.
The following example creates rows in the WM_COMPRESSIBLE_TABLES metadata view for any version-enabled tables that would need to be compressed during an operation that compressed the B_focus_1
workspace.
EXECUTE DBMS_WM.SetCompressWorkspace ('B_focus_1');
Determines whether or not conflicts exist between a workspace and its parent.
DBMS_WM.SetConflictWorkspace( workspace IN VARCHAR2);
Table 4-64 SetConflictWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
This procedure checks for any conflicts between workspace
and its parent workspace, and it modifies the content of the <table_name>_CONF views (explained in Section 5.42) as needed.
A SELECT
operation from the <table_name>_CONF views for all tables modified in a workspace displays all rows in the workspace that are in conflict with the parent workspace. (To obtain a list of tables that have conflicts for the current conflict workspace setting, use the SQL statement SELECT * FROM ALL_WM_VERSIONED_TABLES WHERE conflict = 'YES';
. The SQL statement SELECT * FROM <table_name>_CONF
displays conflicts for <table_name> between the current workspace and its parent workspace.)
Any conflicts must be resolved before a workspace can be merged or refreshed. To resolve a conflict, you must use the ResolveConflicts procedure, and then merge the result of the resolution by using the MergeWorkspace procedure.
The following example checks for any conflicts between B_focus_2
and its parent workspace, and modifies the contents of the <table_name>_CONF views as needed.
EXECUTE DBMS_WM.SetConflictWorkspace ('B_focus_2');
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.
DBMS_WM.SetDiffVersions( workspace1 IN VARCHAR2, workspace2 IN VARCHAR2);
or
DBMS_WM.SetDiffVersions( workspace1 IN VARCHAR2, savepoint1 IN VARCHAR2, workspace2 IN VARCHAR2, savepoint2 IN VARCHAR2);
Table 4-65 SetDiffVersions Procedure Parameters
Parameter | Description |
---|---|
workspace1 |
Name of the first workspace to be checked for differences in version-enabled tables. The name is case sensitive. |
savepoint1 |
Name of the savepoint in If |
workspace2 |
Name of the second workspace to be checked for differences in version-enabled tables. The name is case sensitive. |
savepoint2 |
Name of the savepoint in |
This procedure modifies the contents of the differences views (xxx_DIFF), which are described in Section 5.43. Each call to the procedure populates one or more sets of three rows, each set consisting of:
Values for the common ancestor
Values for workspace1
(savepoint1
or LATEST
savepoint values)
Values for workspace2
(savepoint2
or LATEST
savepoint values)
You can then select rows from the appropriate xxx_DIFF view or views to check comparable table values in the two savepoints and their common ancestor. The common ancestor (or base) is identified as DiffBase
in xxx_DIFF view rows.
The following example checks the differences in version-enabled tables for the B_focus_1
and B_focus_2
workspaces. (The output has been reformatted for readability.)
-- Add rows to difference view: COLA_MARKETING_BUDGET_DIFF EXECUTE DBMS_WM.SetDiffVersions ('B_focus_1', 'B_focus_2'); -- View the rows that were just added. SELECT * from COLA_MARKETING_BUDGET_DIFF; PRODUCT_ID PRODUCT_NAME MANAGER BUDGET WM_DIFFVER WMCODE ---------- ------------ ------- ------ ----------- -------- 1 cola_a Alvarez 2 DiffBase NC 1 cola_a Alvarez 1.5 B_focus_1, LATEST U 1 cola_a Alvarez 2 B_focus_2, LATEST NC 2 cola_b Burton 2 DiffBase NC 2 cola_b Beasley 3 B_focus_1, LATEST U 2 cola_b Burton 2.5 B_focus_2, LATEST U 3 cola_c Chen 1.5 DiffBase NC 3 cola_c Chen 1 B_focus_1, LATEST U 3 cola_c Chen 1.5 B_focus_2, LATEST NC 4 cola_d Davis 3.5 DiffBase NC 4 cola_d Davis 3 B_focus_1, LATEST U 4 cola_d Davis 2.5 B_focus_2, LATEST U 12 rows selected.
Section 5.43 explains how to interpret and use the information in the differences (xxx_DIFF) views.
Disables Workspace Manager locking for the current session.
DBMS_WM.SetLockingOFF();
None.
This procedure turns off Workspace Manager locking that was set on by the SetLockingON procedure. Existing locks applied by this session remain locked. All new changes by this session are not locked.
The following example sets locking off for the session.
EXECUTE DBMS_WM.SetLockingOFF;
Enables Workspace Manager locking for the current session.
DBMS_WM.SetLockingON( lockmode IN VARCHAR2);
Table 4-66 SetLockingON Procedure Parameters
Parameter | Description |
---|---|
lockmode |
Locking mode. Must be
|
This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle database locking. Workspace Manager locks can be used to prevent conflicts. When a user locks a row, the corresponding row in the parent workspace is also locked. Thus, when this workspace merges with the parent at merge time, it is guaranteed that this row will not have a conflict.
For information about Workspace Manager lock management, see Section 1.3.
Exclusive locking (lockmode
value of E
) prevents the use of what-if scenarios in which different values for one or more columns are tested. Thus, plan any testing of scenarios when exclusive locking is not in effect.
Locking is enabled at the user session level, and the locking mode stays in effect until any of the following occurs:
The session goes to another workspace or connects to the database, in which case the locking mode is set to C
(carry-forward) unless another locking mode has been specified using the SetWorkspaceLockModeON procedure.
The session executes the SetLockingOFF procedure.
The locks remain in effect for the duration of the workspace, unless unlocked by the UnlockRows procedure. (Existing locks are not affected by the SetLockingOFF procedure.)
There are no specific privileges associated with locking. Any session that can go to a workspace can set locking on.
The following example sets exclusive locking on for the session.
EXECUTE DBMS_WM.SetLockingON ('E');
All rows locked by this user remain locked until the workspace is merged or rolled back.
Makes the specified workspace or workspaces visible in the multiworkspace views for version-enabled tables.
DBMS_WM.SetMultiWorkspaces( workspaces IN VARCHAR2);
Table 4-67 SetMultiWorkspaces Procedure Parameters
Parameter | Description |
---|---|
workspaces |
The workspace or workspaces for which information is to be added to the multiworkspace views (described in Section 5.46). The workspace names are case sensitive. To specify more than one workspace (but no more than eight), use a comma to separate workspace names. For example: |
This procedure adds rows to the multiworkspace views (xxx_MW). See Section 5.46 for information about the contents and uses of these views.
To see the names of workspaces visible in the multiworkspace views, use the GetMultiWorkspaces function.
An exception is raised if one or more of the following apply:
The user does not have the privilege to go to one or more of the workspaces named in workspaces
.
A workspace named in workspaces
is not valid.
The following example adds information to the multiworkspace views for version-enabled tables in the B_focus_1
workspace.
EXECUTE DBMS_WM.SetMultiWorkspaces ('B_focus_1');
The following example shows the use of the SetMultiWorkspaces procedure to view information without leaving the current workspace, and the use of the GotoWorkspace procedure to view the same information.
-- These two pairs of statements select the same information. EXECUTE DBMS_WM.SetMultiWorkspaces ('myworkspace'); SELECT * from mytable_mw; EXECUTE DBMS_WM.GotoWorkspace ('myworkspace'); SELECT * from mytable;
To select only the rows modified in myworkspace
, change the first SELECT
statement in the preceding example to the following:
SELECT * from mytable_mw WHERE wm_modified_by = 'myworkspace';
The following example shows the latest rows in the combined ancestor versions of the workspaces named myworkspace
and yourworkspace
. If the same row is selected from more than workspace, that row is shown only once. Note that there may be more than one row for a primary key because different workspaces might be selecting different versions of the primary key.
EXECUTE DBMS_WM.SetMultiWorkspaces ('myworkspace,yourworkspace'); SELECT * from mytable_mw;
Sets the value of a Workspace Manager system parameter.
DBMS_WM.SetSytstemParameter( name IN VARCHAR2, value IN VARCHAR2);
Table 4-68 SetSystemParameter Procedure Parameters
Parameter | Description |
---|---|
name |
Name of the Workspace Manager system parameter for which to set the value. The name must be one of the following: |
value |
Value for the specified Workspace Manager system parameter, as explained in Table 1-5 in Section 1.5 |
For information about Workspace Manager system parameters, see Section 1.5.
If this procedure completes successfully, it commits the caller's open database transaction.
An exception is raised if one or more of the following apply:
The user does not have the WM_ADMIN_ROLE
role.
The system parameter name is not valid.
The value is not valid for the system parameter.
You tried to disallow capturing of events, and one or more types of events were being captured. You must first disable the capturing of all events (for example, by calling the SetCaptureEvent procedure and specifying ALL_EVENTS
for event_type
and OFF
for capture
).
You tried to disallow multiparent workspaces, and one or more multiparent workspaces already existed. You must first ensure that all workspaces have no more than one parent workspace (for example, by calling the RemoveAsParentWorkspace procedure as needed).
You tried to disallow nested table columns, and one or more tables with a nested table column were version-enabled. You must first disable versioning on all tables with nested table columns.
You tried to change CR_WORKSPACE_MODE
or NONCR_WORKSPACE_MODE
to PESSIMISTIC_LOCKING
, and data exists in a non-LIVE
workspace for the corresponding type of workspace (continually refreshed or not continually refreshed).
The following example allows multiparent workspaces (described in Section 1.1.10) to be created.
EXECUTE DBMS_WM.SetSystemParameter ('ALLOW_MULTI_PARENT_WORKSPACES', 'ON');
Enables the execution of a trigger for a specified set of triggering events. The trigger will not be executed for events not specified
DBMS_WM.SetTriggerEvents( triggerName IN VARCHAR2, triggerEvents IN VARCHAR2);
Table 4-69 SetTriggerEvents Procedure Parameters
Parameter | Description |
---|---|
triggerName |
Name of the trigger for which to set one or more events. |
triggerEvents |
A comma-delimited list of trigger event names, where each trigger event name is one of the following string constants:
|
For information about using triggers with Workspace Manager, see Section 1.10.
By default, user-defined triggers are executed for both DML and workspace events, unless the default behavior is changed by using the Workspace Manager system parameter FIRE_TRIGGERS_FOR_NONDML_EVENTS
(described in Section 1.5). You can use the SetTriggerEvents
procedure to override the current FIRE_TRIGGERS_FOR_NONDML_EVENTS
setting for specific triggers; however, if you later change the value of the FIRE_TRIGGERS_FOR_NONDML_EVENTS
system parameter, this new value overrides any setting previously specified using the SetTriggerEvents
procedure.
If this procedure completes successfully, it commits the caller's open database transaction.
An exception is raised if one or more of the following apply:
The user is not the trigger owner or does not have the WM_ADMIN_ROLE
role.
triggerName
does not exist.
One of more triggerEvents
values are not valid.
The following example enables the trigger SCOTT.InsertTrigger
only for DML events.
EXECUTE DBMS_WM.setTriggerEvents('SCOTT.InsertTrigger', DBMS_WM.DML);
The following example enables the trigger SCOTT.InsertTrigger
for DML events and table merge operations.
EXECUTE DBMS_WM.setTriggerEvents('SCOTT.InsertTrigger', dbms_wm.DML || ',' || dbms_wm.TABLE_MERGE_WO_REMOVE_DATA || ',' || dbms_wm.TABLE_MERGE_W_REMOVE_DATA);
Sets the session valid time period. (Valid time support is described in Chapter 3.)
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);
Table 4-70 SetValidTime Procedure Parameters
Parameter | Description |
---|---|
validFrom |
The start of the session valid time period. The default value is the current timestamp value. |
validTill |
The end of the session valid time period. The default is that the time remains valid until the session valid time is changed. |
For information about Workspace Manager valid time support, see Chapter 3. Section 3.2 explains how validFrom
and validTill
values are interpreted.
If this procedure is not invoked in the session or if it is invoked with no parameters, all rows that are valid at the current time are considered valid, and the valid time period is considered to be from the current time forward without limit.
The following example sets the session valid time to include all of the year 2003.
EXECUTE DBMS_WM.SetValidTime(TO_DATE('01-01-2003', 'MM-DD-YYYY'), TO_DATE('01-01-2004', 'MM-DD-YYYY'));
Removes the valid time filter for the current session.
DBMS_WM.SetWMValidTimeFilterOFF();
None.
This procedure reverses the effect of theSetWMValidTimeFilterON procedure, and causes the previously defined valid time filter to be ignored for queries against tables with valid time support. Workspace Manager valid time support is explained in Chapter 3.
See also the Usage Notes for the SetWMValidTimeFilterON procedure.
The following example removes the valid time filter for the current session.
EXECUTE DBMS_WM.SetWMValidTimeFilterOFF;
Sets a valid time filter for the current session (that is, a time to be applied to version-enabled tables.
DBMS_WM.SetValidTimeFilterON( filtertime IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);
Table 4-71 SetValidTimeFilterON Procedure Parameters
Parameter | Description |
---|---|
filtertime |
Date to be used as a filter when querying version-enabled tables that have valid time support. The default value is the current time; that is, each select operation on a version-enabled table with valid time support returns data that is valid as of the current time. |
A valid time filter is a time that is applied to queries against version-enabled tables that have valid time support. When a valid time filter is set for the current session, only rows that are valid for the specified time are returned. Workspace Manager valid time support is explained in Chapter 3.
The purpose for setting a valid time filter is usually to work with only one row for a given primary key value. For example, assume that for the current valid time period, the session has two rows for employee Adams: the first row is valid from 01-Mar-2004 to 30-Apr-2005, and the second row is valid from 01-May-2005 until it is changed. If you set the valid time filter to 01-Jan-2005 and select all rows for Adams, only the first row (the one valid from 01-Mar-2004 to 30-Apr-2005) is returned. If you remove the valid time filter and select all rows for Adams, both rows are returned.
The filtertime
value must be in the valid time range for the session. You can set the valid time range using the SetValidTime procedure.
The following example sets a valid time filter so that for queries against version-enabled tables with valid time support, only rows that are valid on January 1, 2005 are returned.
EXECUTE DBMS_WM.SetValidTimeFilterOn(TO_DATE('2005-01-01', 'yyyy-mm-dd'));
Disables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support.
DBMS_WM.SetWMValidUpdateModeOFF();
None.
This procedure disables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support. Workspace Manager valid time support is explained in Chapter 3; sequenced and nonsequenced update operations and sequenced delete operations are explained in Section 3.6.2.1.
When sequenced update and delete operations are enabled, when an update or delete operation is performed on a table with valid time support, the session's current valid time period is used so that only rows valid during that period are updated or deleted. However, calling the SetWMValidUpdateModeOFF procedure enables all row data to be updated or deleted, regardless of the valid time period, and causes WM_VALID column values in the table not to be updated. (This procedure does not affect insert or query operations on tables with valid time support.)
See also the Usage Notes for the SetWMValidUpdateModeON procedure.
The following example disables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support.
EXECUTE DBMS_WM.SetWMValidUpdateModeOFF;
Enables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support.
DBMS_WM.SetWMValidUpdateModeON();
None.
This procedure enables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support. Sequenced update and delete operations are enabled when a table is version-enabled with valid time support or when valid time support is added to a version-enabled table; however, sequenced update and delete operations can be disabled using the SetWMValidUpdateModeOFF procedure.
Workspace Manager valid time support is explained in Chapter 3; sequenced and nonsequenced update operations and sequenced delete operations are explained in Section 3.6.2.2.
The following example enables sequenced and nonsequenced update operations and sequenced delete operations on tables that have valid time support. It reverses the effect of the SetWMValidUpdateModeOFF procedure.
EXECUTE DBMS_WM.SetWMValidUpdateModeON;
Disables the VIEW_WO_OVERWRITE
history option that was enabled by the EnableVersioning or SetWoOverwriteON procedure, changing the option to VIEW_W_OVERWRITE
(with overwrite).
DBMS_WM.SetWoOverwriteOFF();
None.
This procedure affects the recording of history information in the views named <table_name>_HIST by changing the VIEW_WO_OVERWRITE
option to VIEW_W_OVERWRITE
. That is, from this point forward, the views show only the most recent modifications to the same version of the table. A history of modifications to the version is not maintained; that is, subsequent changes to a row in the same version overwrite earlier changes.
This procedure affects only tables that were version-enabled with the hist
parameter set to VIEW_WO_OVERWRITE
in the call to the EnableVersioning procedure.
The <table_name>_HIST views are described in Section 5.44. The VIEW_WO_OVERWRITE
and VIEW_W_OVERWRITE
options are further described in the description of the EnableVersioning procedure.
The history option affects the behavior of the GotoDate procedure. See the Usage Notes for that procedure.
The result of the SetWoOverwriteOFF procedure remains in effect only for the duration of the current session. To reverse the effect of this procedure, use the SetWoOverwriteON procedure.
The following example disables the VIEW_WO_OVERWRITE
history option.
EXECUTE DBMS_WM.SetWoOverwriteOFF;
Enables the VIEW_WO_OVERWRITE
history option that was disabled by the SetWoOverwriteOFF procedure.
DBMS_WM.SetWoOverwriteON();
None.
This procedure affects the recording of history information in the views named <table_name>_HIST by changing the VIEW_W_OVERWRITE
option to VIEW_WO_OVERWRITE
(without overwrite). That is, from this point forward, the views show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.
This procedure affects only tables that were affected by a previous call to the SetWoOverwriteOFF procedure.
The <table_name>_HIST views are described in Section 5.44. The VIEW_WO_OVERWRITE
and VIEW_W_OVERWRITE
options are further described in the description of the EnableVersioning procedure.
The VIEW_WO_OVERWRITE
history option can be overridden when a workspace is compressed by specifying the compress_view_wo_overwrite
parameter as TRUE
with the CompressWorkspace or CompressWorkspaceTree procedure.
The history option affects the behavior of the GotoDate procedure. See the Usage Notes for that procedure.
To reverse the effect of this procedure, use the SetWoOverwriteOFF procedure.
The following example enables the VIEW_WO_OVERWRITE
history option.
EXECUTE DBMS_WM.SetWoOverwriteON;
Disables Workspace Manager locking for the specified workspace.
DBMS_WM.SetWorkspaceLockModeOFF( workspace IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Table 4-72 SetWorkspaceLockModeOFF Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace for which to set the locking mode off. The name is case sensitive. |
auto_commit |
A Boolean value (
|
This procedure turns off Workspace Manager locking that was set on by the SetWorkspaceLockModeON procedure. Existing locks applied by this session remain locked. All new changes by this session or a subsequent session are not locked, unless the session turns locking on by executing the SetLockingON procedure.
An exception is raised if any of the following occurs:
The user does not have the WM_ADMIN_ROLE
role or is not the owner of workspace
.
auto_commit
is TRUE
and an open transaction exists.
workspace
is a continually refreshed workspace (see the description of the isrefreshed
parameter of the CreateWorkspace procedure).
The following example sets locking off for the workspace named NEWWORKSPACE
.
EXECUTE DBMS_WM.SetWorkspaceLockModeOFF('NEWWORKSPACE');
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);
Table 4-73 SetWorkspaceLockModeON Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace for which to enable Workspace Manager locking. The name is case sensitive. |
lockmode |
Default locking mode for row-level locking. Must be
|
override |
A Boolean value (
|
auto_commit |
A Boolean value (
|
This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle database locking. Workspace Manager locks can be used to prevent conflicts. When a user locks a row, the corresponding row in the parent workspace is also locked. Thus, when this workspace merges with the parent at merge time, it is guaranteed that this row will not have a conflict.
For information about Workspace Manager lock management, see Section 1.3.
Exclusive locking (lockmode
value of E
) prevents the use of what-if scenarios in which different values for one or more columns are tested. Thus, plan any testing of scenarios when exclusive locking is not in effect.
If the override parameter value is TRUE
, locking can also be enabled and disabled at the user session level with the SetLockingON and SetLockingOFF procedures, respectively.
All new changes by this session or a subsequent session are locked, unless the session turns locking off by executing the SetLockingOFF procedure.
An exception is raised if any of the following occurs:
The user does not have the WM_ADMIN_ROLE
role or is not the owner of workspace
.
auto_commit
is TRUE
and an open transaction exists.
workspace
is a continually refreshed workspace (see the description of the isrefreshed
parameter of the CreateWorkspace procedure).
The following example sets exclusive locking on for the workspace named NEWWORKSPACE
.
EXECUTE DBMS_WM.SetWorkspaceLockModeON ('NEWWORKSPACE', 'E');
All locked rows remain locked until the workspace is merged or rolled back.
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 procedure.
DBMS_WM.SynchronizeSite( newwritersite IN VARCHAR2);
Table 4-74 SynchronizeSite Procedure Parameters
Parameter | Description |
---|---|
newwritersite |
Name of the new writer site (database link) with which the local site needs to be brought up to date. |
To use this procedure, you must understand how replication applies to Workspace Manager objects, as explained in Appendix C. You must also understand the major Oracle replication concepts and techniques, which are documented in Oracle Database Advanced Replication and Oracle Database Advanced Replication Management API Reference.
You must execute this procedure as the replication administrator user.
You must execute this procedure on the old writer site if you specified the oldwritersiteavailable
parameter as FALSE
when you executed the RelocateWriterSite procedure.
The following example brings the local system up to date with the new writer site (BACKUP-SITE1.ACME.COM
) in the Workspace Manager replication environment.
DBMS_WM.SynchronizeSite('BACKUP-SITE1.ACME.COM');
Enables access and changes to a workspace, reversing the effect of the FreezeWorkspace procedure.
DBMS_WM.UnfreezeWorkspace( workspace IN VARCHAR2);
Table 4-75 UnfreezeWorkspace Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace. The name is case sensitive. |
The operation fails if any sessions are in workspace
.
You can unfreeze a workspace only if one or more of the following apply:
You are the owner of the specified workspace.
You have the WM_ADMIN_ROLE
, the FREEZE_ANY_WORKSPACE
privilege, or the FREEZE_WORKSPACE
privilege for the specified workspace.
The following example unfreezes the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.UnfreezeWorkspace ('NEWWORKSPACE');
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);
Table 4-76 UnlockRows Procedure Parameters
Parameter | Description |
---|---|
workspace |
Name of the workspace: locked rows in this workspace and corresponding rows in the parent workspace will be unlocked, as specified in the remaining parameters. The name is case sensitive. A value of |
table_name |
Name of the table or (if |
where_clause |
The Only primary key columns can be specified in the If the Do not specify the |
all_or_user |
Scope of the request:
|
lock_mode |
Locking mode:
|
Xmin, Ymin |
For Oracle Spatial topologies only (see Section 1.14.1), the X and Y coordinate values, respectively, of the lower-left corner of the window containing the rows to be locked.You must specify these parameters if you specified a topology name for |
Xmax, Ymax |
For Oracle Spatial topologies only (see Section 1.14.1), the X and Y coordinate values, respectively, of the upper-right corner of the window containing the rows to be locked.You must specify these parameters if you specified a topology name for |
This procedure affects Workspace Manager locking, which occurs in addition to any standard Oracle database locking. For an explanation of Workspace Manager locking, see Section 1.3.
This procedure unlocks rows that were previously locked (see the LockRows procedure). It does not affect whether Workspace Manager locking is set on or off (determined by the SetLockingON and SetLockingOFF procedures).
For information about Workspace Manager locking for tables in an Oracle Spatial topology, see Section 1.14.1.
The following example unlocks the EMPLOYEES
table where last_name = 'Smith'
in the NEWWORKSPACE
workspace.
EXECUTE DBMS_WM.UnlockRows ('employees', 'NEWWORKSPACE', 'last_name = ''Smith''');
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);
Table 4-77 UseDefaultValuesForNulls Procedure Parameters
Parameter | Description |
---|---|
mode_var |
Mode for handling the insertion of null values:
|
This procedure affects what Workspace Manager does only if an INSERT statement into a version-enabled table explicitly specifies NULL
for a column when the column has been defined as having a default value. For example, assume the following table definition:
CREATE TABLE players (name VARCHAR2(20), rating NUMBER DEFAULT 10);
If the PLAYERS
table is version-enabled and if you have not executed this procedure with a mode_val
parameter value of OFF
, the following statement inserts a row for Smith
with a null RATING
value:
INSERT INTO players VALUES ('Smith', NULL);
However, if you have executed the UseDefaultValuesForNulls procedure with a mode_val
parameter value of ON
, that statement inserts a row for Smith
with a RATING
value of 10.
If the INSERT statement does not specify a value for a column that has a default value, the default value is inserted regardless of whether or not you previously called the UseDefaultValuesForNulls procedure or what the mode_val
parameter value was. For example, the following statement always inserts a row for Smith
with a RATING
value of 10:
INSERT INTO players VALUES ('Smith');
The following example causes the column default value to be used during the rest of the current session whenever an INSERT statement into a version-enabled table specifies a null value for a column that has a default value.
EXECUTE DBMS_WM.UseDefaultValuesForNulls('ON');