Deleting schema locks

11223
25
10-03-2016 01:10 PM
Highlighted
MVP Regular Contributor

I am wondering how people are deleting schema locks? We are on oracle 11g and SDE 10.1. I have been using a sql delete query run from TOAD to do it for users that have read only connections...

Tags (2)
Reply
0 Kudos
25 Replies
Highlighted
Esri Contributor

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. 

Manage geodatabase locks—Help | ArcGIS for Desktop 

What is happening that requires locks to be removed via SQL? Are they being orphaned?

Reply
0 Kudos
Highlighted
MVP Regular Contributor

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.

Reply
0 Kudos
Highlighted
Esri Contributor

Thanks Forest, you should be able to remove all the users pretty quickly using the following arcpy command. 

gdb = r'Database Connections\w1041.sde'
arcpy.DisconnectUser(gdb, 'ALL')‍‍

DisconnectUser—ArcPy Functions | ArcGIS for Desktop 

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. 

Highlighted
MVP Regular Contributor

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.

Highlighted
Regular Contributor III

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.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

ksjosh82‌ are the connections that are reconnecting using the SDE users as their connection properties in the GP tools?

--- George T.
Reply
0 Kudos
Highlighted
Regular Contributor III

It looks like they are DBO:

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

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.

Prevent geodatabase connections—Help | ArcGIS Desktop 

Manage connections in SQL Server—Help | ArcGIS Desktop 

--- George T.
Highlighted
Regular Contributor III

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.

Reply
0 Kudos