Scenario:
I came across this error whilst trying to recompile a PL\SQL package in the database. It took some time to show this errro message, until then the sqldeveloper was in a locked state.
My package was doing parallel processing of procedures, where we use jobs, chains, programs, steps etc... I triggered this parallel processing once, and closed this before it completes. After this whenever I am trying to edit the package I get this time out error.
Analysis:
When someone is trying to recompile a package and found it is hanging or waiting it may be because some one else is using it and a new attempt to use the package would find it locked.
It seems there may be a lock on package.
Let us find out the session that locks the packages and Kill those.
select * from v$locked_object
no rows selected
select a.sid,a.serial#,b.sql_text from v$session a, v$sql b
where a.sql_id=b.sql_id
and a.username='schema'
select sessionid,owner, name from dba_ddl_locks where name like 'test_pkg'
SESSIONID
OWNER
NAME
2174,schema,test_pkg
SELECT * from v$access where object='test_pkg';
SID
OWNER
OBJECT
TYPE
2174,SCHEMA,TEST_PKG,PACKAGE
select sid,serial# from v$session where sid=2174;
SID
SERIAL#
2174,23456
Kill this session:
alter system kill session '2174,23456' immediate;
system altered
Now you may be able to recombile your package.
No comments:
Post a Comment