Table Locking

One of the most important skills that you need to have is that you have to be familiar with the database. Please keep in mind that AR System is 100% database centric. Especially during the migrations and import operations, tools may lock AR System database tables, even your AR system or you may experience performance problems. In order to find whether any tables or objects are locked, you can use the following queries. Please keep in mind that these queries work for Oracle. You need to run these queries on the AR System database.

QUERY1
select
        C.OSUSER, c.sid, c.serial#, substr(object_name,1,30) OBJECT, c.username,
        substr(c.program,length(c.program)-10,length(c.program)) image,
        decode(b.type,
                'MR', 'Media Recovery',
                'RT', 'Redo Thread',
                'UN', 'User Name',
                'TX', 'Transaction',
                'TM', 'DML',
                'UL', 'PL/SQL User Lock',
                'DX', 'Distributed Xaction',
                'CF', 'Control File',
                'IS', 'Instance State',
                'FS', 'File Set',
                'IR', 'Instance Recovery',
                'ST', 'Disk Space Transaction',
                'TS', 'Temp Segment',
                'IV', 'Library Cache Invalidation',
                'LS', 'Log Start or Switch',
                'RW', 'Row Wait',
                'SQ', 'Sequence Number',
                'TE', 'Extend Table',
                'TT', 'Temp Table',
                b.type) lock_type,
        decode(b.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.lmode)) mode_held,
         decode(b.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* NOT */
                2, 'Row-SELECT (SS)',     /* LIKE */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* SELECT */
                5, 'SELECT/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(b.request)) mode_requested
from sys.dba_objects a, sys.v_$lock b, sys.v_$session c where
a.object_id = b.id1 and b.sid = c.sid and owner not in ('SYS','SYSTEM')

QUERY2
select     owner||'.'||object_name object, l.object_id
   ,oracle_username||' ('||s.status||')' oruser
   ,os_user_name osuser
   ,machine computer
   ,l.process unix
   ,s.sid sessionid,s.serial# serial
   ,r.name rollname
   ,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, serial, object