Archive for the ‘Oracle Database’ Category

TNSPing & SQLPlus just hang without errors

November 22, 2016 Leave a comment

Usually, when you connect to Oracle, you get errors that give you some feedback on what is happening.

Today, I got an issue where when trying to connect to SQLplus or even running a tnsping command was hanging. Not getting any error to start the troubleshooting. The issue was definitely some sort of connectivity but not able to point it out

In our case, we use “nameserver” in addition to tnsnames.ora. Our sqlnet.ora file looks like this:

I needed to trace my “tnsping” command to see where it is getting hung.

To troubleshoot the issue with tnsping hanging, all you need to do is add these settings in sqlnet.ora to trace tnsping

TNSPING.TRACE_DIRECTORY =/d01/abc/product/8.0.6/network/admin

My being a linux box and hence the path. You may need to modify according to your OS and directory

I ran the “tnsping” to the same Oracle SID again, a trace file “tnsping.trc” got generated in the path defined in the above “TNSPING.TRACE_DIRECTORY” variable.

Careful review of the trace file revealed that he connection was having an issue with the “name server” defined in my sqlnet.ora file.
I asked the Oracle DBA to confirm if the “name server” is started and she confirmed that it is not. Once she started the “name server”, tnsping command went successfully and I was able to connect to SQLplus.

Hope this helps you in some way.

Happy learning!!!

-Anand M

Collection of useful script for Oracle Apps DBA

November 9, 2016 Leave a comment

Below are some of the most useful script for any Oracle APPS DBA. These are handy for day to day monitoring and troubleshooting activity.

Hope this helps.

Script to monitor Tablespace Growth

  • This probes the dba_hist_tbspc_space_usage table and gets the data as old as the retention time of AWR snap
  • Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary view dba_hist_tbspc_space_usage
  • This script is based on AWR
  • If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days growth
a as (
select name,ts#,block_size
from v$tablespace,dba_tablespaces
where name = tablespace_name
c as (
select,min(snap_id) Begin_snap_ID, max(snap_id) End_Snap_ID, min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))) begin_time,
max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))) End_time
from dba_hist_tbspc_space_usage,a
where tablespace_id= a.ts#
group by
d as (
round((dh.tablespace_size* A.BLOCK_SIZE)/1024/1024,2) begin_allocated_space,
round((dh.tablespace_usedsize * A.BLOCK_SIZE)/1024/1024,2) begin_Used_space
from dba_hist_tbspc_space_usage dh,c,a --,b
where dh.snap_id = c.Begin_snap_ID
and a.ts# = dh.tablespace_id
and =
e as (
round((tablespace_size*a.block_size)/1024/1024,2) End_allocated_space,
round((tablespace_usedsize*a.block_size)/1024/1024,2) End_Used_space
from dba_hist_tbspc_space_usage,c ,a
where snap_id = c.End_Snap_ID
and a.ts# = dba_hist_tbspc_space_usage.tablespace_id
and =
select,to_char(c.begin_time,'DD-MON-YYYY') Begin_time,d.begin_allocated_space "Begin_allocated_space(MB)",
d.begin_Used_space "Begin_Used_space(MB)",
to_char(c.End_time,'DD-MON-YYYY') End_Time, e.End_allocated_space "End_allocated_space(MB)", e.End_Used_space "End_Used_space(MB)",
(e.End_Used_space - d.begin_Used_space)"Total Growth(MB)", (c.End_time - c.begin_time)"No.of days",
round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*30,2) "Growth(MB)_in_next30_days",
round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*60,2) "Growth(MB)_in_next60_days",
round(((e.End_Used_space - d.begin_Used_space)/(c.End_time - c.begin_time))*90,2) "Growth(MB)_in_next90_days"
from e,d,c
where =
and =
and (e.End_Used_space - d.begin_Used_space) > 0
order by 1

Script to monitor Tablespace Usage

select,a.gbytes "Allocated", a.MAX_SPACE_GB "
Max Space(GB)", a.used_GB, a.Free_GB, a.pct_used_1 "%age Used"
from (
SELECT NVL(b.tablespace_name, NVL(a.tablespace_name, 'UNKOWN')) name,
Gbytes_alloc Gbytes,
Gbytes_alloc - NVL(Gbytes_free, 0) used_GB,
NVL(Gbytes_free, 0) free_GB,
ROUND(((Gbytes_alloc - NVL(Gbytes_free, 0)) / Gbytes_alloc) * 100, 2) pct_used,
ROUND(((Gbytes_alloc - NVL(Gbytes_free, 0)) / MAX_SPACE_GB) * 100, 2) pct_used_1,
NVL(largest_GB, 0) "largest(GB)"
FROM (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) Gbytes_free,
ROUND(MAX(bytes) / 1024 / 1024 / 1024, 2) largest_GB,
FROM sys.dba_free_space
GROUP BY tablespace_name) a,
(SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) Gbytes_alloc,
FROM sys.dba_data_files
GROUP BY tablespace_name) b,
(select b.tablespace_name,sum(greatest(b.bytes / (1024 * 1024 * 1024),
b.maxbytes / (1024 * 1024 * 1024))) "MAX_SPACE_GB"
from dba_data_files b
group by b.tablespace_name)c
WHERE a.tablespace_name(+) = b.tablespace_name
and b.tablespace_name = c.tablespace_name
ORDER BY 6 desc
order by 6 desc

Script to monitor major database wait events

set echo off
set pages 1000
set lines 120
col inst format 9999
col sid format 9999
col event format a29 trunc
col program format a20 trunc
col module format a20
col username format A11
col secs format 99999
select w.inst_id inst, w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait Secs
from gv$session_wait w, gv$session s
where w.inst_id = s.inst_id and
w.sid=s.sid and w.state='WAITING' and
w.event not in ('pmon timer',
'smon timer',
'rdbms ipc message',
'pipe get',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'wakeup time manager',
'slave wait',
'SQL*Net more data to client') and (w.event not like '%slave wait'
and w.event not like 'EMON slave idle wait%'
and w.event not like 'Streams AQ: waiting for%'
and w.event not like 'Space Manager: slave idle wai%'
and w.event not like 'Streams AQ: emn coordinator%'
and w.event not like 'VKRM%'
and w.event not like 'Streams AQ%')
group by w.inst_id, w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait
order by 1,3;

Query (dbcheck.sql) to check how database is performing at a given point of time

set echo off
set feedback off
set verify off
set lines 500
set pages 1000
column event format a30
column module format a35
column sql_id format a15
SELECT event, module, sql_id, COUNT(*)
FROM v$session
('SQL*Net message from client',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams AQ: qmn slave idle wait',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: emn coordinator idle wait', 'DIAG idle wait',
'SQL*Net message to client', 'pmon timer', 'smon timer',
'VKTM Logical Idle Wait', 'JOX Jit Process Sleep',
'PL/SQL lock timer', 'Streams AQ: waiting for messages in the queue',
'EMON slave idle wait', 'rdbms ipc message', 'PX Deq: Execution Msg',
'Streams AQ: waiting FOR messages IN the queue', 'rdbms ipc MESSAGE',
'Space Manager: slave idle wait', 'pipe get', 'PL/SQL LOCK timer',
'SQL*Net more data to client', 'SQL*Net break/reset to client')
GROUP BY event, module, sql_id
set feedback on
set echo on
set verify on

Query to know the specific user session detail

  • Input – Session ID

select ses.action,
substr(ses.program, 1, instr(ses.program, ' ') - 1) PROGRAM,
to_char(ses.logon_time, 'DD-MON-RR HH24:MI:SS') CONNECT_TIME
from v$session ses, v$process proc
where ses.paddr = proc.addr and ses.sid = &sid

Script to look for all the concurrent jobs currently running in the database.

select q.concurrent_queue_name qname,
a.request_id "Req Id",
decode(a.parent_request_id, -1, NULL, a.parent_request_id) "Parent",
a.concurrent_program_id "Prg Id",
(nvl(a.actual_completion_date, sysdate) - a.actual_start_date) * 1440 "Time",
c.concurrent_program_name || ' - ' ||
a.program "Program"
from APPS.fnd_conc_req_summary_v a,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
APPLSYS.fnd_concurrent_programs_tl c2,
APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_user f
where a.controlling_manager = b.concurrent_process_id and
a.concurrent_program_id = c.concurrent_program_id and
a.program_application_id = c.application_id and
c2.concurrent_program_id = c.concurrent_program_id and
c2.application_id = c.application_id and
a.phase_code in ('I', 'P', 'R', 'T') and a.requested_by = f.user_id and
b.queue_application_id = q.application_id and
b.concurrent_queue_id = q.concurrent_queue_id and c2.language = 'US' and
a.hold_flag = 'N'
order by 1, 3;

Query to look for all the concurrent jobs currently running in the database in a specific manager

  • Input – Queue name

set echo off
set heading on
set lines 1000
set pagesize 1000

col spid form a6 head SPID
col program form A60 trunc
col time form 99999.99 head Elapsed
col "Req Id" form 9999999999
col "Parent" form a9
col "Prg Id" form 9999999col qname head "Manager" format a20 trunc
col user_name form A12 head User trunc
set recsep off

select q.concurrent_queue_name qname,
a.request_id "Req Id",
decode(a.parent_request_id, -1, NULL, a.parent_request_id) "Parent",
a.concurrent_program_id "Prg Id",
(nvl(a.actual_completion_date, sysdate) - a.actual_start_date) * 1440 "Time",
c.concurrent_program_name || ' - ' || a.program "Program"
from APPS.fnd_conc_req_summary_v a,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
APPLSYS.fnd_concurrent_programs_tl c2,
APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_user f
where a.controlling_manager = b.concurrent_process_id and
a.concurrent_program_id = c.concurrent_program_id and
a.program_application_id = c.application_id and
c2.concurrent_program_id = c.concurrent_program_id and
c2.application_id = c.application_id and
a.phase_code in ('I', 'P', 'R', 'T') and a.requested_by = f.user_id and
b.queue_application_id = q.application_id and
b.concurrent_queue_id = q.concurrent_queue_id and c2.language = 'US' and
a.hold_flag = 'N' and q.concurrent_queue_name = '&qeueue_name'
order by 1, 3
set echo on


Query to find the concurrent program ID for any concurrent program(findprog.sql)

  • Input – Concurrent Program (Wild character will also do)

set echo off
set line 132
set feed off
set define on
set serveroutput on
set timing off
set pagesize 1000
set heading off
undefine Concurrent_Program_Name1
accept Concurrent_Program_Name1 prompt 'Concurrent_Program_Name: '
exec dbms_output.put_line('*************************************');
exec dbms_output.put_line('Displaying program details...');
exec dbms_output.put_line('*************************************');
select '--------------------------------------------------------'||chr(10) from dual
select 'Prog Name: ' || fcpt.user_concurrent_program_name || chr(10) ||
chr(9) || 'Conc Prog Id: ' || fcpt.concurrent_program_id || chr(10) ||
chr(9) || 'Short Name: ' || fcp.concurrent_program_name || chr(10) ||
chr(9) || 'Application: ' || fat.application_name || chr(10) ||
'--------------------------------------------------------' Details
from apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_programs fcp,
apps.fnd_application_tl fat
where upper(fcpt.user_concurrent_program_name) like
upper('&Concurrent_Program_Name1') and
fcpt.concurrent_program_id = fcp.concurrent_program_id and
fcpt.application_id = fcp.application_id and
fcpt.application_id = fat.application_id
order by 1

set timing on
set heading on

Query to find the history run statistics of any specific program

  • Input – Concurrent Program ID

set echo off
clear column
set lines 500
set feedback off
set verify off
accept program_id prompt 'Enter Conc Prog ID :'

column request_id format 999999999
column username format a15
column name format a40
column argument_text format a30
column actual_start_date format a10

select r.request_id Request,
f.user_name UserName,
to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') Run_date,
round((r.actual_completion_date - r.actual_start_date) * 1440, 4) Elapsed,
r.argument_text "Program Parameters"
from apps.fnd_concurrent_requests r, apps.fnd_user f
where r.concurrent_program_id = &&program_id and
r.requested_by = f.user_id
order by r.actual_completion_date desc

select r.concurrent_program_id Id,
p.user_concurrent_program_name Name,
trunc(r.actual_start_date) Start_date,
round(avg((r.actual_completion_date - r.actual_start_date) * 1440),
2) "Avg Elapsed Time (min)",
from apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_tl p
where r.concurrent_program_id = p.concurrent_program_id and
r.program_application_id = p.application_id and
r.concurrent_program_id = &&program_id and p.language = 'US'
group by r.concurrent_program_id,

select concurrent_program_id,
avg(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Avg_Time",
max(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Max_Time",
min(round((actual_completion_date - actual_start_date) * 1440, 2)) as "Min_Time"
from (select fr.concurrent_program_id,
from apps.fnd_concurrent_requests fr,
apps.fnd_concurrent_programs_tl fc,
apps.fnd_user fu
where fr.concurrent_program_id = fc.concurrent_program_id and
fu.user_id = fr.requested_by and
fr.concurrent_program_id = &&program_id and
fc.language = 'US' and fr.status_code = 'C' and
fr.phase_code = 'C')
group by concurrent_program_id, user_concurrent_program_name

prompt ++++++++++++++++++++++++++++++++++ END

Query to find job statistics submitted by a particular user.
It is useful to run during the month end to monitor the job statistics for a particular user.
It can be customized for (Input)

  • User
  • Request Date or Completion Date.
  • Specific concurrent Program.

select r.request_id Request,
f.user_name UserNam,
r.phase_code) PHASE,
'On Hold',
'No Manager',
r.status_code) STATUS,
to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') Run_date,
to_char(r.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') Comletion_date,
round((nvl(r.actual_completion_date, sysdate) - r.actual_start_date) * 1440,
2) Elapsed,
r.argument_text "Program Parameters"
from apps.fnd_concurrent_requests r,
apps.fnd_user f,
apps.fnd_concurrent_programs_tl fcpt
where r.requested_by = f.user_id
and TO_DATE(r.request_date) >= to_date('08-NOV-2016 10:00:00', 'DD-MON-YYYY HH24:MI:SS') and
f.user_name = '&User_Name' and
fcpt.language = 'US' and
upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) like upper('%&Prog_name%')
AND r.phase_code = 'C' -- Can be commented
AND r.status_code = 'C' -- Can be commented as per need
order by r.actual_completion_date desc

Query to find the profile option and their values defined.
Query can be customized to check for any specific profile option name

  • Input – Profile Option name(Wild character will also do)

select distinct
t.user_profile_option_name "Profile Option Name",
decode(v.level_id, 10001,'Site Level',
10002,'Application Level --> ' ||application_name ,
10003,'Responsibility level-->'||responsibility_name,
10004,'User Level-->' ||u.user_name,
'XXX') "Profile Option Level",
profile_option_value "Value"
from apps.fnd_profile_options o,
apps.fnd_profile_option_values v,
apps.fnd_profile_options_tl t,
apps.fnd_responsibility_tl r,
apps.fnd_application_tl a,apps.fnd_user u
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and start_date_active <= SYSDATE and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and a.application_id(+) = decode(level_id,10002,level_value,null)
and r.responsibility_id(+)= decode(level_id,10003,level_value,null)
and u.user_id(+) = decode(level_id,10004,level_value,null)
and upper(t.user_profile_option_name) like upper('%&Profile_name%')
and t.language = 'US'
order by 2,
decode(v.level_id, 10001,'Site Level',
10002,'Application Level --> ' ||application_name ,
10003,'Responsibility level-->'||responsibility_name,
10004,'User Level-->' ||u.user_name,

Query to know how much datafiles within a specific tablespace can be resized. This query comes very handy when you want to check how much a datafile within a TBS can be resized.

  • Input – Tablespace ID (can be found from v$tablespace) and Tablespace Name

set linesize 1000 pagesize 0 feedback off trimspool on
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
-- 403 is the TS # for APPS_TS_TX_DATA
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue WHERE ktfbuesegtsn = 388 group by ktfbuefno,ktfbuesegtsn
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)where name = 'APPS_TS_TX_DATA'
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
where tablespace_name = 'APPS_TS_TX_DATA'
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc;

Happy Reading!

-Anand M


Space reclaim using complete database export import

November 2, 2016 Leave a comment

We had a non-prod database which was having more than 50% of space as free. Objective was to reclaim the space at OS level and release the space back to storage.
This is an Oracle E-Biz environment.
Ebiz Version – 12.2.2
Oracle DB version –
OS – 64 bit Oracle Linux

Tried to resize the datafile as much as possible but could not reclaim enough space. Hence thought of doing the full database re-org using export and import.

This document demonstrates step by step procedure with screenshots to do full database reorg using export/import

Full Database reorg using export-import

Post export/import, I was able to reclaim around 4 TB (65% reduction) of space.

Pls see the result below.


Hope this helps. Happy learning!!!

-Anand M

Common Database Wait Events

Below is the document containing the most common database waits seen during the day to day activity of an Oracle DBA. It contains the possible causes and different ways to alleviate the wait.

Common Database waits

Happy reading!


Categories: Oracle Database Tags:

PASSWORD column NULL in DBA_USERS from 11g onwards

December 16, 2014 Leave a comment

I got a request from support team to reset the DBA user password as they were getting some error due to password expiration.
However support person was not aware of the password.

  2  from dba_users
  3  where USERNAME = 'PEOPLE';


Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user’s password and restore it to its original value without actually knowing it.
The command:


it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null … so where are we supposed to take this hashed value from?.


--------------- ------------------------------

Starting with Oracle 11g, there is a view called SYS.USER$ which stores the PASSWORD, the way it was stored prior to 11g.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you login with “SYS” account, you can still apply the conventional (prior to 11g method) to temporarily reset the password, and still have access to the hashed password.

WHERE NAME = 'PEOPLE'   2    3  ;


And the command used to reset the password

SQL> alter user PEOPLE identified by values 'F28740221A2D9A70';

User altered.

  2  from dba_users
  3  where USERNAME = 'PEOPLE';

Categories: Oracle Database Tags: ,

ORA-01017 error while accessing DB link from 9i to 11g

Today I faced a strange issue. Support team needed to extract some data from PeopleSoft database ( via oracle ERP database ( A database links already exists between the 2 databases but when SELECT query is fired, we were getting below error.

ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from PEOPLESOFT.WORLD

Elapsed: 00:00:01.62

and I was pretty sure that the login credential for the remote database is perfectly correct. Even I was able to connect remote DB using the same credential.

SQL>conn <a href="mailto:EMP_EXTR@Remote_DB">EMP_EXTR@Remote_DB</a>
Enter password: *********
and ses.sid=(select sid from v$mystat where rownum=1)
ERROR at line 7:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.57
SQL>show user

I tried dropping and then recreating the DB link but still got the same error. I searched the error in Metalink got one direct HIT – Note ID 473716.1.

This asks to create the database link with ‘double quotes’ around the password (IDENTIFIED by clause). I dropped and recreated the database link as advised. This is due to the fact that user passwords are case-sensitive by default from 11gR1 onwards.

This resolved the error.

SQL>select sysdate from <a href="mailto:dual@PEOPLESOFT.WORLD">dual@PEOPLESOFT.WORLD</a>;


Elapsed: 00:00:00.73



Categories: Oracle Database

Understanding Database locks using v$lock

Many times I have seen either user or development team complaining of their DML statements running long or hung. And it is most of the time perceived as caused by slow database.

But every time, I don’t see any contention from database side. It is the database lock that has been causing the ‘hung’ behaviour. There are ways to investigate the database locks and the associated details.

I run below query on V$LOCK and see the following details

<pre>SQL> SELECT sid,
                                DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
        DECODE( request, 0, 'NO', 'YES' ) WAITER,
        decode(LMODE,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') lmode,
                                 decode(REQUEST,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') request,
                                TRUNC(CTIME/60) MIN ,
                        FROM  v$lock
      where request > 0 OR block =1;</pre>
       SID TY BLO WAI LMOD REQU        MIN        ID1        ID2      BLOCK
---------- -- --- --- ---- ---- ---------- ---------- ---------- ----------
       859 TX NO  YES NONE X             0    6094884    1244082          0
      1839 TX NO  YES NONE X             0    5242904    1939567          0
      1926 TX YES NO  X    NONE          1    5242904    1939567          1
      3082 TX YES NO  X    NONE          0    6094884    1244082          1

Any session with block=1 is the blocker. Here session with SID = 1926 is blocking other session as it has block=1. Now we need to find this blocker session is blocking which other sessions. To find this, we need to compare the ID1 and ID2 column value. Here we can see ID1 and ID2 for SID=1926 is matching the ID1 and ID2 of SID=1839 . Hence SID=1839 is the blocked session. Any session which is blocked will not be able to process their request and hence REQUEST column for those blocked session will have value > 0.

Let us describe V$LOCK to understand other interested columns.

SQL> desc v$lock
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 KADDR                                                          RAW(8)
 SID                                                            NUMBER
 TYPE                                                           VARCHAR2(2)
 ID1                                                            NUMBER
 ID2                                                            NUMBER
 LMODE                                                          NUMBER
 REQUEST                                                        NUMBER
 CTIME                                                          NUMBER
 BLOCK                                                          NUMBER

ID1 and ID2 – These are rollback segment and transaction table entries respectively. When a blocker session acquired the lock, it posted an entry for rollback segment and transaction table. And when another session (blocked sessions) tried to read the same block, Oracle tried to generate the “consistent Read” image by reading the rollback segment but since there was an exclusive lock by the ‘blocker’ session, it has to wait. Therefore we same value of ID1 and ID2 for the blocker and the blocked session.

LMODE – This is the mode of lock acquired. Value ranges from 1 t0 6. 1 (null), 2 (row share) , 3 (row exclusive) , 4 (share) , 5 (share row exclusive) and 6 (exclusive). ‘Exclusive’ is the most restrictive kind of lock.

TYPE – This tells us the kind of lock put on the table where the data is locked. If a session has to change the data in a table, it has to acquire ‘transactional lock (TX)’

REQUEST – This column represents the lock mode requested by the blocking session. Any session which is being blocked will always some value updated against this column. In the query output shown above, we can see all the blocked sessions have a value of 5 against the REQUEST column. This means, all these sessions have requested for a lock mode 5 and waiting for the lock being held by the blocker session.

If the ‘Type’ of lock is ‘TM’ – which is DML level lock and generally acquired in row exclusive (LMODE = 3) mode. This will not let any DDL happening on the locked table. When the TYPE = TM, ID1 corresponds to the object ID. We can query dba_objects and find the object name and type against the given object ID.

We can find the row which is being blocked by the blocker session using v$session.

  • First we need to find value of 4 columns from v$session – 1)ROW_WAIT_OBJ# 2)ROW_WAIT_FILE# 3)ROW_WAIT_BLOCK# & 4)ROW_WAIT_ROW#
  • Then using these 4 values and using DBMS_ROWID packge, we can generate the ROWID of the blocked row
    • select dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) where sid = <BLOCKED_SESSION>

Good understanding of database lock helps in troubleshooting and investigating the same. Hope it helps to you as well.


Categories: Oracle Database