HOW TO
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.
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.
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.
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;
Article ID: 000010391
Get help from ArcGIS experts
Download the Esri Support App