How to unlock a schema in oracle database?

Answer 1

The question is a bit short but I cured a locking problem with the following if it helps?

-- MESSAGE "Data" OR "Information" "is out of date" on some APP screens.

--

-- Identify which table is affected. In this case we know that

-- ops are stored in table WTL. We now need a session_id and serial number

-- so we can invoke the kill command against this rogue process.

--

-- sqlplus system/password@schemaname

select object_id from dba_objects where object_name ='WTL';

--3738

--3820

--3930

--3335

--

-- The following was run against 3738, 3820 and 3930 but nothing was returned

-- However when run against 3335 returned a session_id of 71

select * from v$locked_object where object_id=3335;

-- All we need now is the serial number so we can kill the rogue process.

--

select sid, serial#, command, taddr from v$session where sid=71;

--

-- This returned us a serial number 16711

--

-- All the above can be combined as below:

--

select sid, serial#, command, taddr

from v$session

where sid=(select SESSION_ID

from v$locked_object

where object_id in (select object_id

from dba_objects

where object_name='WTL'))

-- If we are sure this is the rogue lock then kill it.

alter system kill session '71,16711';

-- If it refuses to die use

alter system kill session '71,16711' IMMEDIATE ;

--

-- NOTE: Your sid and serial number will be different from this example!

-- NOTE: Closing the APP window and waiting 10 minutes can (sometimes) also cure the fault.