All about lock
Find-out who locks your Oracle DB user
select USERID,
userhost,
decode(returncode,01017,'Login Error','Acount Locked') "ISSUE",
spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
order by ntimestamp# desc ;
userhost,
decode(returncode,01017,'Login Error','Acount Locked') "ISSUE",
spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
order by ntimestamp# desc ;
Find blocking sessions with v$session
SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL
Find blocking sessions using v$lock
SELECT
l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2
Recipie #3 - blocking sessions with
all available information
The next query prints a few more
information, it let's you quickly see who's blocking who. Run this query and
you can immediately call the colleague who's locking your table:
SELECT s1.username || '@' ||
s1.machine
|| ' ( SID=' || s1.sid || ' ) is
blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS
blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
If the table is locked by some session in the
database you cannot create index or make any changes to that particular table.
You will end up with “ORA-00054 resource busy and acquired with NOWAIT
specified” error message. You can find out which session is locking
your targeted table by using following queries. You may have to kill (in case
not needed) the session by following queries.
SQL> create index emp_idx on emp(ename);
create index emp_idx on emp(ename)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or
timeout expired
Find out the session, Instance (For RAC) details from v$locked_object or gv$locked_object (For
RAC) using the following query
SQL> SELECT OBJECT_ID, SESSION_ID, inst_id FROM
GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='EMP' and object_type='TABLE' AND
OWNER='SCOTT');
OBJECT_ID SESSION_ID INST_ID
---------- ---------- ----------
164540
130 2
You can find out the session, Os process details by using the
following query.
SQL> select s.sid, s.serial#,s.inst_id, s.program,p.spid from
gv$session s , gv$process p
where p.addr =s.paddr
and s.sid in (136) 2 3
;
SID
SERIAL# INST_ID
---------- ---------- ----------
PROGRAM SPID
---------- ------------------------
130
1337 2
Sqlplus 43212
In case if you wanted to kill the process which is not a
relevant or import one you can use following SQLs.
Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)
SQL> alter system kill session '130,1337,@2';
System altered.
Now you will be able to proceed with your DDLs.
SQL> create index emp_idx on emp(ename);
Comments
Post a Comment