Schema locks should only be managed through the ArcGIS clients. If locks need to be removed, the supported and recommended way is to disconnect users via the Geodatabase Administration window in ArcGIS Desktop.
What is happening that requires locks to be removed via SQL? Are they being orphaned?
Christian Wells wrote:
Schema locks should only be managed through the ArcGIS clients.
That, in a nutshell, is what our technical lead said. But it does not work on our system.
Even if the ArcCatalog approach was working as expected it would still be inefficient. Sometimes I will have 200 locks that I need to kill in order to update a database. Selecting them by hand off the lock table would be tiresome and prone to error. What are the other options? SDE command line? arcpy?
Christian Wells wrote:
Are they being orphaned?
No, they are not being orphaned.
Thanks Forest, you should be able to remove all the users pretty quickly using the following arcpy command.
gdb = r'Database Connections\w1041.sde'
The SDE command line tools have a similar function, but as they are deprecated, it may be best to start working with the arcpy module.
We're on Oracle 11g and 10.2.2 and have always been able to disconnect everyone either using ArcCatalog administration or arcpy.DisconnectUser(). Not sure if you noticed but when you're in the Connections tab of geodatabase administration, you can Ctrl+A all connections and right click to disconnect all (although you have to Ctrl+click to deselect your own connection). You can also do this when viewing the Locks tab and disconnect everyone with locks on a particular feature class.
But how do you keep the users from reconnecting. I have the box unchecked that says the Geodatabase is accepting new connections and yet my GIS server reconnects on longer GPs.
My guess is that if they are DBO then they have permissions to connect regardless if you have the option selected:
The geodatabase administrator can always make a connection to the geodatabase regardless of how this property is set.
The thing is, the machine is just as it says my GIS server. So the only thing that should be causing locks at all is the GIS services. Seems a shame to have to shut down all services to make a simple schema change to one feature class.