SantaMufasa is puzzled...

[SantaMufasa]

We are simply trying to reorganize a badly fragmented table. We issue this command with the accompanying results:

alter table disbursement_transaction move tablespace app_data;

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

...which most of us have seen many times in our Oracle lives. We then query the current locks on the instance, find the lock that prevents our DDL, then we either contact the person holding lock, or we kill their session to free the lock.

This time (and first time for me), however, there are no locks on anything on the instance!

What gives? What can I do to track down the offending locker? (BTW, I cannot bounce the instance...145 testers are madly trying to finish their testing prior to EoD deadline.)

Looking forward to suggestions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[carp]

Dave -
Found this on Metalink - apparently, if a locking session ends on the DB but not the OS, it can still cause a problem:

"In some cases, you might need to kill the session at the OS
level as well. Some 3rd party database monitoring software and certain unexplained situations will cause the lock to remain despite not showing up in "v$locked_object" after killing the session."

So far, the only other advice I have found is "bounce the database and the locks should all go away" - which isn't much of a help here!

 

[Thargy]

Santa,

DBMS_REDEFINITION might help - can you do an "on the fly" rebuild?

Regards

T

 

[Turkbear]

Hi,
Dave, would alter table disbursement_transaction Coalesce work without 'caring' about the lock?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

turkbear,

is that an oracle thing or SQL Server. My mate google comes up with hardly any hits for that. Can you further my education here?

Regards

T

 

[carp]

T -
Coalesce is an Oracle thing. It combines contiguous pieces of free space into fewer, larger pieces of free space.

 

[Turkbear]

Hi,
Here's a little note on that :

http://www.dba-oracle.com/t_coalesce_deallocate_unused_space.htm

I believe it is used mostly for partitions/subpartitions.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

Sorry Turkbear,

I meant the disbursement_transaction bit. Coalesce I'm aware of, but couldn't find much about disbursement. Can you oblige with another url?

Regards

T

 

[Turkbear]

Hi,

disbursement_transaction

is the table name from Santa's posting, not a process.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

GentlePeople,

Since my original puzzlement, I believe that I have come up with a script that identifies "Lockers" and "Lockees" in resolution of my problem...My earlier "Locks.sql" script queried "dba_objects, v$session, and v$transaction". This new script, below, queries "gv$session, v$session_wait, and v$access, which, in concert, identify, I believe, locking sessions that I was not seeing with my earlier "locks.sql" script.

Since my usual need is to identify locks for a specific object, I wrote the script to prompt for a specific object that a specific schema owns:

accept owner prompt "Enter the owner of the table to check: "
accept table prompt "Enter the table name to check: "
set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a15 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a22
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
col owner format a15 word_wrap
col ss format a10
col since format 999,999 heading "Seconds|Since|Event"
select decode(b.event
             ,'enqueue','Waiting'
             ,'SQL*Net message from client','Locker'
             ,'Undetermined'
             ) Status
      ,b.SECONDS_IN_WAIT since
      ,c.username oruser
      ,c.osuser osuser
      ,c.machine computer
      ,'||c.sid||','||c.serial#||' ss
      ,c.time
      ,c.program
  from (select sid,owner,object from v$access
         where owner = upper('&owner')
           and object like upper('%&table%')) a
      ,(select SID,EVENT,SECONDS_IN_WAIT
          from v$session_wait) b
      ,(select username
              ,osuser
              ,machine
              ,sid
              ,serial#
              ,status
              ,process
              ,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') time
              ,program
          from gv$session) c
 where a.sid = b.sid(+)
   and a.sid = c.sid(+)
/

              Seconds
                Since
STATUS          Event Oracle Username      O/S Username    Computer/Terminal
------------ -------- -------------------- --------------- -----------------
Waiting           531 DHUNT                WM-HUNTDL\dhunt xxx\WM-HUNTDL        
Waiting           525 DHUNT                WM-HUNTDL\dhunt xxx\WM-HUNTDL        
Locker            546 TEST                 WM-HUNTDL\dhunt xxx\WM-HUNTDL        

SID/Ser#   Logon Date/Time      Program
---------- -------------------- ------------
'8,6'      2009/07/31 17:54:01  sqlplusw.exe
'9,503'    2009/07/31 18:06:15  sqlplusw.exe
'10,471'   2009/08/01 09:31:33  sqlplusw.exe

3 rows selected.

I broke the output into two stacked sections to avoid unsightly line wraps. I formatted the "SID/Ser$" column to facilicate session kills for locking sessions:

alter system kill session '10,471';

Perhaps this will prove useful to more than just me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”