English

How To: Discover what state_locks are blocking the compress operation on Oracle

Summary

Use the following Oracle SQL syntax to discover user sessions that are holding state locks, which prevent the compress operation from moving the DEFAULT version to state 0 (the base state).

Upon connecting to an enterprise geodatabase with ArcGIS, a state_lock is obtained for the state the connection references. The state_lock is used to ensure the state of the versioned geodatabase that the session is reading is not deleted or compressed.

When state_locks are present, the compress operation cannot compress those states with state_locks. In some specific cases, because of the state_locks, the compress operation will not be able to compress the SDE.DEFAULT version to state 0 (the base state).

Procedure

The following SQL statement can be executed in Oracle to discover what user sessions hold state_locks that are preventing the compress operation from compressing the DEFAULT version to state 0.

With this information, the geodatabase administrator can contact the user and request that they disconnect or use brute force and kill their session with the sdemon -o kill command or killing their Oracle connection.

  1. Execute the following SQL statement as the SDE user in SQL*Plus.

    Code:
    SELECT DISTINCT d.sde_id, d.owner, TO_CHAR(start_time, 'HH:MI:SS MON-DD-YYYY') LOGON_TIME
    FROM sde.state_lineages a,
    (SELECT state_id, lineage_name
    FROM sde.states
    WHERE state_id = (SELECT state_id
    FROM sde.versions
    WHERE owner = 'SDE' AND name = 'DEFAULT')) b,
    sde.state_locks c, sde.process_information d
    WHERE a.lineage_id <= b.state_id
    AND a.lineage_name = b.lineage_name
    AND c.state_id = a.lineage_id
    AND c.sde_id = d.sde_id
    ORDER BY d.sde_id ASC;

    SDE_ID OWNER LOGON_TIME
    ---------- ------------------------------ ---------------------
    304 JOHN 09:47:47 OCT-08-2009