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

Advertisements

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.

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

 

seeing alert log from sql prompt


the following command can be used to see the alert log from sql prompt.

SQL> select message_text from X$DBGALERTEXT where rownum <= 50;

MESSAGE_TEXT
——————————————————————————–
Recovery of Online Redo Log: Thread 1 Group 6 Seq 53021 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo6_01.log
Standby controlfile consistent with primary
RFS[16]: Selected log 15 for thread 2 sequence 53013 dbid -1291669901 branch 803
846963

Archived Log entry 106105 added for thread 2 sequence 53012 ID 0xb30fface dest 3
:

Media Recovery Waiting for thread 2 sequence 53013 (in transit)
Recovery of Online Redo Log: Thread 2 Group 15 Seq 53013 Reading mem 0

MESSAGE_TEXT
——————————————————————————–
Mem# 0: /n01/oradata1/prp15/prp15z2_sb_redo15_01.log
Media Recovery Waiting for thread 1 sequence 53022 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 53022 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo5_01.log
Standby controlfile consistent with primary
RFS[16]: Selected log 16 for thread 2 sequence 53014 dbid -1291669901 branch 803
846963

Archived Log entry 106107 added for thread 2 sequence 53013 ID 0xb30fface dest 3
:
MESSAGE_TEXT
——————————————————————————–
Media Recovery Waiting for thread 2 sequence 53014 (in transit)
Recovery of Online Redo Log: Thread 2 Group 16 Seq 53014 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z2_sb_redo16_01.log
Media Recovery Waiting for thread 1 sequence 53023 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 53023 Reading mem 0
Mem# 0: /n01/oradata1/prp15/prp15z1_sb_redo6_01.log

reference: http://www.dbagalaxy.com/

Query to check nls_characterset parameter of database


sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 4 00:39:24 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’;

VALUE$
——————————————————————————–
WE8ISO8859P1

SQL> SELECT value$ FROM sys.props$ WHERE name = ‘NLS_NCHAR_CHARACTERSET’;

VALUE$
——————————————————————————–
AL16UTF16