Report geodatabase versioning statistics in an Oracle geodatabase

Summary

Instructions provided describe how to report geodatabase versioning statistics in an Oracle geodatabase. The SQL script provided below reports information about a versioned geodatabase in Oracle. The output lists the number of versions, the number of versions blocking the DEFAULT version from being compressed to state 0 and the owner.names of the first 5 blocking versions, the number of states, the number of entries in the state_lineages table, and the number of rows in the versioned table and delta tables.

Procedure

In SQL*Plus, connect as the sde user and execute the following SQL:

SET SERVEROUTPUT ON
DECLARE

 TYPE cv_type IS REF CURSOR;
 cv cv_type;
 TYPE block_states IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
 blocking_list   block_states;
 TYPE block_versions IS TABLE OF VARCHAR2(128);
 blocking_ver    block_versions := block_versions();

 CURSOR tableinfo_cursor IS
  SELECT owner,table_name,registration_id
  FROM sde.table_registry
  WHERE BITAND(sde.table_registry.object_flags,8) = 8 
  ORDER BY registration_id;

 CURSOR ver_list IS
  SELECT DISTINCT state_id
  FROM sde.versions
  WHERE name <> 'DEFAULT' AND owner <> 'SDE'
  ORDER BY state_id;

 compress_date DATE;
 delta_table   CHAR(1);
 row_count     NUMBER;
 base_count    NUMBER;
 stmt          VARCHAR2(2048);
 state_id      NUMBER;
 lin_name      NUMBER;
 source_lin    NUMBER;
 com_anc_id    NUMBER;
 ver_blocking  NUMBER DEFAULT 0;
 cur_max_state NUMBER;
 pos           NUMBER;
 idx           INTEGER DEFAULT 1;
 s1            NUMBER DEFAULT 0;
 s2            NUMBER DEFAULT 0;
 s3            NUMBER DEFAULT 0;
 s4            NUMBER DEFAULT 0;
 s5            NUMBER DEFAULT 0;

BEGIN

 SELECT state_id, lineage_name INTO state_id, lin_name FROM sde.states
 WHERE state_id = (SELECT state_id FROM sde.versions WHERE name = 'DEFAULT' AND owner = 'SDE');

 SELECT COUNT(*) INTO row_count FROM sde.versions;
   
 DBMS_OUTPUT.PUT_LINE(chr(10));
 DBMS_OUTPUT.PUT_LINE(chr(160)||'                  Versioning statistics');
 DBMS_OUTPUT.PUT_LINE(chr(160)||'=======================================================');
 DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of versions: '||row_count);

 cur_max_state := state_id;

 FOR ver_info IN ver_list LOOP

   SELECT lineage_name INTO source_lin FROM sde.states WHERE state_id = ver_info.state_id;

   SELECT MAX(lineage_id) INTO com_anc_id FROM
    (SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = lin_name AND lineage_id <= state_id
     INTERSECT
     SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = source_lin AND lineage_id <= ver_info.state_id);  

   IF com_anc_id < state_id THEN
     ver_blocking := ver_blocking + 1;

     IF com_anc_id < cur_max_state THEN
       IF blocking_list.COUNT < 5 AND blocking_list.EXISTS(com_anc_id) = FALSE THEN
         blocking_list(com_anc_id) := com_anc_id;
        ELSE
         IF blocking_list.COUNT = 5 AND blocking_list.EXISTS(com_anc_id) = FALSE THEN
           pos := blocking_list.FIRST;
           WHILE pos IS NOT NULL LOOP
             IF blocking_list(pos) = com_anc_id THEN
               blocking_list.DELETE(pos);                  
               blocking_list(com_anc_id) := com_anc_id;
               cur_max_state := com_anc_id;
               EXIT;
             END IF;
             pos := blocking_list.NEXT(pos);
           END LOOP;
         END IF;
       END IF;
     END IF;
   END IF;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of versions blocking DEFAULT: '||ver_blocking);

 IF ver_blocking > 0 THEN
   DBMS_OUTPUT.PUT_LINE(chr(160)||'   Top '||blocking_list.COUNT||' blocking versions...');

   pos := blocking_list.FIRST;
   WHILE pos IS NOT NULL LOOP
     CASE
       WHEN idx = 1 THEN s1 := blocking_list(pos);
       WHEN idx = 2 THEN s2 := blocking_list(pos);
       WHEN idx = 3 THEN s3 := blocking_list(pos);
       WHEN idx = 4 THEN s4 := blocking_list(pos);
       WHEN idx = 5 THEN s5 := blocking_list(pos);
     END CASE;
     idx := idx + 1; 
     pos := blocking_list.NEXT(pos);
   END LOOP;

   stmt := 'SELECT name FROM
             (SELECT owner||''.''||name name FROM sde.versions
              WHERE state_id IN
               (SELECT DISTINCT lineage_id FROM sde.state_lineages
                WHERE lineage_name IN
                 (SELECT DISTINCT lineage_name FROM sde.state_lineages
                  WHERE lineage_id IN (:state1,:state2,:state3,:state4,:state5)))
              ORDER BY state_id) vers
            WHERE rownum < 6';
   EXECUTE IMMEDIATE stmt BULK COLLECT INTO blocking_ver USING s1, s2, s3, s4, s5;

   FOR i IN 1..blocking_ver.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(chr(160)||'     '||blocking_ver(i));
   END LOOP; 
 END IF;

 SELECT COUNT(*) INTO row_count FROM sde.states;
 DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of states: '||row_count);

 SELECT COUNT(*) INTO row_count FROM sde.state_lineages;
 DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of state lineages: '||row_count);

 SELECT COUNT(*) INTO row_count FROM sde.state_lineages WHERE lineage_name = lin_name AND lineage_id <= state_id;
 DBMS_OUTPUT.PUT_LINE(chr(160)||' DEFAULT versions lineage length: '||row_count);

 SELECT MAX(compress_start) INTO compress_date FROM sde.compress_log;
 DBMS_OUTPUT.PUT_LINE(chr(160)||' Last compress: '||to_char(compress_date, 'MON-DD-YYYY'));

 FOR table_info IN tableinfo_cursor LOOP
   stmt := 'SELECT COUNT(*) FROM '||table_info.owner||'.'||table_info.table_name;
   OPEN cv FOR stmt;
   LOOP 
     FETCH cv INTO base_count;
     EXIT WHEN cv%notfound;
   END LOOP;
   CLOSE cv;

   FOR i IN 1..2 LOOP
     IF i = 1 THEN
       delta_table := 'a';
      ELSE
       delta_table := 'd';
     END IF;

     stmt := 'SELECT COUNT(*) FROM '||table_info.owner||'.'||delta_table||''||table_info.registration_id;
     OPEN cv FOR stmt;
     LOOP 
       FETCH cv INTO row_count;
       EXIT WHEN cv%notfound;
     END LOOP;
     CLOSE cv;

     IF i = 1 THEN
       DBMS_OUTPUT.PUT_LINE(chr(10));
       DBMS_OUTPUT.PUT_LINE(chr(160)||' Table - '||table_info.owner||'.'||table_info.table_name||' ('||table_info.registration_id||')');
       DBMS_OUTPUT.PUT_LINE(chr(160)||'   -- Base table: '||base_count);
       DBMS_OUTPUT.PUT_LINE(chr(160)||'   -- Adds table: '||row_count);

       IF row_count > 0 THEN
         stmt := 'SELECT ROUND(COUNT(*)/'||row_count||' * 100, 2)
                    FROM '||table_info.owner||'.a'||table_info.registration_id||'
                    WHERE sde_state_id IN (SELECT lineage_id 
                                           FROM sde.state_lineages
                                           WHERE lineage_name = '||lin_name||' AND lineage_id <= '||state_id||')';
         EXECUTE IMMEDIATE stmt INTO row_count;
         DBMS_OUTPUT.PUT_LINE(chr(160)||'   -- Percentage rows in the DEFAULT version lineage: '||row_count||'%');
       END IF;
      ELSE
       DBMS_OUTPUT.PUT_LINE(chr(160)||'   -- Deletes table: '||row_count);

       IF row_count > 0 THEN
         stmt := 'SELECT ROUND(COUNT(*)/'||row_count||' * 100, 2)
                    FROM '||table_info.owner||'.d'||table_info.registration_id||'
                    WHERE deleted_at IN (SELECT lineage_id 
                                           FROM sde.state_lineages
                                           WHERE lineage_name = '||lin_name||' AND lineage_id <= '||state_id||')';
         EXECUTE IMMEDIATE stmt INTO row_count;
         DBMS_OUTPUT.PUT_LINE(chr(160)||'   -- Percentage rows in the DEFAULT version lineage: '||row_count||'%');
       END IF;
     END IF;  
   END LOOP;
 END LOOP;
END;
/

Example output:

               Versioning statistics
=======================================================
Number of versions: 7248
Number of versions blocking DEFAULT: 168
  Top 5 blocking versions...
    RANDY.REV_6847258
    CHRIS.REV_7019237
    CLARE.REV_7069544
    LUCY.REV_7043793
    SUSAN.REV_7069206
Number of states: 8371
Number of state lineages: 1804258
DEFAULT versions lineage length: 521
Last compress: SEP-21-2008

Table - ADMIN.PRIMARYCONDUCTOR (148)
  -- Base table: 440065
  -- Adds table: 15156
  -- Percentage rows in the DEFAULT version lineage: 21.64%
  -- Deletes table: 6056
  -- Percentage rows in the DEFAULT version lineage: 34.79%

Table - ADMIN.BUILDING (338)
  -- Base table: 58955
  -- Adds table: 524
  -- Percentage rows in the DEFAULT version lineage: 68.7%
  -- Deletes table: 400
  -- Percentage rows in the DEFAULT version lineage: 61%