topic Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible? in Geodatabase Questions

Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

JamesMorrison1 — Wed, 06 Sep 2023 09:17:30 GMT

Hello Data Managers

Linux / Oracle 19c / Geodatabase 11.1 / ST_GEOMERTY - not real Schema just for this example: SDE, FORESTS, RIVERS, LAKES.

On our Test Database the Schema FORESTS holds several Feature Classes. These can have old locks on them. So when we try and add a Field to a Feature Class it is not possible due to locks. Or if we want to drop the Feature Class it is not possible due to locks.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/manage-geodatabase-locks.htm#:~:text=resource%20for%20use.-,Note%3A,pane%20and%20click%20Administration%20%3E%20Locks.

Naturally, we can drop the locks as the SDE User.

Right-click the database connection in the Catalog pane and click Administration > Locks. Right-click the database connection in the Catalog pane and click Administration > Locks. The geodatabase administrator requires elevated privileges to disconnect users. ALTER SYSTEM SELECT_CATALOG_ROLE*

Is it possible to assign these two Privilege to the User FORESTS so they can delete their own older connections? I have the feeling this is not possible?

Can a User disconnect their own locks or is this just the job of the SDE User as the geodatabase administrator?

Feedback welcome.

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

A_Wyn_Jones — Wed, 06 Sep 2023 09:34:35 GMT

Hi @JamesMorrison1,

You're correct that those privileges are required to drop connections as described here:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/privileges-oracle.htm#:~:text=Oracle%20optional%20privileges%20for%20geodatabase%20administrator

You could grant these to the user directly in Oracle - please consider the impact of this though and test it to see if its your desired outcome.

I'd be asking why these locks are being held and turning into "old locks" - are you using feature datasets in your Enterprise geodatabase?

It might mean that users are actively using the feature class and potentially making edits to the dataset - is that a good time to remove their lock? Normally, a user would release their own lock by ending their ArcGIS Pro session - if the locks are being retained then there's a issue.

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

JamesMorrison1 — Wed, 06 Sep 2023 12:00:52 GMT

Thank you very much for your feedback AWJ.

>> You could grant these to the user directly in Oracle - please consider the impact of this though and test it to see if its your desired outcome.

What impacts are you alluding to here?

>> I'd be asking why these locks are being held and turning into "old locks" - are you using feature datasets in your Enterprise geodatabase?

No Feature Datasets. Just Feature Classes. Since I started with Oracle / SDE way back in 2004 we have always encountered these old connections. Never really found a reason for them.....we just killed them via a script run at night. So one editing.

Thanks.

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

RhettZufelt — Wed, 06 Sep 2023 15:24:08 GMT

Do you have any services that are using these feature classes?

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

A_Wyn_Jones — Wed, 06 Sep 2023 15:41:44 GMT

>> What impacts are you alluding to here?

SELECT_CATALOG_ROLE is required to drop connections. It also allows for that connection to upgrade a geodatabase. This capability should probably not sit with your day to day user.

>> No Feature Datasets. Just Feature Classes. Since I started with Oracle / SDE way back in 2004 we have always encountered these old connections. Never really found a reason for them.....we just killed them via a script run at night. So one editing.

Which version of Geodatabase are you currently running? Locks should be terminated as soon as the user session is shutdown. At version 10.6.1 - a known bug with SDE on SQL caused some connections to become "orphaned" and would cause disruption when it comes to admin tasks i.e. changing a schema. I should make it clear that this information pertains to SQL databases which I normally work with (and the bug relates to).

Hope this helps you somehow!

https://support.esri.com/en-us/bug/accessing-a-geodatabases-locks-information-returns-the-bug-000110574

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

JamesMorrison1 — Thu, 07 Sep 2023 09:11:06 GMT

No.

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

JamesMorrison1 — Thu, 07 Sep 2023 09:16:04 GMT

AWJ - we have used all SDE versions 8, 9.x, 10.x now 11.x over the years and we always experienced old connections. Since this a Test system the Developers are often changing the data model and so the old connections stop them. Hence my original question posted below. Thank you for your feedback.

However, my post this time was more on the question: Can a User disconnect their own locks or is this just the job of the SDE User as the geodatabase administrator?

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

RudraniChakraborty — Wed, 13 Sep 2023 11:49:19 GMT

Hello @JamesMorrison1

The data owner only has the privilege to view the existing locks at the dataset level by the following steps:

>>Start ArcGIS Pro and connect to the geodatabase as the dataset owner.
>>Right-click the dataset in the Catalog pane and click Locks.

If you want to delete the locks, we need to connect with the Geodatabase administrator user, which is sde user. Please check the "View locks on your dataset" section in below document:

Manage geodatabase locks

Even though I am able to see the existing locks on my dataset with the data owner, however, when I click on disconnect, I see below error related to insufficient permission:

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

MarceloMarques — Wed, 13 Sep 2023 16:34:27 GMT

Only the sde user can remove geodatabase locks because the sde user is the arcsde geodatabase repository owner.

Note, database locks are different from geodatabase locks, the database locks can only be removed by the database administrator via the sysadmin database user.

Re: Manage geodatabase locks…but not as the SDE geodatabase administrator – Possible?

Bud — Tue, 19 Sep 2023 18:08:01 GMT

Idea: Allow data owners to remove locks on their own data