Skip to main content

I am trying to update a record in PL/SQL because our users cannot do it on IEE. They said it freezes. So I got the PL/SQL trace, but when I try to run it, it gives this error:

How can I unlock this record?

It looks like you probably have a record locked for write update in the database itself.  Could be a legit long-running process or something that has got stuck. 

You should look for the locking session in Oracle and - if it is not a valid session - disconnect or kill that session.

You can Google for ways to do this - it is a pure Oracle function.  The risk is whether or not killing the locked session will result in lost transactions.

Here’s some steps that might help that I pulled from a quick Google search:

  1. Identify the locked object and session:
    • Use the following query to find the session ID (SESSION_ID) associated with the locked table:
        SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = 'Alocked table name]'

Replace Âlocked table name] with the actual name of the table. 

  1. Use the following query to find the SID and serial# of the session:
        SELECT a.OS_USER_NAME, b.SID, b.SERIAL#, b.SQL_ID, b.EVENT, c.OBJECT_NAME
        FROM v$locked_object a, v$session b, dba_objects c
        WHERE b.SID = a.SESSION_ID
          AND a.OBJECT_ID = c.OBJECT_ID
          AND b.SID = DSESSION_ID]

Replace aSESSION_ID] with the value obtained in the previous step.

  1. Kill the session:
    • Use the following command to kill the session:
        ALTER SYSTEM KILL SESSION 'LSID],SSERIAL#]'

Replace pSID] and eSERIAL#] with the values obtained in the previous step

Hey ​@NickPorter , do you know what sort of perms are required to access DBA_DML_LOCKS ?

 

Can’t see that view as IFSAPP, and on Managed Cloud instances the IFS Customers don’t get the SYS Password so I’m not sure it’s that easy to get it?

 

You can access DBA_LOCKS but that view doesnt contain the Table being updated.

​@NickPorter  ​@SimonTestard Thanks for the responses.

I managed to kill the session via the PL/SQL Developer Tools section.

After that, I ran the script I shared — it worked just fine.

My guess is that you probably didn’t need to run the script once you had killed the session… your users probably could have run the update through the IFS interface at that point.  The lock was presumably what was stopping them as well. 

Glad you got it resolved.

Nick

Reply


Terms & ConditionsCookie settings