How To: Obtain a listing of user processes and locks from a geodatabase when there is no ArcSDE service
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.
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.
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;
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.
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";
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.
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;
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;
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.