Report the recommended reconcile order using SQL in Oracle

Summary

Instructions provided describe how to produce the recommended reconcile order using SQL in Oracle. The output from the query is the order that versions should be reconciled with the sde.DEFAULT version prior to performing a compress. By reconciling the versions in the order returned by the query, it allows the compress command to move as many rows as necessary from each versioned class delta tables to the base tables.

Using the SQL approach is the same as if using the ArcObjects interface IVersionedWorkspace2::RecommendedReconcileOrder to obtain an enumeration of version names.

Procedure

Creating a global temporary table and a stored procedure as well as executing a SQL statement is required to return the list of versions in the recommended reconcile order.

Note:
The steps should be performed by the sde administrative user in SQL*Plus.

1. Create the global temporary table that will be used by the stored procedure to record each version's state_id and common ancestor state_id value.
   
   Drop the version_order table, if it already exists.

SQL> DROP TABLE version_order;
DROP TABLE version_order
           *
ERROR at line 1:
ORA-00942: table or view does not exist

2. Create the global temporary table.

SQL> CREATE GLOBAL TEMPORARY TABLE version_order (
  2  owner        VARCHAR2(32), 
  3  name         VARCHAR2(64), 
  4  state_id     NUMBER(38), 
  5  ca_state_id  NUMBER(38))
  6  ON COMMIT PRESERVE ROWS;

Table created.

3. Create the stored procedure common_ancestor_exe.

CREATE OR REPLACE PROCEDURE common_ancestor_exe AS

 CURSOR ver_list IS
   SELECT name, owner, state_id
   FROM sde.versions
   WHERE name <> 'DEFAULT'
   AND name NOT LIKE 'SYNC_%'
   AND name NOT LIKE 'REP_CO_SYNC_%'
   ORDER BY state_id;

 default_id          NUMBER;
 default_lin         NUMBER;
 source_lin          NUMBER;
 common_ancestor_id  NUMBER;

BEGIN

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

 DELETE FROM version_order;

 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 common_ancestor_id FROM
  (SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = default_lin AND lineage_id <= default_id
   INTERSECT
   SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = source_lin AND lineage_id <= ver_info.state_id);  

  IF common_ancestor_id < default_id THEN
    INSERT INTO version_order VALUES (ver_info.owner, ver_info.name, ver_info.state_id, common_ancestor_id);
  END IF;

 END LOOP;

 COMMIT;

END;
/

4. Execute the stored procedure and query the global temporary table to generate the recommended reconcile order.

EXEC sde.common_ancestor_exe;

PL/SQL procedure successfully completed.

5. Execute the following SQL statement, which returns the owner and name of each version based on the recommended reconcile order.

SQL> SELECT owner, name 
  2 FROM version_order 
  3 ORDER BY ca_state_id, state_id;

OWNER        NAME
------------ --------------
RANDY        REV_6847258
CHRIS        REV_7019237
CLARE        REV_7069544
LUCY         REV_7043793
SUSAN        REV_7069206

The versions should then be reconciled with ArcGIS against the DEFAULT version. For each version reconciled, more rows will be moved from the delta tables to the base tables.