How to find locks preventing a specific version from being deleted within SDE tables?

[AndrewRudin]

If a specific version cannot be deleted because it has locks on it, Is there a way to identify the sde process that is causing the lock using PL/SQL to inspect the table_locks, object_locks, and process_information tables within SDE?  I can't seem to find the right joins to tie a specific version to a lock.

I often ask this question of our DB admin's and their response is usually to just to kill all the connections or restart SDE.  I'd rather prevent kicking everyone off if I can.

I think the sde demon tools have something like this, but I am not the server administrator and don't have access to the box.

[AndrewRudin]

forgot to mention, our SDE is 9.3.1 SP2, Oracle11g

[JakeSkinner]

If a user is accessing a version there should be a state lock.  You can query the version table for the version that is locked:

SQL> select state_id from sde.versions where name = 'Child_Version'

1864

Then you can query the state locks table to get to the sde_id of the user:

SQL> select sde_id from sde.state_locks where state_id = '1864'

4371

Next, you can query the process information table to get the user's name:

SQL> select owner from sde.process_information where sde_id = '4371'

VECTOR

You can request this user to disconnect, or you can use the 'sdemon -o kill' command to kill this specific user:

C:\> sdemon -o kill -t 4371 -i sde:oracle11g

[AndrewRudin]

Thanks.  I guess there isn't a special lock for when someone is editing versus just viewing a version.  So if anyone is viewing data in a version, it can't be deleted.

I also run a python script every night to reconcile all our editing versions with DEFAULT.  Sometimes people leave their edit sessions open and the reconcile tool returns an error about the edit session being open.  This prevents us from getting a good compress, so I need to figure out how to identify these sessions and kill them before I run my script.

[JakeSkinner]

If you are running a python script to perform the reconcile/post/compress you will want to make sure no users are connected to the database.  A connection acquires a state lock and a locked state cannot be compressed.  You may want to consider using the 'sdemon -o kill -t all' command to kill all connected users so you can perform the compress successfully.  Also, there is a great script here for reconciling/posting versions.

[MichaelTrust]

Hi Jake, I have a Python script that for years worked fine in SDE 10.0. It deletes a version owned by SDE if it exists and then re-creates it (it's a child of another user's version). It then posts it to SDE.DEFAULT. Now we are at 10.8.1 for the enterprise GDB (Oracle 19c). The Python script now fails on the delete version step, saying there is a lock by user SDE. How do I get past this? Interestingly, I CAN delete the SDE-owned version from the Administer GDB GUI, but not using ArcPy either in a script or in the Python window in ArcCatalog. I clicked on your link above for the script for reconciling/posting versions but it just goes to https://community.esri.com/. Is the one at https://community.esri.com/t5/python-questions/using-a-python-script-to-reconcile-and-post/td-p/3527... applicable? Any help would be appreciated. Thanks.