3
Metadata Views

Workspace Manager creates and maintains metadata views to hold information about such things as version-enabled tables, workspaces, savepoints, users, privileges, locks, and conflicts. These views are read-only to users. You can use the information in these views to help administer the long transaction environment and diagnose problems.

• USER_WM_VERSIONED_TABLES (Section 3.17) and ALL_WM_VERSIONED_TABLES (Section 3.6) contain information about version-enabled tables.

• USER_WM_MODIFIED_TABLES (Section 3.13) and ALL_WM_MODIFIED_TABLES (Section 3.3) contain information about version-enabled tables that have been modified.

• USER_WORKSPACES (Section 3.20) and ALL_WORKSPACES (Section 3.9) contain information about workspaces.

• USER_WORKSPACE_SAVEPOINTS (Section 3.19) and ALL_WORKSPACE_SAVEPOINTS (Section 3.8) contain information about savepoints.

• USER_WORKSPACE_PRIVS (Section 3.18) and ALL_WORKSPACE_PRIVS (Section 3.7) contain information about privileges specific to Workspace Manager.

• USER_WM_PRIVS (Section 3.14) contains information about privileges that the current user has in each workspace.

• ROLE_WM_PRIVS (Section 3.11) contains information about privileges that all roles granted to the current user have in each workspace.

• USER_WM_LOCKED_TABLES (Section 3.12) and ALL_WM_LOCKED_TABLES (Section 3.2) contain information about locks placed in the current workspace on rows in version-enabled tables.

• DBA_WORKSPACE_SESSIONS (Section 3.10) contains information about all users in all workspaces other than LIVE.

• USER_WM_RIC_INFO (Section 3.15) and ALL_WM_RIC_INFO (Section 3.4) contain information about referential integrity constraints.

• USER_WM_TAB_TRIGGERS (Section 3.16) and ALL_WM_TAB_TRIGGERS (Section 3.5) contain information about triggers defined on version-enabled tables.

• ALL_VERSION_HVIEW (Section 3.1) contains information about the version hierarchy.

There are also views created per version-enabled table, as follows:

• Conflict view, each having a name in the form <table_name>_CONF. (See Section 3.21.)

• Difference view, each having a name in the form <table_name>_DIFF. (See Section 3.22.)

• Lock view, each having a name in the form <table_name>_LOCK. (See Section 3.23.)

• History view (if history tracking is enabled), each having a name in the form <table_name>_HIST. (See Section 3.24.)

• Multiworkspace view, each having a name in the form <table_name>_MW. (See Section 3.25.)

3.1 ALL_VERSION_HVIEW

ALL_VERSION_HVIEW contains information about the version hierarchy. It is used by Workspace Manager to perform queries against the xxx_HIST views (described in Section 3.24).

Column	Datatype	Null?	Description
VERSION	NUMBER(38)	NOT NULL	Version number of the workspace identified in the WORKSPACE column.
PARENT_VERSION	NUMBER(38)		Version number of the parent version of the version identified in the VERSION column.
WORKSPACE	VARCHAR2(30)		Name of the workspace associated with the version number in the VERSION column.

3.2 ALL_WM_LOCKED_TABLES

ALL_WM_LOCKED_TABLES contains information about Workspace Manager locks on rows in version-enabled tables that the current user can access.

Related View

• USER_WM_LOCKED_TABLES (Section 3.12) contains information about Workspace Manager locks on rows in version-enabled tables of which the current user is the owner.

  Column	Datatype	Null?	Description
  TABLE_OWNER	VARCHAR2(40)		User name of the table owner.
  TABLE_NAME	VARCHAR2(40)		Name of the table.
  LOCK_MODE	VARCHAR2(9)		Type of lock: EXCLUSIVE or SHARED.
  LOCK_OWNER	VARCHAR2(4000)		User name of the owner of the lock.
  LOCKING_WORKSPACE	VARCHAR2(4000)		Workspace in which the lock was placed.

3.3 ALL_WM_MODIFIED_TABLES

ALL_WM_MODIFIED_TABLES contains information about all version-enabled tables that have been modified and on which the current user has one or more of the following privileges: SELECT, INSERT, DELETE, UPDATE.

Related View

• USER_WM_MODIFIED_TABLES (Section 3.13) contains information about version-enabled tables that have been modified and of which the current user is the owner.

  Column	Datatype	Null?	Description
  TABLE_NAME	VARCHAR2(61)	NOT NULL	Name of a version-enabled table.
  WORKSPACE	VARCHAR2(30)	NOT NULL	Workspace in which the modification occurred.
  SAVEPOINT	VARCHAR2(30)		Name of the savepoint associated with the most recent modification, or LATEST if a savepoint does not yet exist is the workspace.

3.4 ALL_WM_RIC_INFO

ALL_WM_RIC_INFO contains information about referential integrity constraints in version-enabled tables that the current user can access. Workspace Manager uses this information to provide referential integrity support, which is described in Section 1.6.

Related View

• USER_WM_RIC_INFO (Section 3.15) contains information about referential integrity constraints in version-enabled tables of which the current user is the owner.

  Column	Datatype	Null?	Description
  CT_OWNER	VARCHAR2(40)	NOT NULL	Owner of the child table in the referential integrity constraint.
  CT_NAME	VARCHAR2(40)		Name of the child table in the referential integrity constraint.
  PT_OWNER	VARCHAR2(40)		Owner of the parent table in the referential integrity constraint.
  PT_NAME	VARCHAR2(40)		Name of the parent table in the referential integrity constraint.
  RIC_NAME	VARCHAR2(40)	NOT NULL	Name of the referential integrity constraint.
  CT_COLS	VARCHAR2(4000)		List of foreign key columns in the child table in the referential integrity constraint.
  PT_COLS	VARCHAR2(4000)		List of foreign key columns in the parent table in the referential integrity constraint.
  R_CONSTRAINT_NAME	VARCHAR2(40)		Name of the unique constraint defined on the parent table in the referential integrity constraint.
  DELETE_RULE	VARCHAR2(2)		Rule to apply when deletion occurs in the parent table. C (Cascade) causes related child table rows to be deleted; R (Restrict) prevents the deletion if any related child table rows exist.
  STATUS	VARCHAR2(8)		ENABLED if the referential integrity constraint is enabled, or DISABLED if the referential integrity constraint is disabled.

3.5 ALL_WM_TAB_TRIGGERS

ALL_WM_TAB_TRIGGERS contains information about triggers that the current user created and for version-enabled tables owned by the current user that have triggers defined on them. If the current user has the CREATE ANY TRIGGER privilege, trigger information is displayed for all version-enabled tables.

Related View

• USER_WM_TAB_TRIGGERS (Section 3.16) contains information about triggers that are owned by the current user and that are on version-enabled tables.

  Column	Datatype	Null?	Description
  TRIGGER_OWNER	VARCHAR2(50)	NOT NULL	Owner (schema) of the trigger.
  TRIGGER_NAME	VARCHAR2(50)	NOT NULL	Name of the trigger.
  TABLE_OWNER	VARCHAR2(50)		Owner (schema) of the table on which the trigger is defined.
  TABLE_NAME	VARCHAR2(50)		Name of the table on which the trigger is defined.
  TRIGGER_TYPE	VARCHAR2(3)		Trigger type: one of the codes described following this table.
  STATUS	VARCHAR2(10)		ENABLED if the trigger is enabled; DISABLED if the trigger is disabled.
  WHEN_CLAUSE	VARCHAR2(4000)		Clause that must evaluate to TRUE for the trigger body (TRIGGER_BODY) to execute.
  DESCRIPTION	VARCHAR2(4000)		Description of the trigger. Useful if the trigger must be re-created.
  TRIGGER_BODY	LONG		Statements executed by the trigger.

TRIGGER_TYPE is one of the following values:

• BIR: before insert for each row

• AIR: after insert for each row

• BUR: before update for each row

• AUR: after update for each row

• BDR: before delete for each row

• ADR: after delete for each row

• BIS: before insert for each statement

• AIS: after insert for each statement

• BUS: before update for each statement

• AUS: after update for each statement

• BDS: before delete for each statement

• ADS: after delete for each statement

3.6 ALL_WM_VERSIONED_TABLES

ALL_WM_VERSIONED_TABLES contains information about all version-enabled tables on which the current user has one or more of the following privileges: SELECT, INSERT, DELETE, UPDATE.

Related View

• USER_WM_VERSIONED_TABLES (Section 3.17) contains information about version-enabled tables of which the current user is the owner.

  Column	Datatype	Null?	Description
  TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of a version-enabled table.
  OWNER	VARCHAR2(30)	NOT NULL	Owner (schema) of the table.
  NOTIFICATION	VARCHAR2(3)		YES if conflict notification is enabled; NO if conflict notification is not enabled.
  NOTIFYWORKSPACES	VARCHAR2(3999)		Names of workspaces where a user needs to be notified if a conflict occurs on the table.
  CONFLICT	VARCHAR2(4000)		YES if there are any conflicts on the table between the workspace that performed the SetConflictWorkspace operation and its parent workspace; otherwise, NO.
  DIFF	VARCHAR2(4000)		YES if there are any differences for this table as a result of a SetDiffVersions operation; otherwise, NO

3.7 ALL_WORKSPACE_PRIVS

ALL_WORKSPACE_PRIVS contains information about Workspace Manager privileges in all workspaces that the current user can access.

Related View

• USER_WORKSPACE_PRIVS (Section 3.18) contains information about Workspace Manager privileges in workspaces created by the current user.

  Column	Datatype	Null?	Description
  GRANTEE	VARCHAR2(30)		User or role to which the privilege was granted.
  WORKSPACE	VARCHAR2(30)		Name of the workspace.
  PRIVILEGE	VARCHAR2(22)		Name of the Workspace Manager privilege.
  GRANTOR	VARCHAR2(30)		User or role that granted the privilege.
  GRANTABLE	VARCHAR2(3)		YES if grantee was given the grant option (that is, can grant the privilege to other users); NO if grantee was not given the grant option.

3.8 ALL_WORKSPACE_SAVEPOINTS

ALL_WORKSPACE_SAVEPOINTS contains information about savepoints in all workspaces that the current user can access.

Related View

• USER_WORKSPACE_SAVEPOINTS (Section 3.19) contains information about savepoints in workspaces created by the current user.

  Column	Datatype	Null?	Description
  SAVEPOINT	VARCHAR2(30)	NOT NULL	Name of the savepoint. Explicit savepoints are named by users; implicit savepoints are named by Workspace Manager.
  WORKSPACE	VARCHAR2(30)	NOT NULL	Workspace in which the savepoint was created.
  IMPLICIT	VARCHAR2(3)		YES if the savepoint is implicit (that is, was created automatically by Workspace Manager); NO if the savepoint is explicit (that is, was created by a user).
  POSITION	NUMBER(38)		Position of the savepoint in the sequence in which savepoints were created. In a RollbackToSP operation, if any implicit savepoints have greater position values than the position of the savepoint to be rolled back to, you must first merge or remove the workspaces that caused these intervening implicit savepoints to be created.
  OWNER	VARCHAR2(30)		Name of the user that created the savepoint.
  CREATETIME	DATE		Date and time that the savepoint was created.
  DESCRIPTION	VARCHAR2(1000)		Description of the savepoint.

3.9 ALL_WORKSPACES

ALL_WORKSPACES contains information about all workspaces that the current user can access.

Related View

• USER_WORKSPACES (Section 3.20) contains information about workspaces created by the current user.

  Column	Datatype	Null?	Description
  WORKSPACE	VARCHAR2(30)		Name of the workspace.
  PARENT_WORKSPACE	VARCHAR2(30)		Parent workspace of this workspace.
  PARENT_SAVEPOINT	VARCHAR2(30)		Implicit savepoint that was created in the parent workspace when this workspace was created.
  OWNER	VARCHAR2(30)		User that created the workspace.
  CREATETIME	DATE		Date and time that the workspace was created.
  DESCRIPTION	VARCHAR2(1000)		Description of the workspace.
  FREEZE_STATUS	VARCHAR2(8)		FROZEN if the workspace is frozen (by a FreezeWorkspace operation); UNFROZEN if the workspace is not frozen.
  FREEZE_MODE	VARCHAR2(12)		NO_ACCESS, READ_ONLY, or 1WRITER. See the freezemode parameter description for the FreezeWorkspace procedure in Chapter 2.
  FREEZE_WRITER	VARCHAR2(30)		The user allowed to make changes in the workspace; or null if the workspace is not frozen or if it is frozen in NO_ACCESS or READ_ONLY mode. See the freezewriter parameter description for the FreezeWorkspace procedure in Chapter 2.
  RESOLVE_STATUS	VARCHAR2(8)		ACTIVE if a conflict resolution session is in progress; INACTIVE if a conflict resolution session is not in progress.
  RESOLVE_USER	VARCHAR2(30)		Name of the user that started the conflict resolution session if resolve_status is ACTIVE; otherwise, null.
  CONTINUALLY_REFRESHED	VARCHAR2(3)		YES if the workspace is continually refreshed (see the description of the isrefreshed parameter of the CreateWorkspace procedure); NO if the workspace is not continually refreshed.
  WORKSPACE_LOCKMODE	VARCHAR2(9)		EXCLUSIVE if the locking mode is exclusive; SHARED is the locking mode is shared; CARRY if the locking mode is carry-forward. See the lockmode parameter description for the SetWorkspaceLockModeON procedure in Chapter 2.
  WORKSPACE_LOCKMODE_OVERRIDE	VARCHAR2(3)		YES if the override option is TRUE; NO if the override option is FALSE; null if the workspace lock mode is not set. See the override parameter description for the SetWorkspaceLockModeON procedure in Chapter 2.

3.10 DBA_WORKSPACE_SESSIONS

DBA_WORKSPACE_SESSIONS contains information about all users and workspaces (except for the LIVE workspace). This view is only available to users with the WM_ADMIN_ROLE role. It is useful for monitoring users in the different workspaces.

Column	Datatype	Null?	Description
USERNAME	VARCHAR2(30)		User name.
WORKSPACE	VARCHAR2(30)	NOT NULL	Workspace that the user is currently in.
SID	NUMBER		Session ID.
SERIAL#	NUMBER		Internal serial number.
STATUS	VARCHAR2(8)		ACTIVE if the user currently has an open transaction (that is, a regular transaction, as opposed to a "long transaction" in the Workspace Manager sense); INACTIVE if the user does not have an open transaction.

3.11 ROLE_WM_PRIVS

ROLE_WM_PRIVS contains information about privileges that all roles granted to the current user have in each workspace.

Related View

• USER_WM_PRIVS (Section 3.14) contains information about privileges that the current user has in each workspace.

  Column	Datatype	Null?	Description
  ROLE	VARCHAR2(30)		Name of the role.
  WORKSPACE	VARCHAR2(30)		Name of the workspace.
  PRIVILEGE	VARCHAR2(22)		Name of the Workspace Manager privilege.
  GRANTABLE	VARCHAR2(3)		YES if the role was given the grant option (that is, can grant the privilege to other users); NO if the role was not given the grant option.

3.12 USER_WM_LOCKED_TABLES

USER_WM_LOCKED_TABLES contains information about Workspace Manager locks on rows in version-enabled tables of which the current user is the owner. Its columns are the same as those in ALL_WM_LOCKED_TABLES in Section 3.2.

3.13 USER_WM_MODIFIED_TABLES

USER_WM_MODIFIED_TABLES contains information about version-enabled tables that have been modified and of which the current user is the owner. Its columns are the same as those in ALL_WM_MODIFIED_TABLES in Section 3.3.

3.14 USER_WM_PRIVS

USER_WM_PRIVS contains information about privileges that the current user has in each workspace.

Related View

• ROLE_WM_PRIVS (Section 3.11) contains information about privileges that all roles granted to the current user have in each workspace.

  Column	Datatype	Null?	Description
  WORKSPACE	VARCHAR2(30)		Name of the workspace.
  PRIVILEGE	VARCHAR2(22)		Name of the Workspace Manager privilege.
  GRANTOR	VARCHAR2(30)		Name of the user that granted the privilege to the current user.
  GRANTABLE	VARCHAR2(3)		YES if the user was given the grant option (that is, can grant the privilege to other users); NO if the user was not given the grant option.

3.15 USER_WM_RIC_INFO

USER_WM_RIC_INFO contains information about referential integrity constraints in version-enabled tables of which the current user is the owner. Its columns are the same as those in ALL_WM_RIC_INFO in Section 3.4.

Workspace Manager uses this information to provide referential integrity support, which is described in Section 1.6.

3.16 USER_WM_TAB_TRIGGERS

USER_WM_TAB_TRIGGERS contains information about triggers that are owned by the current user and that are on version-enabled tables. Its columns are the same as those in ALL_WM_TAB_TRIGGERS in Section 3.5, except that it does not contain the TRIGGER_OWNER column.

3.17 USER_WM_VERSIONED_TABLES

USER_WM_VERSIONED_TABLES contains information about version-enabled tables of which the current user is the owner. Its columns are the same as those in ALL_WM_VERSIONED_TABLES in Section 3.6.

3.18 USER_WORKSPACE_PRIVS

USER_WORKSPACE_PRIVS contains information about Workspace Manager privileges in workspaces created by the current user. Its columns are the same as those in ALL_WORKSPACE_PRIVS in Section 3.7.

3.19 USER_WORKSPACE_SAVEPOINTS

USER_WORKSPACE_SAVEPOINTS contains information about savepoints in workspaces created by the current user. Its columns are the same as those in ALL_WORKSPACE_SAVEPOINTS in Section 3.8.

3.20 USER_WORKSPACES

USER_WORKSPACES contains information about workspaces created by the current user. Its columns are the same as those in ALL_WORKSPACES in Section 3.9.

3.21 xxx_CONF Views

There is one conflict view per version-enabled table. Each conflict view has a name in the form <table_name>_CONF. For example, if the EMPLOYEE table is version-enabled, the EMPLOYEE_CONF metadata view exists.

Each conflict view contains the columns shown in Table 3-1.

Table 3-1 Columns in the xxx_CONF Views  

Column	Datatype	Description
WM_WORKSPACE	VARCHAR2(256)	Workspace in which the conflict exists.
(One column for each column in original table)	(Same as column in original table)	Value of the column in this workspace.
WM_DELETED	VARCHAR2(3)	YES if the row has been deleted; NO if the row has not been deleted; NE if the row is nonexistent (does not exist).

The following example lists the key value and all column values of conflicting rows in the EMPLOYEE table in the current workspace and the parent workspace. This view is available after the SetConflictWorkspace procedure has been called specifying the child workspace (the current workspace in this case).

SELECT * FROM EMPLOYEE_CONF;

If ID, NAME, and CITY are the columns in the EMPLOYEE table, then assume the following values:

WM_WORKSPACE  ID   NAME    CITY     WM_DEL
NEWWORKSPACE  12   SMITH   NASHUA   NO
DiffBase      12   SMITH   NY       NO
LIVE          12   SMITH   BOSTON   NO

The database row identified by ID = 12 was changed in NEWWORKSPACE and LIVE workspaces. In NEWWORKSPACE the city was changed to NASHUA, and in the LIVE workspace the city was changed to BOSTON. When NEWWORKSPACE is merged into LIVE, this row will show up as a conflict. The application must pick between the choices and resolve conflicts in favor of the workspace with the desired value.

The following example begins a conflict resolution session, calls the ResolveConflicts procedure to delete the conflicting row from the NEWWORKSPACE workspace and to insert the value in the parent workspace (LIVE) into both workspaces, commits the transaction, and ends the conflict resolution session.

DBMS_WM.BeginResolve ('NEWWORKSPACE');
DBMS_WM.ResolveConflicts ('NEWWORKSPACE', 'EMPLOYEE', 'ID = 12', 'PARENT');
COMMIT;
DBMS_WM.CommitResolve ('NEWWORKSPACE');

For additional information about conflict resolution, see Section 1.2.5.

3.22 xxx_DIFF Views

There is one difference view for each version-enabled table. Each difference view has a name in the form <table_name>_DIFF. For example, if the EMPLOYEE table is version-enabled, the EMPLOYEE_DIFF metadata view exists. Rows are added to one or more xxx_DIFF views each time the SetDiffVersions procedure is executed.

Each difference view contains the columns shown in Table 3-2.

Table 3-2 Columns in the xxx_DIFF Views  

Column	Datatype	Description
(One column for each column in original table)	(Same as column in original table)	Value of the column in this workspace.
WM_DIFFVER	VARCHAR2(256)	Branch from which the values in the preceding columns are taken. (See the explanation following this table.)
WM_CODE	VARCHAR2(4000)	One of the following codes describing the change: U (updated), D (deleted), I (inserted), NC (no change), NE (nonexistent).

The WM_DIFFVER value is in one of the following formats:

• '<workspace1>, <savepoint1>'

• '<workspace2>, <savepoint2>'

• 'DiffBase'

If the two-parameter version of the SetDiffVersions procedure was used, the value of savepoint1 or savepoint2 is LATEST.

Note the following about the possible values for WM_CODE:

• NC will appear for rows in workspaces that did not change the value when another workspace did change the value. For example, if '<workspace2>, <savepoint2>' updated the row, the code for that row is U, but the code for the '<workspace1>, <savepoint1>' and 'DiffBase' rows is NC if they did not modify the row.

• NE will appear for 'DiffBase' if a row is inserted in one or more branches, and NE will appear for 'DiffBase' and a branch if only one branch has had any insert operations.

For more information, including an example showing rows being added to a differences view, see the section on the SetDiffVersions procedure in Chapter 2.

3.23 xxx_LOCK Views

There is one lock view per version-enabled table. Each lock view has a name in the form <table_name>_LOCK. For example, if the EMPLOYEE table is version-enabled, the EMPLOYEE_LOCK metadata view exists.

Each lock view contains the columns shown in Table 3-3.

Table 3-3 Columns in the xxx_LOCK Views  

Column	Datatype	Description
(One column for each column in original table)	(Same as column in original table)	Value of the column in this workspace.
WM_LOCKMODE	VARCHAR2(9)	Type of lock: E (exclusive) or S (shared).
WM_USERNAME	VARCHAR2(4000)	User name of the owner of the lock.
WM_LOCKINGWORKSPACE	VARCHAR2(4000)	Name of the workspace in which the lock was placed.
WM_INCURWORKSPACE	VARCHAR2(3)	Contains YES if the row is contained in the current workspace, and NO if the row is not contained in the current workspace.

3.24 xxx_HIST Views

There is one history view per version-enabled table if the table was version-enabled with the hist parameter set to VIEW_W_OVERWRITE or VIEW_WO_OVERWRITE in the call to the EnableVersioning procedure. Each history view has a name in the form <table_name>_HIST. For example, if the EMPLOYEE table is version-enabled with the hist parameter set to VIEW_W_OVERWRITE or VIEW_WO_OVERWRITE, the EMPLOYEE_HIST metadata view exists.

You can use the history views to log and audit modifications to version-enabled tables.

Each history view contains the columns shown in Table 3-4.

Table 3-4 Columns in the xxx_HIST Views  

Column	Datatype	Description
(One column for each column in original table)	(Same as column in original table)	Value of the column in this workspace.
WORKSPACE	VARCHAR2(30)	Name of the workspace containing the row.
VERSION	INTEGER	Version number of the row with which the data is associated.
USER_NAME	VARCHAR2(4000)	Name of the user that created the row.
TYPE_OF_CHANGE	VARCHAR2(1)	Type of change operation that was performed on the row: D (delete), I (insert), or U (update).
CREATETIME	DATE	Time when the row was created or updated.
RETIRETIME	DATE	Time when the row was deleted or modified.

3.25 xxx_MW Views

There is one multiworkspace view per version-enabled table. Each multiworkspace view has a name in the form <table_name>_MW. For example, if the EMPLOYEE table is version-enabled, the EMPLOYEE_MW metadata view exists. Rows are added to one or more xxx_MW views each time the SetMultiWorkspaces procedure (described in Chapter 2) is executed.

Each multiworkspace view contains the columns shown in Table 3-5.

Table 3-5 Columns in the xxx_MW Views  

Column	Datatype	Description
(One column for each column in original table)	(Same as column in original table)	Value of the column in this workspace.
MODIFIED_BY	VARCHAR2(4000)	Workspace containing the row that was modified.
OPTYPE	VARCHAR2(4000)	One of the following codes describing the change: U (updated), D (deleted), I (inserted).

You can use the <table_name>_MW view to see changes in another workspace without leaving the current workspace (for example, to check if there is a conflict with the other workspace). Each row in the view shows the data as it would be in that workspace if the workspace had been merged when the row was inserted in the view.

You can also use the <table_name>_DIFF view (see Section 3.22) to see changes in another workspace without leaving the current workspace; however, the <table_name>_DIFF view can be used for only two workspaces, whereas the <table_name>_MW view can be used for any number of workspaces.