English

How To: Audit the creation and deletion of geodatabase versions in Oracle

Summary

Instructions provided describe how to audit the creation and deletion of versions in Oracle and how to find the duration of time the version existed in ArcGIS prior to being deleted.

The information captured can be valuable for administrators to help gather an understanding of how long versions exist in the system. For example, knowing the average version exists for three days may indicate the organization's throughput for creating a version, which represents a work order, performing the work, posting the version to DEFAULT, and deleting the version.

Procedure

The following steps demonstrate how to create an auditing table, a trigger for capturing the deletion of a version to audit and SQL for reporting statistics about each version, and its life span or duration as a version in ArcGIS.

All these steps should be executed by the SDE user in SQL*Plus.

  1. Create the table that will persist each version's history or audit information.

    Code:
    SQL> CREATE TABLE versions_history
    2 (name VARCHAR2(64) NOT NULL,
    3 owner VARCHAR2(32) NOT NULL,
    4 description VARCHAR2(65),
    5 parent_name VARCHAR2(64),
    6 parent_owner VARCHAR2(32),
    7 creation_time DATE NOT NULL,
    8 deletion_time DATE NOT NULL);

    Table created.

  2. Create a trigger on the sde.versions table to capture the information when a version is deleted. Each time a version is deleted, the trigger executes and inserts the version information into the versions_history table.

    Code:
    SQL> CREATE OR REPLACE TRIGGER versions_history_audit
    2 AFTER DELETE ON sde.versions FOR EACH ROW
    3 BEGIN
    4
    5 INSERT INTO sde.versions_history VALUES
    6 (:OLD.name, :OLD.owner, :OLD.description, :OLD.PARENT_NAME,
    7 :OLD.parent_owner, :OLD.creation_time, SYSDATE);
    8
    9 EXCEPTION
    10 WHEN OTHERS THEN
    11 NULL;
    12
    13 END;
    14 /

    Trigger created.

  3. To report information gathered in the versions_history audit table, the following SQL can be used to return each version's name and its duration: the life span for which it existed in ArcGIS and the duration of time from its creation to deletion.

    Code:
    SQL> COL VERSION FORMAT a64
    SQL> SELECT owner||'.'||name VERSION,
    2 TO_CHAR(creation_time, 'MON-DD-YYYY HH:MI:SS') CREATION_DATE,
    3 TO_CHAR(deletion_time, 'MON-DD-YYYY HH:MI:SS') DELETION_DATE,
    4 TRUNC(deletion_time - creation_time) ||' DAYS, '||
    5 TRUNC(MOD((deletion_time - creation_time) * 24, 24)) || ' HOURS, '||
    6 TRUNC(MOD((deletion_time - creation_time) * 24 * 60, 60)) ||' MINUTES, '||
    7 TRUNC(MOD((deletion_time - creation_time) * 24 * 60 * 60, 60)) || ' SECONDS' "DURATION"
    8 FROM sde.versions_history
    9 ORDER BY creation_time;