English

How To: Work with Oracle Workspace Manager and ArcSDE

Summary

Instructions provided in this article describe a general workflow for working with Oracle versioning - Workspace Manager for ArcSDE/Oracle SDO spatial data.

The purpose of this article is to demonstrate working with Oracle versioning, but not using ArcSDE 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 ArcSDE 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. Download and register the script tool 'ExecuteSQL' for ArcMap from the link below. After installation, SQL commands can be run from within an ArcMap session.

    ExecuteSQL Command for ArcMap
  2. Using SQL*Plus, enable versioning on a table that maintains history:
    Code:
    SQL> EXEC dbms_wm.EnableVersioning(table_name => 'TEST_SDO', hist => 'VIEW_WO_OVERWRITE');

  3. Using SQL*Plus, create a testing workspace.
    Code:
    SQL> EXEC dbms_wm.createWorkspace('LOGON_TEST');


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

  4. Open up a direct connection/3-tier connection in ArcMap and add the TEST_SDO layer.
  5. 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.
  6. Switch to the workspace in the Execute SQL window.
    Code:
    begin dbms_wm.gotoWorkspace('LOGON_TEST'); end;

  7. 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).
  8. Save the edits and select Editor > Stop Editing.
  9. From SQL*Plus, run following code.
    Code:
    SQL> select app_no from test_sdo where objectid=21569;

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

  10. In SQL*Plus, switch to the right workspace.
    Code:
    SQL> EXEC dbms_wm.gotoWorkspace('LOGON_TEST');

  11. Run the query again and observe the changes.
    Code:
    SQL> select app_no from test_sdo where objectid=21569;

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

  12. 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.
  13. Run following queries to double-check the changes from SQL*Plus for the same workspace.
    Code:
    SQL> select count(*) from test_sdo where objectid>22029;

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

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

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

  14. 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).
    Code:
    begin dbms_wm.gotoWorkspace('LIVE'); end;

Related Information