Is This Content Helpful?
We're glad to know this article was helpful.
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.
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.
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);
SQL> CREATE OR REPLACE TRIGGER versions_history_audit
2 AFTER DELETE ON sde.versions FOR EACH ROW
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);
10 WHEN OTHERS THEN
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;