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 end their session with the sdemon -o kill command or by ending their Oracle connection.

• 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