Archive for August, 2011

Getting Rid of Orphan State Locks in SDE with Oracle

August 31, 2011 Leave a comment


User of SDE database with Oracle was doing editing on a featureclass on ArcMap when ArcMap suddenly crashed. After the crash, a state lock stayed in STATES_LOCKS table that prevented SDE database compress to state zero. When SDE database can’t be compressed to state zero, the edits that user made on states other than state zero would NOT be moved to business table after compress. Once this happens, most likely you will find that the user who made the edits can see the change on his feature class, SDE admin will get successful result running sdeversion -o compress, but the changes user made at his end just won’t show up on web map that consumes the feature class.


I ran into similar problem recently. There are several things you can do to fix this issue:

1. Check to make sure ALL your database users have been disconnected from ArcCatalog or ArcMap. You can first look at SDE_ID in STATE_LOCKS table. Then run SDEMON -o status -I users. This command will give you the users that is connecting to your SDE database. You should find an ID in the first column of the command result. Look for ID the same as SDE_ID in STATE_LOCKS table. This way you can tell which user (most likely the PC of your user) is giving you the trouble. You can run SDEMON -o kill to kill an inactive connection.

2. If you still see a state lock, that means most likely this is an orphan lock. DON’T try to use SQL to remove the state lock because using SQL to manipulate these tables have known to crash SDE database. Instead, run SDEMON -o shutdown to stop your SDE service and then SDEMON -o start to restart the service. After doing so, you might still see the state lock but once you issue sdeversion -o compress to compress the state tree, the orphan state lock will go away.

If you are as unfortunate as me, you might run into an issue where after stopping your SDE service, you can’t start it – SDEMON -o start just won’t response. In this case, ask your UNIX admin to find additional active process on the server that belongs to SDE user and kill it! In my case, a “oracle” process is still running and it is manually killed. After killing the process, SDEMON -o start works again.

Categories: Spatial Database Tags: ,