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

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;