English

How To: 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.

    Code:
    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.

    Code:
    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.

    Code:
    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.

    Code:
    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.

    Code:
    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.