English

How To: Obtain a listing of user processes and locks from a geodatabase when there is no ArcSDE service

Summary

The SDEMON command line utility requires the use of an ArcSDE application server to obtain information about user processes and locks in the geodatabase. In an environment where no ArcSDE service is running, and connection to the geodatabase is only through direct connections, these SQL scripts offer an alternative for gathering useful information about users currently connected to the geodatabase, and about locks on tables and states that may exist in the geodatabase.

Procedure

The examples here are for Oracle and SQL Server geodatabases, and can be run from any client such as Oracle SQL*Plus or from within Microsoft's SQL Server Management Studio. For example, in Oracle SQL*Plus, connect as the SDE user; in MS SQL Server management Studio, right-click on the database and select 'New Query'.

  • List User Processes
    Provides a listing of user processes currently connected to the geodatabase.

    Oracle

    Code:

    SELECT SDE_ID, OWNER, NODENAME||':'||SYSNAME "NODENAME:SYSNAME",TO_CHAR(START_TIME,'DAY MON DD HH24:MI:SS YYYY') "START_TIME" FROM SDE.PROCESS_INFORMATION;

    SQL Server

    Code:

    select sde_id, owner, nodename + ':' + sysname "nodename:sysname", start_time from sde.sde_process_information;

  • List Table Locks
    Provides a listing of ArcSDE table locks currently in use (including owner and tablename), along with the user process holding the lock.

    Oracle

    Code:

    SELECT A.SDE_ID, A.OWNER, L.REGISTRATION_ID, B.OWNER||'.'||B.TABLE_NAME "TABLE", A.NODENAME, L.LOCK_TYPE FROM SDE.PROCESS_INFORMATION A, SDE.TABLE_REGISTRY B, SDE.TABLE_LOCKS L
    WHERE A.SDE_ID = L.SDE_ID AND L.REGISTRATION_ID = B.REGISTRATION_ID
    ORDER BY "TABLE";

    SQL Server

    Code:

    select a.sde_id, a.owner, l.registration_id, b.owner + '.' + b.table_name "Table", a.nodename, l.lock_type from sde.sde_process_information a, sde.sde_table_registry b, sde.sde_table_locks l
    where a.sde_id = l.sde_id and l.registration_id = b.registration_id
    order by "Table";

  • List State Locks
    Provides a listing of ArcSDE state locks currently in use.

    Oracle

    Code:

    SELECT L.STATE_ID, L.LOCK_TYPE, A.SDE_ID, A.OWNER, A.NODENAME FROM SDE.PROCESS_INFORMATION A, SDE.STATE_LOCKS L
    WHERE A.SDE_ID = L.SDE_ID
    ORDER BY STATE_ID;

    SQL Server

    Code:

    select l.state_id, l.lock_type, a.sde_id, a.owner, a.nodename from sde.sde_process_information a, sde.sde_state_locks l
    where a.sde_id = l.sde_id
    order by state_id;

    Note:
    While data can be safely selected from the ArcSDE system tables, the system tables should not be altered using anything other than the ArcGIS software.

Related Information