table lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: table lock

  1. #1
    Join Date
    Apr 2001
    Posts
    151
    Hi all,

    how can check which table(s) is being locked. I remember v$lock and another v$ join together will do it but I am blank now....

    Elin@trend

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    set showmode off echo off
    set heading off pagesize 0 linesize 80 timing off feedback off
    set termout off verify off
    rem
    rem Script: userlocks.sql
    rem Purpose: List locks on tables and other objects currently held
    rem by user sessions, including session information and commands
    rem to kill those sessions.
    rem
    rem Author: Stephen Rea
    rem Released: March 22, 1999
    rem
    column object_name format a10
    column username format a10
    column owner format a10
    column sid format 9999
    set heading on pagesize 60 termout on
    select username,v$session.sid,serial#,owner,object_id,object_name,
    object_type,v$lock.type
    from dba_objects,v$lock,v$session where object_id = v$lock.id1
    and v$lock.sid = v$session.sid and owner != 'SYS';
    set heading off pagesize 0
    !echo
    !echo To kill locking sessions:
    select 'alter system kill session ''' || v$session.sid || ',' ||
    serial# || ''';'
    from dba_objects,v$lock,v$session where object_id = v$lock.id1
    and v$lock.sid = v$session.sid and owner != 'SYS';
    !echo
    set linesize 80 termout on heading on pagesize 24 timing on feedback 6
    set termout on verify on echo on showmode both
    Reddy,Sam

  3. #3
    Join Date
    Apr 2001
    Posts
    151
    How long does it take to exec the script you gave to me.

    it has been running for 10 mins. normal?
    Elin@trend

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try running the first part.

    select username,v$session.sid,serial#,owner,object_id,object_name,
    object_type,v$lock.type
    from dba_objects,v$lock,v$session where object_id = v$lock.id1
    and v$lock.sid = v$session.sid and owner != 'SYS';
    Reddy,Sam

Posting Permissions

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


Click Here to Expand Forum to Full Width