Manage connections in Oracle

Available with Standard or Advanced license.

As the geodatabase administrator, you occasionally need to see who is connected to the geodatabase and which connections are locking resources. You may even need to remove certain connections. For example, an editor may have left an edit session open, locking the data in the edit session and preventing other editors from accessing it. If there are a limited number of connections that can be made to a geodatabase, you might need to disconnect a user who failed to log out to free up a connection. Or you may want to have everyone log out so you can apply a patch to the database, restore the database, or compress the geodatabase.

If the users are not present to log out themselves, you can connect to the geodatabase as the geodatabase administrator from ArcGIS for Desktop or an ArcPy function, and drop the users' connections.

Caution:

Use caution when disconnecting users. Esri recommends that you avoid disconnecting active sessions, especially if the connected user is in the process of editing. You should only disconnect users if a session was left open even when not being used (for example, over the weekend), if a session is orphaned and locking an object, or if a deadlock situation occurs.

To disconnect users from a geodatabase in Oracle, the geodatabase administrator must either be added to the DBA role or granted ALTER SYSTEM and SELECT_CATALOG_ROLE privileges.

First, determine who is connected to the geodatabase and remove connections as needed. You can connect to the geodatabase in ArcGIS for Desktop using the DisconnectUser ArcPy function.

Identify and remove connections from ArcGIS for Desktop

You can connect to the geodatabase as the geodatabase administrator in ArcGIS for Desktop to view and remove connections from within the Geodatabase Administration dialog box.

1. Start ArcMap or ArcCatalog.
2. Connect to the geodatabase as the geodatabase administrator.
3. Right-click the database connection in the Catalog tree, point to Administration, and click Administer Geodatabase.
4. Click the Connections tab.

   A list of all user sessions that are currently connected to the geodatabase is displayed. Your session is displayed in italic text.

5. Contact individuals and ask them to close their sessions. If you are unable to contact them and still need to disconnect them from the geodatabase, proceed with the next step.
6. Right-click the specific user session you want to remove from the geodatabase and click Disconnect.

   The user session will be immediately dropped from the geodatabase.

Identify and remove connections using ArcPy

You can run Python functions to define a geodatabase connection, list the users who are currently connected to the geodatabase, and remove a connection from the geodatabase.

1. Create a database connection file by running the CreateDatabaseConnection_management function in a Python window. Save the user name and password with the file.

   In this example, a connection file (oragdb.sde) is created in the temp folder. The connection is to the spora database, logging in as the sde user.

   import arcpy
   arcpy.CreateDatabaseConnection_management ("c:\\temp", "oragdb.sde", "ORACLE", "spora", "DATABASE_AUTH", "sde", "mysdepwd", "SAVE_USERNAME")
   

2. Run the ListUsers function to get a list of all current connections to the spora geodatabase.

   Specify the connection file you created in the previous step.

   ##No need to import arcpy if you are running functions in the same Python window.
   import arcpy
   
   arcpy.ListUsers("c:\\temp\oragdb.sde")
   

   A list of user connections is returned.

   [user(ClientName=u'PC4', ConnectionTime=datetime.datetime(2014, 2, 18, 8, 30, 19),
    ID=18, IsDirecConnection=True, Name=u'publisher1')]
   [user(ClientName=u'PC25', ConnectionTime=datetime.datetime(2014, 2, 21, 14, 10, 43),
    ID=33, IsDirecConnection=True, Name=u'editor2')]
   [user(ClientName=u'PC11', ConnectionTime=datetime.datetime(2014, 2, 22, 9, 18, 26),
    ID=39, IsDirecConnection=True, Name=u'reader5')]
   [user(ClientName=u'PCA2', ConnectionTime=datetime.datetime(2014, 2, 22, 11, 21, 2),
    ID=41, IsDirecConnection=True, Name=u'sde')]

3. Contact individuals and ask them to close their sessions. If you are unable to contact them and still need to disconnect them from the geodatabase, proceed with the next step.
4. Use the information obtained from the ListUsers function to identify which connection to remove using the DisconnectUser function.

   Specify the ID of the connection to remove. Here, the connection with ID 33 is removed:

   ##No need to import arcpy if you are running functions in the same Python window.
   import arcpy
   
   arcpy.DisconnectUser("c:\\temp\oragdb.sde",33)
   

   The user session will be immediately dropped from the geodatabase.