script to get blocking Sessions details in Oracle


Though there are various ways to get info about blocking sessions, but i would like to have it documented here as well..

Sourcehttp://www.orskl.com/blocking-sessions-in-oracle/

1. A full query to get list of blocking sessions with BLOCKER, WAITER and OBJECT being blocked:
It works on Single Instance and RAC set-up as well.

col blk_sess format a11
col wtr_sess format a11
col blocker format a10
col waiter format a10
col duration format a9
col blocked_object format a50
select /*+ rule */
a.inst_id ||’,’ || a.sid || ‘,’ || a.serial# blk_sess,
a.username blocker,
h.type,
b.inst_id||’,’||b.sid || ‘,’ || b.serial# wtr_sess,
b.username waiter,
o.owner || ‘.’ || o.object_name ||
nvl2 (subobject_name, ‘.’ || subobject_name, null) blocked_object,
lpad (to_char (trunc (w.ctime / 3600)), 3, ‘0’) || ‘:’ ||
lpad (to_char (mod (trunc (w.ctime / 60), 60)), 2, ‘0’) || ‘:’ ||
lpad (to_char (mod (w.ctime, 60)), 2, ‘0’) duration
from gv$lock h, gv$lock w, gv$session a, gv$session b, dba_objects o
where h.block != 0
and h.lmode != 0
and h.lmode != 1
and w.request != 0
and w.id1 = h.id1
and w.id2 = h.id2
and h.sid = a.sid
and w.sid = b.sid and h.inst_id = a.inst_id
and decode (w.type, ‘TX’, b.row_wait_obj#,
‘TM’, w.id1)
= o.object_id
order by w.ctime desc;

2. A simple query to find out blocking in the database:

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

3. To get limited information on blocking sessions you can use:

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

How to See hidden Parameters and remove them from SPFILE


sometimes when we set hidden parameters, ofcourse after considering the up and down sides of setting that parameter..If you want to see the value of that hidden parameter and you use The “show parameter” command in SQL*Plus Ideally  shows us only the regular parameters and excludes the hidden ones. It is important to know, though, that  only if a hidden parameter was explicitly set in the database, the “show parameter” will show it. It will not show any hidden parameter with a default untouched value.

Following script can be used to return the name of the  all (+hidden parameter also), its current value and its default value:

select par.ksppinm name,
val.ksppstvl value,
val.ksppstdf def_val
from x$ksppi par,
x$ksppcv val
where par.indx=val.indx
order by 1;

Note:  if you want to remove a hidden parameter, remove it. Don’t set it to its default value.

Instead of creating a pfile from spfile, removing  the entry and recreating the spfile OR just changing  the value to the default value you can use the below command to remove hidden parameter

alter system reset hidden_parameter_name scope=spfile sid='*';

Note: The “reset” command works only when writing to the spfile.

Listing only active archive log destinations


select name, value, null status, null error
from v$parameter
where name = 'log_archive_dest'
and value is not null
union all
select p.name, p.value, s.status, s.error
from v$parameter p , V$ARCHIVE_DEST_STATUS s
where name like 'log_archive_dest%'
and s.dest_name = upper(p.name)
and p.name not like '%state%'
and p.value is not null
and 'enable' = (
select lower(p2.value)
from v$parameter p2
where p2.name = substr(p.name,1,instr(p.name,'_',-1)) || 'state' || substr(p.name,instr(p.name,'_',-1))
)
union all
select p.name, p.value, s.status, s.error
from v$parameter p, V$ARCHIVE_DEST_STATUS s
where p.name like 'log_archive_dest_stat%'
and s.dest_name = upper(p.name)
and lower(p.value) = 'enable'
and (
select p2.value
from v$parameter p2
where name = substr(p.name,1,16) || substr(p.name,instr(p.name,'_',-1))
) is not null

NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_1 LOCATION=/n02/oraarch1/orq152a VALID
 valid_for=(ONLINE_LOGFILES,al
 l_roles) db_unique_name=ORQ152
 A

log_archive_dest_2 SERVICE=orq152b LGWR ASYNC AFF VALID
 IRM REOPEN=10 NET_TIMEOUT=30 v
 alid_for=(online_logfiles,prim
 ary_role) db_unique_name=orq15
 2b


NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_3 LOCATION=/n02/oraarch1/orq152a VALID
 _standby valid_for=(STANDBY_LO
 GFILES,STANDBY_ROLE) DB_UNIQUE
 _NAME=orq152a

reference: http://jkstill.blogspot.in/

how to find fixed underlying table for particular v$ parameter


If you want to find underlying base or fixed table from where all data in v$ view is coming from, you can use the below query.

SQL> select view_definition
  from v$fixed_view_definition where view_name=’V$name_of_view’;
for example, if i want to check the fixed table info for view v$backup
query would be
SQL> select view_definition from v$fixed_view_definition where lower (view_name)=’v$backup’;

VIEW_DEFINITION
——————————————————————————————————————————————————————————————————–
select FILE# , STATUS , CHANGE# , TIME from GV$BACKUP where inst_id = USERENV(‘Instance’)

let’s check the fix table for this view gv$backup
SQL> select view_definition from v$fixed_view_definition where lower (view_name)=’gv$backup’;

VIEW_DEFINITION
——————————————————————————————————————————————————————————————————–
select inst_id,hxfil, decode(hxerr, 0,decode(bitand(fhsta, 1), 0,’NOT ACTIVE’,’ACTIVE’), 1,’FILE MISSING’, 2,’OFFLINE NORMAL’, 3,’NOT VERIFIED’, 4,’FILE NOT FOUND’, 5,’CANNOT OPEN FILE’, 6,’CANNOT REA
D HEADER’, 7,’CORRUPT HEADER’, 8,’WRONG FILE TYPE’, 9,’WRONG DATABASE’, 10,’WRONG FILE NUMBER’, 11,’WRONG FILE CREATE’, 12,’WRONG FILE CREATE’, 16,’DELAYED OPEN’, ‘UNKNOWN ERROR’), to_number(fhbsc),
to_date(fhbti,’MM/DD/RR HH24:MI:SS’,’NLS_CALENDAR=Gregorian’) from x$kcvfhonl

so now we know this all info comes from x$kcvfhonl.
there is one more way we can check the same information, but that would be the ideal approach when we know we can get the same with less effort.
SQL> set autot stat
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot on exp
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
1 NOT ACTIVE 1.2388E+11 08-APR-16
2 NOT ACTIVE 1.2388E+11 08-APR-16
3 NOT ACTIVE 1.2388E+11 08-APR-16
4 NOT ACTIVE 1.2388E+11 08-APR-16
5 NOT ACTIVE 1.2388E+11 08-APR-16
6 NOT ACTIVE 1.2388E+11 08-APR-16
7 NOT ACTIVE 1.2388E+11 08-APR-16
8 NOT ACTIVE 1.2388E+11 08-APR-16
9 NOT ACTIVE 1.2388E+11 08-APR-16
10 NOT ACTIVE 1.2388E+11 08-APR-16
11 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
155 NOT ACTIVE 1.2388E+11 08-APR-16
156 NOT ACTIVE 1.2388E+11 08-APR-16
157 NOT ACTIVE 1.2388E+11 08-APR-16
158 NOT ACTIVE 1.2388E+11 08-APR-16
159 NOT ACTIVE 1.2388E+11 08-APR-16
160 NOT ACTIVE 1.2388E+11 08-APR-16
161 NOT ACTIVE 1.2388E+11 08-APR-16
162 NOT ACTIVE 1.2388E+11 08-APR-16
163 NOT ACTIVE 1.2388E+11 08-APR-16
164 NOT ACTIVE 1.2388E+11 08-APR-16
165 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
166 NOT ACTIVE 1.2388E+11 08-APR-16
167 NOT ACTIVE 1.2388E+11 08-APR-16
168 NOT ACTIVE 1.2388E+11 08-APR-16
169 NOT ACTIVE 1.2388E+11 08-APR-16
170 NOT ACTIVE 1.2388E+11 08-APR-16
171 NOT ACTIVE 1.2388E+11 08-APR-16
172 NOT ACTIVE 1.2388E+11 08-APR-16
173 NOT ACTIVE 1.2388E+11 08-APR-16
174 NOT ACTIVE 1.2388E+11 08-APR-16
175 NOT ACTIVE 1.2388E+11 08-APR-16
176 NOT ACTIVE 1.2388E+11 08-APR-16

FILE# STATUS CHANGE# TIME
———- —————— ———- ——————
177 NOT ACTIVE 1.2388E+11 08-APR-16
178 NOT ACTIVE 1.2388E+11 08-APR-16
179 NOT ACTIVE 1.2388E+11 08-APR-16
180 NOT ACTIVE 1.2388E+11 08-APR-16
181 NOT ACTIVE 1.2388E+11 08-APR-16

:
:
:
: And so on

181 rows selected.

Execution Plan
———————————————————-
Plan hash value: 591542025

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 74 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KCVFHONL | 1 | 74 | 0 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“INST_ID”=USERENV(‘INSTANCE’))

so here from explain plan we can get the same info.

I hope it was useful🙂 Thanks for visiting the blog, keep learning and keep sharing.

ORA-04043: object x$bh does not exist


SQL> conn rajat/rajat;
Connected.
SQL> desc v$bh;
Name Null? Type


FILE# NUMBER
BLOCK# NUMBER
CLASS# NUMBER
STATUS VARCHAR2(10)
XNC NUMBER
FORCED_READS NUMBER
FORCED_WRITES NUMBER
LOCK_ELEMENT_ADDR RAW(8)
LOCK_ELEMENT_NAME NUMBER
LOCK_ELEMENT_CLASS NUMBER
DIRTY VARCHAR2(1)
TEMP VARCHAR2(1)
PING VARCHAR2(1)
STALE VARCHAR2(1)
DIRECT VARCHAR2(1)
NEW CHAR(1)
OBJD NUMBER
TS# NUMBER

SQL> desc x$bh;
ERROR:
ORA-04043: object x$bh does not exist

solution: It is there in the SYS schema. It is just no longer exposed. If you want to expose it, you can do it like this…

  1. WARNING _ THIS IS NOT SUPPORTED BY ORACLE – you are completely on your own here.
  2. Login as SYS
  3. Execute the following
  • create view x_$bh as select * from x$bh;
  • create public synonym x$bh for x_$bh;
  • grant select on x_$bh to <some dba-only role>;

SQL> conn / as sysdba
Connected.
SQL> create view x_$bh as select * from x$bh;

View created.

SQL> create public synonym x$bh for x_$bh;

Synonym created.

SQL> grant select on x_$bh to rajat;

Grant succeeded.

SQL> conn rajat/rajat;
Connected.
SQL> desc x$bh;
Name Null? Type
—————————————– ——– —————————-
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8)
BLSIZ NUMBER
NXT_HASH RAW(8)
PRV_HASH RAW(8)
NXT_REPL RAW(8)
PRV_REPL RAW(8)
FLAG NUMBER
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER
TS# NUMBER
FILE# NUMBER
DBARFIL NUMBER
DBABLK NUMBER
CLASS NUMBER
STATE NUMBER
MODE_HELD NUMBER
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(8)
DIRTY_QUEUE NUMBER
SET_DS RAW(8)
OBJ NUMBER
BA RAW(8)
CR_SCN_BAS NUMBER
CR_SCN_WRP NUMBER
CR_XID_USN NUMBER
CR_XID_SLT NUMBER
CR_XID_SQN NUMBER
CR_UBA_FIL NUMBER
CR_UBA_BLK NUMBER
CR_UBA_SEQ NUMBER
CR_UBA_REC NUMBER
CR_SFL NUMBER
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER
LRBA_BNO NUMBER
HSCN_BAS NUMBER
HSCN_WRP NUMBER
HSUB_SCN NUMBER
US_NXT RAW(8)
US_PRV RAW(8)
WA_NXT RAW(8)
WA_PRV RAW(8)
OQ_NXT RAW(8)
OQ_PRV RAW(8)
AQ_NXT RAW(8)
AQ_PRV RAW(8)
OBJ_FLAG NUMBER
TCH NUMBER
TIM NUMBER
CR_RFCNT NUMBER
SHR_RFCNT NUMBER

Happy reading and don’t be shy to comment :)

primary key with a multi-column index


DB version: Oracle 12.1

SQL> create table system.test (col1 number,col2 number, col3 number);

Table created.

SQL> create index system.idx1 on system.test (col2,col1);

Index created.

SQL> alter table system.test ADD PRIMARY KEY (col1) using index;

Table altered.

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
2
—————————————————————————————

But if we do the same steps but just change the index order
SQL> create index system.idx1 on system.test (col1,col2);

Index created.

we get total no of indexes for table=1

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
1

why oracle doesn’t use the existing index in first case ?

Answer is:  Multiple reasons, To use a multi-column index for a primary key, the key should be the leading index columns. Remember, Oracle support only “Index Skip Scan” for non-leading columns. ROWID mapping will be always based on leading columns. (Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.)

For more information about index skip scan please follow: http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

 

 

Index rebuilding in parallel, what could go wrong?


I have one table tab1 as mentioned below

SQL> desc tab1;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
C1 VARCHAR2(100)
C2 VARCHAR2(200)

SQL> create index idx_tab1 on tab1(ID);

Index created.

SQL> alter index idx_tab1 rebuild parallel 4;

Index altered.

SQL> select degree from dba_indexes where index_name=’IDX_TAB1′;

DEGREE
—————————————-
4

NOTE: Index rebuilding in parallel changes the degree permanently .SO NEXT time  it will use the index with parallel degree of 4, which could lead to high CPU utilization. So we can change it to avoid any unexpected problem..

SQL> alter index idx_tab1 parallel 1;

Index altered.

SQL> select degree from dba_indexes where index_name=’IDX_TAB1′;

DEGREE
—————————————-
1