Oracle Workspace Manager 組件 說明

 

一.Workspace Manager 組件說明

 

在說明之前，我們先用如下SQL查看一下DB中的組件：

SQL> col comp_id for a15

SQL> col version for a15

SQL> col comp_name for a30

SQL> select comp_id,comp_name,versionfrom dba_registry ;

 

COMP_ID         COMP_NAME                      VERSION

--------------------------------------------- ---------------

XDB             Oracle XML Database            11.2.0.3.0

AMD             OLAP Catalog                   11.2.0.3.0

EM              Oracle Enterprise Manager      11.2.0.3.0

SDO             Spatial                        11.2.0.3.0

ORDIM           Oracle Multimedia              11.2.0.3.0

CONTEXT         Oracle Text                    11.2.0.3.0

ODM             Oracle Data Mining             11.2.0.3.0

EXF             Oracle Expression Filter       11.2.0.3.0

RUL             Oracle Rules Manager           11.2.0.3.0

OWM             Oracle Workspace Manager       11.2.0.3.0

CATALOG         Oracle Database Catalog Views  11.2.0.3.0

 

COMP_ID         COMP_NAME                      VERSION

--------------------------------------------- ---------------

CATPROC         Oracle Database Packages and T11.2.0.3.0

                ypes

 

JAVAVM          JServer JAVA Virtual Machine   11.2.0.3.0

XML            Oracle XDK                     11.2.0.3.0

CATJAVA         Oracle Database Java Packages  11.2.0.3.0

XOQ             Oracle OLAP API                11.2.0.3.0

APS             OLAP Analytic Workspace        11.2.0.3.0

 

17 rows selected.

 

MOS 說明：

Oracle 8i/9i/10g/11g 組件（Components） 說明

http://blog.csdn.net/tianlesoftware/article/details/5937382

 

Oracle WorkspaceManager is a new feature of Oracle 9i that transparently and securely versionsrelational content in place with no changes to application SQL (DML), whilepermitting simultaneous read and write access to the same production data.

--Oracle Workspace Manager 是 Oracle9i 的一個新特性，它使應用程式不必對應用程式的 SQL (DML）進行任何更改，便可將相關內容透明安全地儲存在適當位置，而且允許同時對同一生產資料進行讀寫訪問。

 

Applications usea PL/SQL stored procedure to set users in a workspace. Once in a workspace,changes to data in version-enabled tables (using existing application SQL)create new row versions (within the same tablespace) that are only visiblewithin the workspace until explicitly merged with production data. Or, changescan be rolled back to a specific point in time through the use ofsavepoints. 

--應用程式使用 PL/SQL 預存程序將使用者佈建到工作區中。一旦在某個工作區更改已啟用版本戳記的表中的資料（使用現有的應用程式 SQL），則將建立一個新的行版本（在同一個資料表空間），且新的行版本僅在該工作區內可見，直至其被顯式合并到生產資料中。或者可以通過使用儲存點將更改及時復原到指定時間點。

 

Users in aworkspace always see a transactionally consistent view of the entire database.This means they see the changes made in their workspace plus the rest of thedata in the database as it existed when their workspace was created or sincetheir workspace was last refreshed. 
       --工作區內的使用者看到的總是整個資料庫的事務一致性視圖。這就意味著他們可看到在其工作區內發生的更改，以及當建立工作區或上一次重新整理工作區時資料庫中已存在的其他資料。

 

Use OracleEnterprise Manager or call the Workspace Manager PL/SQL API to version-enabletables and perform workspace operations, including create, goto workspace, gotodate, merge, refresh, rollback, compare, resolve conflicts and removeworkspace, as well as manage workspace security and locking. 

--使用 Oracle Enterprise Manager 或調用Workspace Manager PL/SQL API，可對錶啟用版本戳記以及執行工作區操作，包括建立、轉至工作區、轉至日期、合并、重新整理、復原、比較、解決衝突和刪除工作區，以及管理工作區安全和鎖定。

 

WorkspaceManager supports Oracle9i, Oracle8i, Oracle Spatial and many Oracle DBMS features,including referential integrity, locking, triggers, import and export.

-- Workspace Manager 支援Oracle 9i，8i，OracleSpatial 和其他的DBMS 特性，包含referential integrity, locking, triggers, import 和export。

 

OWM組件使用者：

WMSYS: It stores data dictionary for OracleWorkspace Manager

 

可版本化的表（Version-enabling tables）

工作區管理器可以對資料庫裡面的一個或多個使用者表進行版本化，版本化的一個單元是一行。如果一個表是可版本化的，那麼表裡面的所有行都可以支援資料的多版本。版本化的那一行儲存在和源行一樣的表裡面。所有對行版本的插入、更新和刪除操作（DML）都會在Oracle短事務裡面進行，以確保版本資料的完整性。版本的基本結構對於資料庫的使用者來說是不可見的。工作區管理器通過對版本化的表重新命名為tablename_LT來實現這種版本化，在重新命名的表裡面添加一些列來儲存版本化的中繼資料，用源表的名字來建立已版本化的表的一個視圖，並且為視圖定義一個INSTEAD OF的觸發器以方便SQL DML操作。如果你不再需要這個已版本化的表，你可以使這個表的版本化無效。

  

官網說明：

DatabaseApplication Developer's Guide - Workspace Manager, 10g Release 2 (10.2)

 

 

二．卸載Workspace Manager

MOS：

Note.263428.1 Howto De-install Oracle Workspace Manager

 

注意：

De-installingOracle Workspace Manager will remove any existing workspaces and the associatedmetadata.

卸載Workspace Manager 將會移除任何workspace和關聯的metadata。

 

步驟如下：

1)     Disable versioning on allversion-enabled tables in the database before de-installing Oracle WorkspaceManager.

 

2)     To de-install login to SQL*Plusas SYSDBA, invoke the de-installation script:
SQL> @$ORACLE_HOME/rdbms/admin/owmuinst.plb

The Install / Upgrade/ De-install process is discussed in the readme included in the kit. The readmeis supplied with Workspace Manager patch kits.

 

 

三．安裝Workspace Manager

官方文檔的連結如下：

Howto install Workspace Manager with Custom Databases 11gR1
Installing WorkspaceManager with Custom Databases 11gR2

 

WorkspaceManager is installed by default in the seed database and in all databasescreated by the Database Configuration Assistant (DBCA). However, in all otherOracle databases, such as those you create with a customized procedure, youmust install Workspace Manager before you can use its features.

--預設情況下，Workspace Manager 是安裝的，如果我們是自己定製的安裝過程中沒有安裝OWM，那麼就需要手工的來安裝。

 

To install WorkspaceManager in a custom database, do the following:

（1）At the system command prompt,change the current directory to the directory that contains Workspace Managerinstallation script and packages, as shown in the following example:

cd<ORACLE_HOME>/rdbms/admin

 

（2）Connect as SYS to theOracle instance with a command in the following format:

sqlplus sys

Enter thepassword for the SYS account when you are prompted.

 

（3）Run the owminst.plb script:

SQL> @owminst.plb

 

（4）Verify the installation ofWorkspace Manager by entering the following command while connected as anyvalid database user, and ensure that the output is as shown here:

SQL> select dbms_wm.getWorkspace fromdual;

 

GETWORKSPACE

----------------------------------------------------------------------------

LIVE

 

 

四. 其他問題：

 

4.1 Workspace Manager  版本問題

Note.341353.1 Whydoes the Workspace Manager version differ from the current RDBMS patchsetversion 

 

In V9.2.x toV10.2.x, the Oracle Workspace Manager updates are not integrated with thegeneric RDBMS patchsets, but are released seperately.

--在Oracle 9i 到10g中，OWM 的更新不是和RDBMSPatchsets一起的，需要獨立的update OWM。

 

You will need toobtain the latest available Workspace Manager patchset from Metalink:

--從MOS上下載OWM的步驟如下：

 

Steps to find the patchset:

 

Go to http://support.oracle.com

Patches & Updates
Advanced Search
Product or Product Family : Workspace Manager
Release : 9.2.0.x or 10.1.0.x or 10.2.0.x
Press <Go>

Install thehighest available Workspace Manager Patchset for your 9.2.0 or 10.1.0 or 10.2.0release. Follow the instructions described in the README.

 

From Oracle 11g onwards,the Oracle Workspace Manager updates are integrated with the generic RDBMSpatchsets.

--注意，從Oracle11g 以後，OWM的更新和RDBMSPatchset一起，不需要在單獨的更新OWM。

 

4.2 Workspace Manager 常見問題

Note.156963.1 FrequentlyAsked Questions for Oracle Workspace Manager 

4.2.1 How Does Workspace Manager Work?

The WorkspaceManager API is implemented as PL/SQL packages, automatically installed withOracle9i. Using the API or the Version Enabled Tables folder in OracleEnterprise Manager, one can selectively version-enable some or all tables in anexisting or new database. There can be a hierarchy of workspaces in thedatabase. By default, when a workspace is created, it is created from thetopmost, or LIVE, database workspace.

 

All changes madein a workspace are made by conventional short transactions. Creating anexplicit savepoint causes a new version of a row to be created the next timethe row is updated. Changes made in one or more workspaces to the sameproduction data are captured automatically as new versions of the data. Storageexpansion and row proliferation is minimized by versioning only changed rowsand by placing the new versions in the same tablespace.

 

Conflicts aredetected automatically before changes are merged into the LIVE workspaceand can be resolved by the user with Oracle Enterprise Manager orprogrammatically through the API. 

 

 

4.2.2 Summary of Enterprise Manager Integration:

While connectedto a database, you will see a folder called Workspace Management that can beexpanded to see two sub-folders: Version Enabled Tables and Workspaces.

(1)    The OEM Version Enabled tablessub-folder allows you to view table status and set tables as version-enabled.

(2)    The OEM Workspaces folderallows you to:

1)Create and view workspace hierarchies and attributes, including:

2)Set and view workspace access modes. The user access modes for aworkspace are:

a)       No access, is the default

b)       Read only

c)       Single writer, allowing allother users to read

d)      Workspace operations only, suchas merge and rollback

3)Set and view Savepoints

a)       Implicit savepoint created bythe system when child workspace is created

b)       Explicit savepoint created by auser

4)Rollback changes since last explicit savepoint

5)Resolve differences between any two workspaces or between twosavepoints in a workspace

6)Refresh an entire workspace, a table, or rows with data from theparent workspace. Refreshing a workspace may not succeed if there are conflicts

7)Merge all changes made in the workspace or changes made to aspecific table.

8)Set Privileges to access, create, delete, rollback and mergeworkspaces

 

4.2.3 Summary of Workspace Manager metadata views:

WorkspaceManager creates and maintains metadata views to hold information that helps tomanage the workspace environment and diagnose problems. These views areread-only to users. Views that span the whole workspace environment are:

(1)    USER_WM_VERSIONED_TABLES and ALL_WM_VERSIONED_TABLES

(2)    USER_WM_MODIFIED_TABLES and ALL_WM_MODIFIED_TABLES

(3)    USER_WORKSPACES and ALL_WORKSPACES containinformation on the workspaces user owns or can access

(4)    USER_WORKSPACE_SAVEPOINTS and ALL_WORKSPACE_SAVEPOINTS

(5)    USER_WORKSPACE_PRIVS and ALL_WORKSPACE_PRIVS includesall users' privileges

(6)    USER_WM_PRIVS includesprivileges the current user has in each workspace

(7)    ROLE_WM_PRIVS

(8)    USER_WM_LOCKED_TABLES and ALL_WM_LOCKED_TABLES

(9)    DBA_WORKSPACE_USERS containsuser info for workspaces other than LIVE

(10) USER_WM_RIC_INFO and ALL_WM_RIC_INFO containreferential integrity constraints

(11) USER_WM_TAB_TRIGGERS and ALL_WM_TAB_TRIGGERS

(12) ALL_VERSION_HVIEW is a workspace hierarchy

 

Views created for eachworkspace enabled table are:

(1)    Conflict view

(2)    Difference view

(3)    Lock view

(4)    History view

(5)    Multiworkspace view

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Skype: tianlesoftware

Email:   tianlesoftware@gmail.com

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940