Deleting schema locks

19006
25
10-03-2016 01:10 PM
forestknutsen1
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)
0 Kudos
25 Replies
ChristianWells
Esri Regular 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?

0 Kudos
forestknutsen1
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.

0 Kudos
ChristianWells
Esri Regular 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. 

BlakeTerhune
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.

JoshWhite
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.

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
George_Thompson
Esri Frequent Contributor

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

--- George T.
0 Kudos
JoshWhite
Regular Contributor III

It looks like they are DBO:

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
George_Thompson
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.
JoshWhite
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.

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos