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
/
exit2. 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 clear8. 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 on12. 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 clear19. mysid
-- writer : ezis.cloud (noname)
-- Description :
-- Requirements :
select sid from v$mystat where rownum =1
/