Database Monitoring

08:23 Feroz Khan 0 Comments

select s1.inst_id,s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$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 l2.id2 = l2.id2 order by s1.sid; 

SELECT inst_id,blocking_instance,sid||','||serial# as "sid_serial" , blocking_session,inst_id, username, event, seconds_in_wait siw FROM   gv$session  WHERE  blocking_session_status = 'VALID';

select object_type ,owner||'.'||object_name Object, 

SESSION_ID SID, 

replace(upper(OS_USER_NAME),'HDFCDOMAIN\') OS_USER_NAME, 

replace(ORACLE_USERNAME,'OPS$') ORAUSER, 

decode(LOCKED_MODE,0,'No Lock', 

1,'No Lock', 

2,'Row-Share', 

3,'Row-Exclusive', 

4,'Share', 

5,'Share-Row-Exclusive', 

6,'Exclusive','No Lock') LOCKED_MODE, 

process 

from dba_objects a,gv$locked_object b 

where a.object_id=b.object_id and object_name in ('&1') 

order by 2,sid 

/  

        

set pagesize 500 

set linesize 200 

col message_text for a120 

col HOST_ID for a35 

col ORIGINATING_TIMESTAMP for a32 

select HOST_ID,originating_timestamp,message_text 

from sys.x$dbgalertext 

where originating_timestamp > sysdate-2 and message_text like'%ORA-%' 

order by ORIGINATING_TIMESTAMP;