No image

Oracle Monitoring Scripts 5

1. ll

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

!date
set pause off
set heading off
set linesize 250
col csid for a28
col pid for a25
col event for a100
col osuser for a10
col program for a20
col comments for a25
col npid for 999999999
set pages 24
select
a.sid||'-'||a.serial#||'('||a.osuser||'/'||lower(a.status)||')' csid,
c.event||' {'||
decode(event, 'enqueue', c.p1text||'='||chr(hextodec(c.p1raw)), 
c.p1text||'='||c.p1)|| '} [' || c.p2text||'='||c.p2||
c.p3text||'='||c.p3||']' event,
a.process||'-'||b.spid pid,
decode(instrb(a.program,'@'),0,a.program,
substrb(a.program,1,instrb(a.program,'(')-2)) program,
'command->' ||
decode(a.command,
	0,'n/a',
	1,'create cwtable',
	2,'insert',
	3,'select',
	4,'create cluster',
	5,'alter cluster',
	6,'update',
	7,'delete',
	8,'drop cluster',
	9,'create index',
	10,'drop index',
	11,'alter index',
	12,'drop table',
	13,'create sequence',
	14,'alter sequence',
	15,'alter table',
	16,'drop sequence',
	17,'grant',
	18,'revoke',
	19,'create synonym',
	20,'drop synonym',
	21,'create view',
	22,'drop view',
	23,'validate index',
	24,'create procedure',
	25,'alter procedure',
	26,'lock table',
	27,'no operation',
	28,'rename',
	29,'comment',
	30,'audit',
	31,'noaudit',
	32,'create database link',
	33,'drop database link',
	34,'create database',
	35,'alter database',
	36,'create rollback segment',
	37,'alter rollback segment',
	38,'drop rollback segment',
	39,'create tablespace',
	40,'alter tablespace',
	41,'drop tablespace',
	42,'alter session',
	43,'alter user',
	44,'commit',
	45,'rollback',
	46,'savepoint',
	47,'pl/sql execute',
	48,'set transaction',
	49,'alter system switchlog',
	50,'explain',
	51,'create user',
	52,'create role',
	53,'drop user',
	54,'set role',
	55,'set role',
	56,'create schema',
	57,'create control file',
	58,'alter tracing',
	59,'create trigger',
	60,'alter trigger',
	61,'drop trigger',
	62,'analyze table',
	63,'analyze index',
	64,'analyze cluster',
	65,'create profile',
	66,'drop profile',
	67,'alter profile',
	68,'drop procedure',
	69,'drop procedure',
	70,'alter resource cost',
	71,'create snapshot log',
	72,'alter snapshot log',
	73,'drop snapshot log',
	74,'create snapshot',
	75,'alter snapshot',
	76,'drop snapshot',
	79,'alter role',
	85,'truncate table',
	86,'truncate cluster',
	88,'alter view',
	91,'create function',
	92,'alter function',
	93,'drop function',
	94,'create package',
	95,'alter package',
	96,'drop package',
	97,'create package body',
	98,'alter package body',
	99,'frop package body',
	'UNKNOWN'
) comments
from v$session a, v$process b, v$session_wait c
where a.paddr = b.addr
and a.sid = c.sid and
event != 'SQL*Net message from client'
and event != 'rdbms ipc message'
and event != 'pmon timer'
and event != 'smon timer'
and event != 'lock manager wait for remote message'
and   event not like 'Null%'
order by c.event, a.sid
/

select /*+ rule */
 to_number(sid)||'   [ '|| type || ' ]   [ '|| id1 || ' ]   [ '|| id2 || ' ]   [ ' ||
 decode(lmode,						 -- Lock mode in which the session holds the lock:	
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(lmode)) || ' ]   [ ' ||
decode(request,						 -- Lock mode in which the process requests the lock:		
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(request)) || ' ]   [ ' ||
decode(block,						 -- The lock is blocking another lock
                0, 'NONE',  
                1, 'Blocking',      
                2, 'Global',        
                to_char(block)) || ' ]'
from v$lock
where type in ( 'ST', 'DX', 'TM', 'TX' , 'SQ' )
and request > 0 or block = 1
order by type, id1, sid, lmode
/
exit

2. llib

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select
       waiter.sid   waiter,
       waiter.event wevent,
       to_char(blocker_event.sid)||','||to_char(blocker_session.serial#) blocker,
       substr(decode(blocker_event.wait_time,
                     0, blocker_event.event,
                    'ON CPU'),1,30) bevent
from
       x$kglpn p,
       gv$session      blocker_session,
       gv$session_wait waiter,
       gv$session_wait blocker_event
where
          p.kglpnuse=blocker_session.saddr
   and p.kglpnhdl=waiter.p1raw
   and waiter.event in ( 'library cache pin' , 
                          'library cache lock' ,
                          'library cache load lock')
   and blocker_event.sid=blocker_session.sid
   and waiter.sid != blocker_event.sid
order by
      waiter.p1raw,waiter.sid
/

3. lll

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SELECT A.entr_no, '00', '00',
A.fee_atcl_cd, '0', A.payr_no,'000',
A.sale_itm_cd, A.sale_cl_cd, A.sale_br, A.gds_cd, A.svc_cd,
sum(decode(A.ck_date||A.cre_mth, '200010A',
decode(A.month_gap,0, A.one_time_fee+A.other_time_fee*(A.req_div_cnt-1),
A.other_time_fee*(A.month_gap+A.req_div_cnt)),
decode(A.month_gap,0,A.one_time_fee,A.other_time_fee))) amt
FROM
( SELECT a.*,
 decode(a.cls_dt, null,
 decode(d.entr_no, null,'200010',
 decode(a.fee_itm_cd,
 d.fee_itm_cd, substr(D.d2,1,6), '200010')),
 decode(a.fee_itm_cd,
  '079',
  decode(sign(months_between(to_date('200010','yyyymm'),
to_date(substr(a.cls_dt,1,6),'yyyymm')) - 7),-1,
greatest(substr(nvl(a.cls_dt,'0'),1,6),
substr(a.eft_occr_dt,1,6))),
'090',
decode(sign(months_between(to_date('200010','yyyymm'),
to_date(substr(a.cls_dt,1,6),'yyyymm')) - 7),-1,
greatest(substr(nvl(a.cls_dt,'0'),1,6),
substr(a.eft_occr_dt,1,6))),
substr(a.cls_dt,1,6))) as c_cls_dt,
decode(a.cls_dt, null,
decode(a.fee_itm_cd,
d.fee_itm_cd,
decode(d.entr_no, null, ' ', substr(d.d2,1,6)), ' '),
substr(a.cls_dt,1,6)) ck_date,
decode(a.mast_st_cd, '2', 'A',
decode(d.entr_no, null, 'C',
decode(a.fee_itm_cd, d.fee_itm_cd, 'A', 'C'))) cre_mth,
decode(a.fee_itm_cd, d.fee_itm_cd, nvl(d.d1,'0'), '0') d1,
decode(a.fee_itm_cd, d.fee_itm_cd, nvl(d.d2,'9'), '9') d2,
decode(a.fee_itm_cd, d.fee_itm_cd,
decode(d.d1, d.d2, '200010'), a.eft_occr_dt) ck_eft_occr_dt
 FROM
         ( SELECT a.entr_no, d.payr_no, a.fee_grp_cd fee_itm_cd,
a.sale_itm_cd, a.fee_itm_cd fee_atcl_cd, a.eft_occr_dt,
d.ms_gds_cd gds_cd, a.svc_cd,
         nvl(a.sale_bo_cd,d.sale_bo_cd) as sale_br,
decode(a.one_pay_mthd_cd,
'1', a.other_time_fee, a.one_time_fee) one_time_fee,
                         decode(a.one_pay_mthd_cd,
'1',a.req_div_cnt-1,a.req_div_cnt) req_div_cnt,
         a.other_time_fee , d.cls_dt, e.sale_cl_cd,
 months_between(to_date(greatest(substr(a.eft_occr_dt,1,6),
substr(d.bill_strt_dd,1,6)),'yyyymm'),
to_date('200010','yyyymm')) as month_gap,
a.entr_no||a.fee_grp_cd check_1, d.mast_st_cd
FROM cconetim_200010 a,
	ccenterm_200010 d,
	ccaccntm_200010 e,
	cm0fitmc f
where a.entr_no                 = d.entr_no
AND a.fee_grp_cd                = f.fee_itm_cd
AND f.itm_grp_cd               = '가입'
AND d.payr_no = e.payr_no
) a,
( SELECT A.entr_no, A.fee_itm_cd,
A.entr_no||A.fee_itm_cd check_1,
A.d1, A.d2
FROM ( SELECT A.entr_no,
A.fee_itm_cd,
nvl(substr(max(A.eft_occr_dt),1,6),'0') d1, '200010' d2
FROM ( SELECT a.entr_no,
decode(c.rlt_itm_cd, NULL, a.fee_grp_cd, c.rlt_itm_cd)
fee_itm_cd,
a.eft_occr_dt
FROM cconetim_200010 a, cm0fitmc c
WHERE a.fee_grp_cd = c.fee_itm_cd
AND c.itm_typ_cd = 'A01'
AND a.eft_occr_dt < to_char(add_months(
to_date('200010','yyyymm'),1),'yyyymm')
) A
GROUP BY A.entr_no, A.fee_itm_cd ) A ) d
WHERE a.check_1 = d.check_1(+) ) A
WHERE A.month_gap <= 0
AND A.c_cls_dt >= '200010'
AND (A.month_gap + (A.req_div_cnt - 1) ) >= 0
AND substr(A.ck_eft_occr_dt,1,6) between substr(A.d1,1,6) and substr(A.d2,1,6)
GROUP BY A.entr_no, '0000000000', '00000000000000000000',
A.fee_atcl_cd, '0', A.payr_no, A.sale_itm_cd, A.sale_cl_cd,
A.sale_br, A.gds_cd, A.svc_cd

4. llll

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

explain plan set statement_id = 'wthlord' for
SELECT A.entr_no, '00', '00',
A.fee_atcl_cd, '0', A.payr_no,'000',
A.sale_itm_cd, A.sale_cl_cd, A.sale_br, A.gds_cd, A.svc_cd,
sum(decode(A.ck_date||A.cre_mth, '200010A',
decode(A.month_gap,0, A.one_time_fee+A.other_time_fee*(A.req_div_cnt-1),
A.other_time_fee*(A.month_gap+A.req_div_cnt)),
decode(A.month_gap,0,A.one_time_fee,A.other_time_fee))) amt
FROM
( SELECT a.*,
 decode(a.cls_dt, null,
 decode(d.entr_no, null,'200010',
 decode(a.fee_itm_cd,
 d.fee_itm_cd, substr(D.d2,1,6), '200010')),
 decode(a.fee_itm_cd,
  '079',
  decode(sign(months_between(to_date('200010','yyyymm'),
to_date(substr(a.cls_dt,1,6),'yyyymm')) - 7),-1,
greatest(substr(nvl(a.cls_dt,'0'),1,6),
substr(a.eft_occr_dt,1,6))),
'090',
decode(sign(months_between(to_date('200010','yyyymm'),
to_date(substr(a.cls_dt,1,6),'yyyymm')) - 7),-1,
greatest(substr(nvl(a.cls_dt,'0'),1,6),
substr(a.eft_occr_dt,1,6))),
substr(a.cls_dt,1,6))) as c_cls_dt,
decode(a.cls_dt, null,
decode(a.fee_itm_cd,
d.fee_itm_cd,
decode(d.entr_no, null, ' ', substr(d.d2,1,6)), ' '),
substr(a.cls_dt,1,6)) ck_date,
decode(a.mast_st_cd, '2', 'A',
decode(d.entr_no, null, 'C',
decode(a.fee_itm_cd, d.fee_itm_cd, 'A', 'C'))) cre_mth,
decode(a.fee_itm_cd, d.fee_itm_cd, nvl(d.d1,'0'), '0') d1,
decode(a.fee_itm_cd, d.fee_itm_cd, nvl(d.d2,'9'), '9') d2,
decode(a.fee_itm_cd, d.fee_itm_cd,
decode(d.d1, d.d2, '200010'), a.eft_occr_dt) ck_eft_occr_dt
 FROM
         ( SELECT a.entr_no, d.payr_no, a.fee_grp_cd fee_itm_cd,
a.sale_itm_cd, a.fee_itm_cd fee_atcl_cd, a.eft_occr_dt,
d.ms_gds_cd gds_cd, a.svc_cd,
         nvl(a.sale_bo_cd,d.sale_bo_cd) as sale_br,
decode(a.one_pay_mthd_cd,
'1', a.other_time_fee, a.one_time_fee) one_time_fee,
                         decode(a.one_pay_mthd_cd,
'1',a.req_div_cnt-1,a.req_div_cnt) req_div_cnt,
         a.other_time_fee , d.cls_dt, e.sale_cl_cd,
 months_between(to_date(greatest(substr(a.eft_occr_dt,1,6),
substr(d.bill_strt_dd,1,6)),'yyyymm'),
to_date('200010','yyyymm')) as month_gap,
a.entr_no||a.fee_grp_cd check_1, d.mast_st_cd
FROM cconetim_200010 a,
	ccenterm_200010 d,
	ccaccntm_200010 e,
	cm0fitmc f
where a.entr_no                 = d.entr_no
AND a.fee_grp_cd                = f.fee_itm_cd
AND f.itm_grp_cd               = '가입'
AND d.payr_no = e.payr_no
) a,
( SELECT A.entr_no, A.fee_itm_cd,
A.entr_no||A.fee_itm_cd check_1,
A.d1, A.d2
FROM ( SELECT A.entr_no,
A.fee_itm_cd,
nvl(substr(max(A.eft_occr_dt),1,6),'0') d1, '200010' d2
FROM ( SELECT a.entr_no,
decode(c.rlt_itm_cd, NULL, a.fee_grp_cd, c.rlt_itm_cd)
fee_itm_cd,
a.eft_occr_dt
FROM cconetim_200010 a, cm0fitmc c
WHERE a.fee_grp_cd = c.fee_itm_cd
AND c.itm_typ_cd = 'A01'
AND a.eft_occr_dt < to_char(add_months(
to_date('200010','yyyymm'),1),'yyyymm')
) A
GROUP BY A.entr_no, A.fee_itm_cd ) A ) d
WHERE a.check_1 = d.check_1(+) ) A
WHERE A.month_gap <= 0
AND A.c_cls_dt >= '200010'
AND (A.month_gap + (A.req_div_cnt - 1) ) >= 0
AND substr(A.ck_eft_occr_dt,1,6) between substr(A.d1,1,6) and substr(A.d2,1,6)
GROUP BY A.entr_no, '0000000000', '00000000000000000000',
A.fee_atcl_cd, '0', A.payr_no, A.sale_itm_cd, A.sale_cl_cd,
A.sale_br, A.gds_cd, A.svc_cd
;

5. lock

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

column sid            format 9999
column lock_type      format a20
column MODE_HELD      format a11
column MODE_REQUESTED format a10
column LOCK_ID1       format a8
column LOCK_ID2       format a8

--select /*+ rule */
--   a.sid,
--   decode(a.type,
--   'MR', 'Media Recovery',
--   'RT', 'Redo Thread',
--   'UN', 'User Name',
--   'TX', 'Transaction',
--   'TM', 'DML',
--   'UL', 'PL/SQL User Lock',
--   'DX', 'Distributed Xaction',
--   'CF', 'Control File',
--   'IS', 'Instance State',
--   'FS', 'File Set',
--   'IR', 'Instance Recovery',
--   'ST', 'Disk Space Transaction',
--   'IR', 'Instance Recovery',
--   'ST', 'Disk Space Transaction',
--   'TS', 'Temp Segment',
--   'IV', 'Library Cache Invalidation',
--   'LS', 'Log Start or Switch',
--   'RW', 'Row Wait',
--   'SQ', 'Sequence Number',
--   'TE', 'Extend Table',
--   'TT', 'Temp Table',
--   a.type) lock_type,
--   decode(a.lmode,
--   0, 'None',           /* Mon Lock equivalent */
--   1, 'Null',           /* N */
--   2, 'Row-S (SS)',     /* L */
--   3, 'Row-X (SX)',     /* R */
--   4, 'Share',          /* S */
--   5, 'S/Row-X (SSX)',  /* C */
--   6, 'Exclusive',      /* X */
--   to_char(a.lmode)) mode_held,
--   decode(a.request,
--   0, 'None',           /* Mon Lock equivalent */
--   1, 'Null',           /* N */
--   2, 'Row-S (SS)',     /* L */
--   3, 'Row-X (SX)',     /* R */
--   4, 'Share',          /* S */
--   5, 'S/Row-X (SSX)',  /* C */
--   6, 'Exclusive',      /* X */
--   to_char(a.request)) mode_requested,
--   to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
--from v$lock a
----where a.type not in ('MR', 'DM', 'RT')
--where type in ( 'ST', 'DX', 'TM', 'TX' , 'SQ' )
--and   request > 0 or block = 1
--order by a.sid
--/
select /*+ rule */
       to_number(sid)||'   [ '|| type || ' ]   [ '|| id1 || ' ]   [ '|| id2 || ' ]   [ ' ||
       decode(lmode,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(lmode)) || ' ]   [ ' ||
       decode(request,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(request)) || ' ]   [ ' ||
       decode(block,
              0, 'NONE',
              1, 'Blocking',
              2, 'Global',
              to_char(block)) || ' ]' lockinfo
from  v$lock
where type in ( 'ST', 'DX', 'TM', 'TX' , 'SQ' )
and   request > 0 or block = 1
order by type, id1, sid, lmode
/

column username       cle
column sid            cle
column lock_type      cle
column MODE_HELD      cle
column MODE_REQUESTED cle
column LOCK_ID1       cle
column LOCK_ID2       cle

6. lock_lib

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select
       waiter.sid   waiter,
       waiter.event wevent,
       to_char(blocker_event.sid)||','||to_char(blocker_session.serial#) blocker,
       substr(decode(blocker_event.wait_time,
                     0, blocker_event.event,
                    'ON CPU'),1,30) bevent
from
       x$kglpn p,
       gv$session      blocker_session,
       gv$session_wait waiter,
       gv$session_wait blocker_event
where
          p.kglpnuse=blocker_session.saddr
   and p.kglpnhdl=waiter.p1raw
   and waiter.event in ( 'library cache pin' , 
                                      'library cache lock' ,
                                      'library cache load lock')
   and blocker_event.sid=blocker_session.sid
   and waiter.sid != blocker_event.sid
order by
      waiter.p1raw,waiter.sid;

--SELECT SID,SERIAL#,USERNAME,TERMINAL,PROGRAM,osuser
--FROM V$SESSION
--WHERE SADDR in (
--                SELECT KGLLKSES 
--                FROM X$KGLLK LOCK_A
--                WHERE KGLLKREQ = 0
--                AND EXISTS (
--                            SELECT LOCK_B.KGLLKHDL 
--                            FROM X$KGLLK LOCK_B
--                            WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
--                            AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
--                            AND KGLLKREQ > 0)
--               )
--;

7. lock_tree

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

COL HOLD_SID          FOR A8  HEADING "HOLD|SID"    
COL WAIT_SID          FOR A8  HEADING "WAIT|SID" 
COL LOCKED_OBJ        FOR A42 HEADING "LOCKED|OBJECT"
COL HOLD_MODE         FOR A7  HEADING "HOLD|LOCK|MODE"  
COL REQ_MODE          FOR A7  HEADING "REQUEST|LOCK|MODE"
COL PROGRAM           FOR A18 TRUNCATE
COL STATUS            FOR A3 TRUNCATE HEADING STA|TUS
COL GB                FOR A4 HEADING "H/W|TYPE"
COL SECONDS_IN_WAIT   FOR 99999 HEADING "WAIT|TIME" 
COL TYPE              FOR A4  HEADING      "LOCK|TYPE"
COL USERNAME          FOR A8  TRUNCATE

select /*+ no_merge(v) ordered */
       decode(v.hold_sid,null,'','('||v.inst_id||')'||v.hold_sid)            Hold_Sid
      ,decode(v.wait_sid,null,'','^','▽','('||v.inst_id||')'||v.wait_sid)   WAIT_SID
      ,v.gb    
--    ,v.inst_id             "Instance"
      ,sw.seconds_in_wait    
      ,v.type           
      ,DECODE( V.LMODE ,
         0, 'NONE'  ,          -- 'NONE' ,
         1, 'NULL'  ,          -- 'NULL' ,
         2, 'ROW SH',          -- 'ROW SHARE' ,
         3, 'ROW EX',          -- 'ROW EXCLUSIVE' ,
         4, 'SHARE' ,          -- 'SHARE' ,
         5, 'SH R X',          -- 'SHARE ROW EXCLUSIVE' ,
         6, 'EX',              -- 'EXCLUSIVE' ,
         TO_CHAR( V.LMODE ) )       HOLD_MODE
      ,DECODE( V.REQUEST ,
         0,  'NONE'  ,         -- 'NONE' ,
         1,  'NULL'  ,         -- 'NULL' ,
         2,  'ROW SH',         -- 'ROW SHARE' ,
         3,  'ROW EX',         -- 'ROW EXCLUSIVE' ,
         4,  'SHARE' ,         -- 'SHARE' ,
         5,  'SH R X',         -- 'SHARE ROW EXCLUSIVE' ,
         6,  'EX',             -- 'EXCLUSIVE' ,
         to_char( v.request ) )       REQ_MODE
       ,(select object_name||'('||substr(object_type,1,1)||')' from dba_objects do where do.object_id = s.row_wait_obj# ) LOCKED_OBJ
--     ,v.id1                          "ID1"
--     ,v.id2                          "ID2"
       ,s.username
--       ,to_char(s.sid)||','||to_char(s.serial# )  as  sid1
       ,s.status                                   as  status
       ,s.program                       
/*
       ,substr(s.sql_trace,1,2)||'/'||substr(s.sql_trace_waits,1,1)||'/'||substr(s.sql_trace_binds,1,1)                              as sql_trace1 
--     ,trunc(p.pga_alloc_mem/1024/1024)          as  pga1
       ,decode(substr(s.action,1,4),'FRM:',s.module||'(Form)','Onli',s.module||'(Form)','Conc',s.module||'(Conc)',s.module )         as module1
--     ,decode(s.blocking_session,null,'',substr(s.blocking_session_status,1,3)||'('||s.blocking_instance||')'||(s.blocking_session -1) ) as blocking1 
       ,s.seconds_in_wait                         as  seconds_in_wait1
       ,substr(s.event,1,25)                      as wait_event1
       ,last_call_et                              as lce1
       ,trim((select substr(sql_text,1,20) from gv$sql sq where sq.inst_id = s.inst_id and sq.sql_id  = s.sql_id and rownum= 1 ))                                as sql_text1
       ,s.machine                                 as machine1
       ,s.osuser                                  as osuser1
       ,s.terminal                                as user_info1
       ,to_char(logon_time,'yyyymmdd HH24:MI:SS') as logon1
       ,s.process                                 as cpid1
       ,p.spid                                    as spid1
       ,'kill -9 '||p.spid                        as kill1
       ,'alter system kill session '||''''||s.sid||','||s.serial#||''''||' ; ' as kill2
*/
from (
      select rownum, inst_id, decode(request,0,to_char(sid)) hold_sid,
             decode(request,0,'^',to_char(sid)) wait_sid, sid, 
             DECODE(REQUEST,0,'HOLD','WAIT') GB,
             id1,id2, lmode, request, type
      from   gv$lock
      where  (id1,id2,type ) in (select id1,id2,type from gv$lock where lmode = 0)
     ) v , gv$session s, gv$session_wait sw,gv$process         p
where v.sid = s.sid
and   v.inst_id = s.inst_id
and   s.sid = sw.sid
and   s.inst_id = sw.inst_id
and   s.paddr   = p.addr
and   s.inst_id = p.inst_id
order by v.id1, v.request, sw.seconds_in_wait desc;


COL HOLD_SID          clear
COL WAIT_SID          clear
COL LOCKED_OBJ        clear
COL HOLD_MODE         clear
COL REQ_MODE          clear
COL PROGRAM           clear
COL STATUS            clear
COL GB                clear
COL SECONDS_IN_WAIT   clear
COL TYPE              clear
COL username          clear

8. lock2_e

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col sid             for 9999
col blocking_others for a12
col id1             for 99999999999
col id2             for 9999999999
col mode_held       for a15
col mode_requested  for a15

set pause off

select /*+ rule */
 l.sid, l.type, l.id1, l.id2,
decode(l.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(lmode)) mode_held,
decode(l.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(request)) mode_requested,
decode(l.block,
                0, 'NONE',  /* Not blocking any other processes */
                1, 'Blocking',      /* This lock blocks other processes */
                2, 'Global',        /* This lock is global, so we can't tell */
                to_char(block)) blocking_others,
l.ctime, decode(l.type, 'TM', o.name, NULL) oname
from v$lock l, sys.obj$ o
-- where type in ( 'DX', 'TM', 'TX', 'SQ' )
where l.type != 'MR'
and l.id1 = o.obj#(+)
-- order by type, id1, sid, lmode
order by sid, type, id1, sid, lmode
/

select /*+ rule */
 l.sid, l.type, l.id1, l.id2,
decode(l.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(lmode)) mode_held,
decode(l.request,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(request)) mode_requested,
decode(l.block,
                0, 'NONE',  /* Not blocking any other processes */
                1, 'Blocking',      /* This lock blocks other processes */
                2, 'Global',        /* This lock is global, so we can't tell */
                to_char(block)) blocking_others,
l.ctime, decode(l.type, 'TM', o.name, NULL) oname
from v$lock l, sys.obj$ o
-- where type in ( 'DX', 'TM', 'TX', 'SQ' )
where l.type != 'MR'
and (request > 0 or l.block = 1)
and l.id1 = o.obj#(+)
-- order by type, id1, sid, lmode
order by sid, type, id1, sid, lmode
/

col sid             clear
col blocking_others clear
col id2             clear
col id1             clear
col mode_held       clear
col mode_requested  clear

9. lockinfo

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col term    for a20
col program for a30  trunc

select a1.sid                                       as hsid,
       a1.serial#                                   as ser#,
       b2.sid                                       as wsid,
       a2.serial#                                   as ser#,
       decode(b2.sid,null,a1.terminal,a2.terminal)  as term,
       decode(b2.sid,null,substr(a1.program,1,28),
                          substr(a2.program,1,28))  as program,
       decode(b2.sid,null,b1.id1,b3.id1)            as objid,
       decode(b2.sid,null,d1.name,d2.name)          as objnm
from   v$session a1, v$session a2,
       v$lock    b1, v$lock    b2, v$lock    b3,
       sys.obj$  d1, sys.obj$  d2
where  a1.lockwait is null
and    a1.sid > 6
and    a1.sid = b1.sid
and    b1.id1 = d1.obj#(+)
and    d1.owner#(+) > 5
and    b1.id2 = b2.id2(+)
and    b2.id2(+) > 0
and    ((b2.sid is null) or (b2.sid != a1.sid))
and    b2.sid = a2.sid(+)
and    a2.sid = b3.sid(+)
and    b3.id1 = d2.obj#(+)
and    b3.id2(+) = 0
order by hsid,wsid desc
/

col term    clear
col program clear

10. locktree

-- writer : ezis.cloud (noname)
-- Description  : show lock tree
-- Requirements : 

col id1 format a20
col id2 format a10
col lmode format a5
col request format a5
col h_mode format a5
col w_mode format a5

with holder as (
  select
    sid,
    type, 
    (case when (type = 'TM') then 
      (select object_name||'('||id1||')' from all_objects where object_id = id1)
      else id1||'' end) as id1,
    id2||'' as id2, 
    --lmode,
    lmode,
    request,
    ctime
  from
    v$lock
  where
    block = 1
),
waiter as (
  select
    sid,
    type, 
    (case when (type = 'TM') then 
      (select object_name||'('||id1||')' from all_objects where object_id = id1)
      else id1||'' end) as id1,
    id2||'' as id2, 
    lmode,
    request,
    ctime
  from
    v$lock
  where
    block = 0 and
    request > 0
  order by ctime desc
) 
select
  h.type, 
  h.sid as h_sid, 
  decode(h.lmode,6,'X(6)',5,'SRX(5)',4,'S(4)',3,'RX(3)',2,'RS(2)',1,'N(1)',h.lmode) as h_mode,
  w.sid as w_sid,
  decode(w.request,6,'X(6)',5,'SRX(5)',4,'S(4)',3,'RX(3)',2,'RS(2)',1,'N(1)',w.request) as w_mode,
  h.id1,
  h.id2,
  w.ctime as "w_time(cs)"
from
  waiter w, holder h
where
  w.id1 = h.id1 and
  w.id2 = h.id2
order by
  1
;

11. log

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set verify off
COL        H00   FOR 999
COL        H01   FOR 999
COL        H02   FOR 999
COL        H03   FOR 999
COL        H04   FOR 999
COL        H05   FOR 999
COL        H06   FOR 999
COL        H07   FOR 999
COL        H08   FOR 999
COL        H09   FOR 999
COL        H10   FOR 999
COL        H11   FOR 999
COL        H12   FOR 999
COL        H13   FOR 999
COL        H14   FOR 999
COL        H15   FOR 999
COL        H16   FOR 999
COL        H17   FOR 999
COL        H18   FOR 999
COL        H19   FOR 999
COL        H20   FOR 999
COL        H21   FOR 999
COL        H22   FOR 999
COL        H23   FOR 999
COL        TOTAL FOR 9999
COL        DAY   FOR A4
COL        DT    FOR A8 HEADING FIRST|TIME

prompt archive file count

SELECT  to_char(first_time,'yyyymmdd') DT
       ,TO_CHAR(first_time, 'Dy')      Day
       ,COUNT(1)                       Total
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'00',1,null)) H00
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'01',1,null)) H01
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'02',1,null)) H02
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'03',1,null)) H03
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'04',1,null)) H04
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'05',1,null)) H05
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'06',1,null)) H06
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'07',1,null)) H07
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'08',1,null)) H08
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'09',1,null)) H09
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'10',1,null)) H10
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'11',1,null)) H11
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'12',1,null)) H12
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'13',1,null)) H13
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'14',1,null)) H14
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'15',1,null)) H15
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'16',1,null)) H16
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'17',1,null)) H17
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'18',1,null)) H18
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'19',1,null)) H19
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'20',1,null)) H20
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'21',1,null)) H21
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'22',1,null)) H22
       ,count(DECODE(TO_CHAR(first_time, 'hh24'),'23',1,null)) H23
FROM    v$log_history
WHERE   1=1
AND first_time > TRUNC(sysdate - 7)
and THREAD# = &1.
GROUP BY to_char(first_time,'yyyymmdd'), TO_CHAR(first_time, 'Dy')
ORDER BY 1 DESC
;

prompt archive file bytes (M)

SELECT  to_char(first_time,'yyyymmdd')          DT
       ,TO_CHAR(first_time, 'Dy')               Day
       ,sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024   Total
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'00',blocks*block_size,null))/1024/1024/1024 H00
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'01',blocks*block_size,null))/1024/1024/1024 H01
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'02',blocks*block_size,null))/1024/1024/1024 H02
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'03',blocks*block_size,null))/1024/1024/1024 H03
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'04',blocks*block_size,null))/1024/1024/1024 H04
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'05',blocks*block_size,null))/1024/1024/1024 H05
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'06',blocks*block_size,null))/1024/1024/1024 H06
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'07',blocks*block_size,null))/1024/1024/1024 H07
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'08',blocks*block_size,null))/1024/1024/1024 H08
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'09',blocks*block_size,null))/1024/1024/1024 H09
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'10',blocks*block_size,null))/1024/1024/1024 H10
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'11',blocks*block_size,null))/1024/1024/1024 H11
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'12',blocks*block_size,null))/1024/1024/1024 H12
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'13',blocks*block_size,null))/1024/1024/1024 H13
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'14',blocks*block_size,null))/1024/1024/1024 H14
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'15',blocks*block_size,null))/1024/1024/1024 H15
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'16',blocks*block_size,null))/1024/1024/1024 H16
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'17',blocks*block_size,null))/1024/1024/1024 H17
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'18',blocks*block_size,null))/1024/1024/1024 H18
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'19',blocks*block_size,null))/1024/1024/1024 H19
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'20',blocks*block_size,null))/1024/1024/1024 H20
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'21',blocks*block_size,null))/1024/1024/1024 H21
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'22',blocks*block_size,null))/1024/1024/1024 H22
       ,sum(DECODE(TO_CHAR(first_time, 'hh24'),'23',blocks*block_size,null))/1024/1024/1024 H23
FROM    V$ARCHIVED_LOG
WHERE   1=1
AND first_time > TRUNC(sysdate - 7)
and THREAD# = &1.
and dest_id = 1
GROUP BY to_char(first_time,'yyyymmdd'), TO_CHAR(first_time, 'Dy')
ORDER BY 1 DESC
;

COL        H00   CLEAR
COL        H01   CLEAR
COL        H02   CLEAR
COL        H03   CLEAR
COL        H04   CLEAR
COL        H05   CLEAR
COL        H06   CLEAR
COL        H07   CLEAR
COL        H08   CLEAR
COL        H09   CLEAR
COL        H10   CLEAR
COL        H11   CLEAR
COL        H12   CLEAR
COL        H13   CLEAR
COL        H14   CLEAR
COL        H15   CLEAR
COL        H16   CLEAR
COL        H17   CLEAR
COL        H18   CLEAR
COL        H19   CLEAR
COL        H20   CLEAR
COL        H21   CLEAR
COL        H22   CLEAR
COL        H23   CLEAR
COL        TOTAL CLEAR
COL        DAY   CLEAR
COL        DT    CLEAR
set verify on

12. logging

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select 'alter table '||owner||'.'||table_name||' logging;' stmt
from dba_tables
where logging = 'NO'
and table_name not like 'RUPD%'
and owner not in ( 'SYS','SYSTEM' )
union all
select 'alter index '||owner||'.'||index_name||' logging;' stmt
from dba_indexes
where logging = 'NO'
/

13. login

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set    linesize  220 pagesize 9999 timing on tab off time on
set    long          65536
set    longchunksize 65536
set    arraysize     5000
--define _editor='c:\Program Files\Vim\vim74\vim.exe'
define _editor='vim'
define dft_owner='EXBILL'
define dft_nls_date_format='yyyy-mm-dd'
--alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF6';
--alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF6' ;
--alter session set NLS_DATE_FORMAT='&dft_nls_format' ;
--SET    SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SET    SQLPROMPT "_USER> "

14. logmnr_check

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set line 300
set verify off
col name format a50 truncate
col FIRST_TIME format a25 truncate
col NEXT_TIME format a25 truncate

prompt format => YYYY-MM-DD HH24:MI:SS
prompt start time => 
accept start_time
prompt end time   => 
accept end_time

SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') as "FIRST_TIME",
       TO_CHAR(NEXT_TIME, 'YYYY-MM-DD HH24:MI:SS') as "NEXT_TIME",
       TO_CHAR(COMPLETION_TIME , 'YYYY-MM-DD HH24:MI:SS') as "COMPLETION_TIME",
       NAME
  FROM V$ARCHIVED_LOG
 WHERE NAME LIKE '%ora_arch_sec%'
   AND TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') < '&&start_time'
   AND TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') > '&&end_time'
   OR ( TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') < '&&end_time'
        AND TO_CHAR(NEXT_TIME, 'YYYY-MM-DD HH24:MI:SS') > '&&end_time'
        AND NAME LIKE '%ora_arch_sec%')
   OR ( TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') < '&&start_time'
        AND TO_CHAR(NEXT_TIME, 'YYYY-MM-DD HH24:MI:SS') > '&&start_time'
        AND NAME LIKE '%ora_arch_sec%')
/

set verify on
col name clear
col FIRST_TIME clear
col NEXT_TIME clear

15. longops

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN program FORMAT A30 trunc
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       s.program,
       lpad(TRUNC(sl.elapsed_seconds/60),3,' ') || ':' || lpad(MOD(sl.elapsed_seconds,60),2,' ') elapsed,
       lpad(TRUNC(sl.time_remaining/60),3,' ')  || ':' || lpad(MOD(sl.time_remaining,60),2,' ') remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
and    sl.time_remaining > 0
order by 6
/


COLUMN sid clear
COLUMN serial# clear
COLUMN machine clear
COLUMN progress_pct clear
COLUMN elapsed clear
COLUMN remaining clear
COLUMN program clear

16. mem_usage

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

rem This script will show the current PGA, UGA memory size per session.

set pagesize 66
set pause on
set verify off
set feed off
clear screen

column sid heading 'sid' format 999
column username heading 'username' format a8
column pgm heading 'program' format a25
column terminal heading 'terminal' format a8
column pga heading 'PGA session memory' format a11
column uga heading 'UGA session memory' format a11
column pga_sum heading 'SUM PGA mem' format a12
column uga_sum heading 'SUM UGA mem' format a12
column pga_max heading 'Max PGA session memory' format a15
column uga_max heading 'Max UGA session memory' format a15
column pga_m_sum heading 'Sum Max PGA session memory' format a11
column uga_m_sum heading 'Sum Max UGA session memory' format a11

spool ../sess.txt

ttitle '**********< Program Global Area >**********'
ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,
max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;


ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,
sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';


ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,
max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;


ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,
sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';

spool off
exit

17. mem_used

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col program form a40
col spid    form a12
col machine form a22

select *
from (
select p.addr,p.spid,p.program,round(p.PGA_USED_MEM/1024) kbytes, s.sid, s.serial#, s.machine
  from v$process p, v$session s
 where 1=1
   and p.addr = s.paddr (+)
 order by PGA_USED_MEM desc
 ) where rownum <= 20
/

col program clear
col spid    clear
col machine clear

18. myevent

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col event  for a50
select event, total_waits, time_waited
from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
order by 3 desc
;
col event  clear

19. mysid

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select sid from v$mystat where rownum =1
/
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]