Work with Oracle Workspace Manager and an Enterprise geodatabase

Summary

Instructions provided in this article describe a general workflow for working with Oracle versioning.

The purpose of this article is to demonstrate working with Oracle versioning, but not using Enterprise geodatabase versioning, and how to edit and check data changes from ArcGIS Desktop (ArcMap).

Note:
There are still some limitations to working with Oracle Workspace Manager on geodatabase feature classes, such as the inability to preview the data changes from ArcCatalog. Even with ArcMap, a third-party add-on DLL is needed to execute SQL directly from ArcMap so that workspaces can be switched within an ArcMap session.

Procedure

Both ArcMap and Oracle SQL*Plus are used in following workflow.

1. Using SQL*Plus, enable versioning on a table that maintains history:

SQL> EXEC dbms_wm.EnableVersioning(table_name => 'TEST_SDO', hist => 'VIEW_WO_OVERWRITE');

2. Using SQL*Plus, create a testing workspace.

SQL> EXEC dbms_wm.createWorkspace('LOGON_TEST');

Note:
An Oracle Workspace is like a version in a geodatabase. The default workspace for a session context is called LIVE.

3. Open up a direct connection/3-tier connection in ArcMap and add the TEST_SDO layer.
4. Under Editor > Options, uncheck the option to edit a version of the database with the ability to undo and redo, so that edits are directly written against the base table, and start editing.
5. Switch to the workspace in the Execute SQL window.

begin dbms_wm.gotoWorkspace('LOGON_TEST'); end;

6. From ArcMap, open the attribute table, and update one attribute column, for example, update column "APP_NO" to set the value as "123" for one particular feature (objectid=21569).
7. Save the edits and select Editor > Stop Editing.
8. From SQL*Plus, run following code.

SQL> select app_no from test_sdo where objectid=21569;

APP_NO
--------------------

9. In SQL*Plus, switch to the right workspace.

SQL> EXEC dbms_wm.gotoWorkspace('LOGON_TEST');

10. Run the query again and observe the changes.

SQL> select app_no from test_sdo where objectid=21569;

APP_NO
--------------------
123

11. Go back to ArcMap, start editing again, delete one feature (objectid=21986) and create two new features (objectid=22030,22031). Stop editing and save the edits.
12. Run following queries to double-check the changes from SQL*Plus for the same workspace.

SQL> select count(*) from test_sdo where objectid>22029;

  COUNT(*)
----------
         2

SQL> select count(*) from test_sdo where objectid=21986;

  COUNT(*)
----------
         0

13. Go back to ArcMap, change the workspace back to the default workspace, and refresh the ArcMap session. The above changes are no longer visible; those changes were made in a different workspace (version).

begin dbms_wm.gotoWorkspace('LIVE'); end;