Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: who is using a table I want to drop

    I issue a drop table command and get the following message,

    'ORA-00054: resource busy and acquire with NOWAIT specified'.

    How do I find out who is using this table?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Easiest way would be to use the EM console to check out the locks. Otherwise you would need to query the V$LOCK view.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    To use EM, though, you'll need to make sure that you're not trying to acquire the resource in NOWAIT mode. I think te alternative, WAIT, has a parm in seconds that you can provide, like

    Code:
    select *
    from table
    where PF = 1
    for update wait 60;
    That'll give you 60 seconds to check for the lock in EM.

    -cf

  4. #4
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    This will show all DML locks:

    select t1.sid "SID", s.username, s.osuser, s.program,
    s.machine, o.object_name, t1.ctime
    from v$lock t1, v$session s, dba_objects o
    where
    t1.type='TM' and
    t1.sid = s.sid and
    t1.id1 = o.object_id
    order by t1.sid, t1.ctime;

  5. #5
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    what does a value of 9948 in c.time mean?

    I changed the sql like this.

    Code:
    SELECT t1.sid SID, s.username, s.osuser, s.program, 
    s.machine, o.object_name, t1.ctime
    FROM
    v$ACCESS    A,
    v$LOCK     T1,
    v$SESSION   S,
    DBA_OBJECTS O
    WHERE
    t1.TYPE='TM'
    AND A.SID   = S.SID
    AND T1.sid  = s.sid
    AND T1.id1  = o.object_id
    AND  A.OBJECT LIKE 'XXDP_00_END_DATE'||'%'	
    ORDER BY T1.sid, T1.ctime;
    Last edited by wrwelden; 03-05-07 at 16:18.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what does a value of 9948 in c.time mean?
    Are you incapable or unwilling to read the fine Oracle Reference Manual where all the data dictionary tables & views are documented?
    http://download-west.oracle.com/docs...htm#sthref3719
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Thank you sir. You light up my life.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •