can not drop a function 3

[huchen]

Hello,
Here is my problem:
I have a function sf_test1:

CREATE OR REPLACE FUNCTION scott.sf_test1(
id number)
return number
as
cnt number(15, 2) := 0;
begin
select count(*) into cnt from emp_exp
where emp_id = id;
return cnt;
end;

the status of this function is valid. This function is referenced by 5 other functions. and all of them are valid.

User_a has execute privilege on sf_test1

When I drop user user_a, I got this problem:
drop user user_a
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
scott.SF_TEST1

When I drop function scott.SF_TEST1, I got this error:
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
scott.SF_test1


How can I drop this object?
Thank you.

 

[SantaMufasa]

Huchen,

When you receive the error "ORA-04021: timeout occurred while waiting to lock object", typically it results from the object being in use by someone/something else. I have scripts to display both "locks" and "blocking locks" (i.e., locks that are preventing another session from proceeding), so if you need either/both of those, let me know so I can post them for you.

Then, of course, there is the "sledgehammer" approach:

SHUTDOWN IMMEDIATE
STARTUP RESTRICT
drop user user_a cascade;
ALTER SYSTEM DISABLE RESTRICTED SESSION;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[huchen]

Thank you Mufasa. It did not work for the whole morning. But when I tried it again after read your response, it worked when I use "drop user cascade". I can also recreate the function. I am not sure if it is because I used "cascade". Could you please post both of your scripts for my future reference?
Hat off for you!

 

[SantaMufasa]

Huchen,

I will post a proof-of-concept of the scripts below first, then I shall post the actual scripts. The scenario is:

1) User TEST will UPDATE rows in its S_EMP table.
2) A DBA user (DHUNT) will run the "locks.sql" script to show that locks exist, then run the "lockblock.sql" scrip to show that the locks are blocking no one else.
3) User DOH will attempt to UPDATE TEST.S_EMP, as well, but be blocked by TEST's locks.
4) The DBA user will run the "lockblock.sql" script to show that TEST's lock are blocking DOH's progress.

Here is the proof-of-concept listing:

show user
USER is "TEST"

update s_emp set salary = salary * 1.1;

show user
USER is "DHUNT"

SQL> @locks

Wed Aug 02                                                                                                     page    1
                                                      Lock Listing

                                                    Machine name         Unix                   RBS
Locked Object        Oracle Username  O/S Username  of Locker            Process   SID/Ser#     Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
TEST.AUDITOR         TEST (INACTIVE)   DOMAIN\dhunt  DOMAIN\TFMOBILE2709 1268:2336 '10,3065'    RBS5 2006/08/02 12:14:01
TEST.S_EMP           TEST (INACTIVE)   DOMAIN\dhunt  DOMAIN\TFMOBILE2709 1268:2336 '10,3065'    RBS5 2006/08/02 12:14:01
************************************************************************************************************************

SQL> @lockblock

Gathering lock information...
...Countdown...8...
...7...
...6...
...5...
...4...
...3...
...2...
...1...
GO

There are no blocking locks.

show user
USER is "DOH"

SQL> update test.s_emp set salary = salary * 1.1 where rownum = 1;

(session hangs due to TEST locks)

show user
USER is "DHUNT"

SQL> @lockblock

Gathering lock information...
...Countdown...8...
...7...
...6...
...5...
...4...
...3...
...2...
...1...
GO

There is 1 blocking lock:

Object               Holder                    Waiter                    Lock Type
-------------------- ------------------------- ------------------------- -------------
TEST.S_EMP            DOMAIN\dhunt:TEST(10)     DOMAIN\dhunt:DOH(7)      row exclusive

Here, then are the scripts ("locks.sql"):

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select     owner||'.'||object_name obj
   ,oracle_username||' ('||s.status||')' oruser
   ,os_user_name osuser
   ,machine computer
   ,l.process unix
   ,''''||s.sid||','||s.serial#||'''' ss
   ,r.name rs
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from       v$locked_object l
   ,dba_objects o
   ,v$session s
   ,v$transaction t
   ,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

"lockblock.sql":

Rem    Name:    LockBlock.sql
Rem    Author:  Dave Hunt
Rem
Rem    This script shows who is holding a lock that other people are
Rem    waiting for and who the waiters are.
Rem
Rem
prompt
prompt Gathering lock information...
set echo off
set feedback off
set pagesize 0
set linesize 150
ttitle off
col "Object" format a20
col "Holder" format a25
col "Waiter" format a25
col "Lock Type" format a20
prompt ...Countdown...8...
create table TempAllObjects as select * from All_Objects;
prompt ...7...
create index TempAllObjectsID on TempAllObjects (object_id);
prompt ...6...
create table TempVSession as select * from v$Session;
prompt ...5...
create index TempVSessionSID on TempVSession (sid);
prompt ...4...
create table TempVLock as select * from v$lock;
prompt ...3...
create index TempVLockSID on TempVLock(SID);
prompt ...2...
create index TempVLockID1 on TempVLock(ID1);
prompt ...1...
create index TempVLockType on TempVLock(Type);
prompt GO
prompt
select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',
           'There are '||count(*)||' blocking locks:')
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
set pagesize 35
select distinct o.owner||'.'||o.object_name "Object"
   ,sh.osuser||':'||sh.username||'('||sh.sid||')' "Holder"
   ,sw.osuser||':'||sw.username||'('||sw.sid||')' "Waiter",
        decode(lh.lmode
           , 0, 'none'
           , 1, 'null'
           , 2, 'row share'
           , 3, 'row exclusive'
           , 4, 'share'
           , 5, 'share row exclusive'
           , 6, 'exclusive'
           ,    'unknown')  "Lock Type"
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
drop table TempAllObjects;
drop table TempVSession;
drop table TempVLock;
prompt
set feedback on

Let us know if you find these useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[huchen]

Yes, It is very userful. Thank you Santa.