No image

Oracle Monitoring Scripts 8

1. rt01

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

SELECT /*+ use_hash( a b c d e f g h i j k) full(a) full(b) full(c) full(d) full(e) full(f) full(g) full(h) full(i) full(j) full(k) parallel
(a 4) parallel(b 4) parallel(c 4) parallel(d 4) parallel(e 4) parallel(f 4) parallel(g 4) parallel(h 4) parallel(i 4) parallel(j 4) parallel
(k 4) */
k.*,a.*,b.*,c.*
  FROM trtbta15 partition (p000) k,
       trch10vs a,
       trlh10vs b,
       trsc10vs c,
       trcl10vs d,
       trrs10vs e,
       trcb10vs f,
       trcw10vs g,
       trcm10vs h,
       trlm10vs i,
       trex10vs j
 WHERE k.ta15_sys_org_c = '001'
   AND k.ta15_tr_part_key = '00'
   AND k.ta15_accno_dc = '01'
   AND k.ta15_so_yn = 'N'
   AND k.ta15_cd_mbyn = '1'
   AND k.ta15_sys_org_c = a.trch_sys_org_c(+)   AND k.ta15_accno = a.trch_accno(+)
   AND k.ta15_sys_org_c = b.trlh_sys_org_c(+)   AND k.ta15_accno = b.trlh_accno(+)
   AND k.ta15_sys_org_c = c.trsc_sys_org_c(+)   AND k.ta15_accno = c.trsc_accno(+)
   AND k.ta15_sys_org_c = d.trcl_sys_org_c(+)   AND k.ta15_accno = d.trcl_accno(+)
   AND k.ta15_sys_org_c = e.trrs_sys_org_c(+)   AND k.ta15_accno = e.trrs_accno(+)
   AND k.ta15_sys_org_c = f.trcb_sys_org_c(+)   AND k.ta15_accno = f.trcb_accno(+)
   AND k.ta15_sys_org_c = g.trcw_sys_org_c(+)   AND k.ta15_accno = g.trcw_accno(+)
   AND k.ta15_sys_org_c = h.trcm_sys_org_c(+)   AND k.ta15_accno = h.trcm_accno(+)
   AND k.ta15_sys_org_c = i.trlm_sys_org_c(+)   AND k.ta15_accno = i.trlm_accno(+)
   AND k.ta15_sys_org_c = j.trex_sys_org_c(+)   AND k.ta15_accno = j.trex_accno(+)
   
/

2. running

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

col LAST_date    for a14
col THIS_date    for a14
col what        for a48 truncate
col SCHEMA_USER for a10
col sid for a10
col osuser for a10

select r.sid||','||s.serial# sid, r.job, r.failures, to_char(r.last_date,'mm-dd hh24:mi:ss') last_date, 
       to_char(r.this_date,'mm-dd hh24:mi:ss') this_date, j.SCHEMA_USER, j.what,s.osuser
from dba_jobs_running r, dba_jobs j, v$session s
where r.job = j.job
and r.sid = s.sid (+)
order by r.THIS_DATE;

col LAST_date    clear
col THIS_date    clear
col what        clear
col SCHEMA_USER clear
col sid clear
col osuser clear

3. s

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

set    verify off long 500

col    col1       for a36 heading "Session info1" 
col    col2       for a50 heading "Session info2"
col    sql_text   for a70 wrap

set    termout off
col    col_command         new_value col_command
col    col_elap            new_value col_elap
col    col_SQL_EXEC_START  new_value col_SQL_EXEC_START

select
       case when version  >= 11 then 'select command_name from v$sqlcommand where command_type = b.command'    
            else 'NULL'
       end as col_command
      ,case when version  >= 11 then 'to_char((sysdate-sql_exec_start)*24*60,''FM999.00'')'
            else                     'last_call_et'
       end as col_elap
      ,case when version  >= 11 then 'sql_exec_start'
            else                     'null'
       end as col_SQL_EXEC_START
from  (select to_number(substr(version,1,instr(version,'.',1,2)-1)) as version from v$instance)
/

col    col_command    clear
col    col_elap       clear
alter  session set nls_date_format='yyyy-mm-dd hh24:mi:ss' ;
set    termout on

select          'sid       : '         ||b.sid||','||b.SERIAL#
     ||chr(10)||'status    : '         ||b.status
     ||chr(10)||'username  : '         ||b.username
     ||chr(10)||'process   : '         ||b.process
     ||chr(10)||'spid      : kill -9 ' ||p.spid
     ||chr(10)||'osuser    : '         ||b.osuser
     ||chr(10)||'lck_stat  : '         ||BLOCKING_SESSION_STATUS -- 9i:comment
     ||chr(10)||'lck_inst  : '         ||BLOCKING_INSTANCE       -- 9i:comment
     ||chr(10)||'lck_sid   : '         ||BLOCKING_SESSION        -- 9i:comment
     ||chr(10)||'PDML_STAT : '         ||PDML_STATUS
     ||chr(10)||'PDDL_STAT : '         ||PDDL_STATUS
     ||chr(10)||'PQ_STAT   : '         ||PQ_STATUS  
     ||chr(10)||'command   : '         || (&col_command)
     ||chr(10)||'elapsed(m): '         || (&col_elap)
     ||chr(10)||'WAIT_OBJ# : '         ||(select object_name||'.'||SUBOBJECT_NAME from dba_objects where object_id = ROW_WAIT_OBJ# and rownum = 1)
       as col1,
                'terminal : '          ||b.terminal
     ||chr(10)||'module   : '          ||b.module
     ||chr(10)||'action   : '          ||b.action
     ||chr(10)||'machine  : '          ||b.machine
     ||chr(10)||'program  : '          ||b.program
     ||chr(10)||'pr       : '          ||to_char(si.physical_reads  ,'99,999,999,999,999')
     ||chr(10)||'cr       : '          ||to_char(si.consistent_gets ,'99,999,999,999,999')
     ||chr(10)||'logon_tm : '          ||b.LOGON_TIME
     ||chr(10)||'q_start  : '          ||&col_SQL_EXEC_START
     ||chr(10)||'sqll_addr,hash :'     ||sql_address || ',' || sql_hash_value    
     ||chr(10)||'sql_id   : '          ||b.sql_id   ||' - '||b.SQL_CHILD_NUMBER  -- 9i:comment
     ||chr(10)||'pre_sql  : '          ||prev_sql_id||' - '||b.PREV_CHILD_NUMBER -- 9i:comment
     ||chr(10)||'event    : '          ||w.event
     ||chr(10)||RPAD(W.P1TEXT,9,' ')   ||': '|| w.P1
     ||chr(10)||RPAD(W.P2TEXT,9,' ')   ||': '|| w.P2
     ||chr(10)||RPAD(W.P3TEXT,9,' ')   ||': '|| w.P3
       as col2
      ,case RAWTOHEX(b.sql_address)
         when '00' then (select sql_fulltext from v$sql A where sql_id = b.prev_sql_id and rownum=1 )  
         else           (select sql_fulltext from v$sql A where sql_id = b.sql_id      and rownum=1 )
       end  sql_text
from   v$session      b
     , v$session_wait w
     , v$sess_io      si
     , v$process      p
     , v$sesstat      st
where  b.sid             = &1
and    b.paddr           = p.addr (+)
and    b.sid             = w.sid
and    b.sid             = si.sid
and    b.sid             = st.sid (+)
and    st.STATISTIC# (+) = 9
/

--select
--       b.object_id
--      ,b.owner owner
--      ,b.object_name
--from v$lock a, dba_objects b
--where a.type = 'TM'
--and a.lmode > 0
--and a.request = 0
--and a.sid = &1
--and a.id1 = b.object_id
--;

col    col1       clear
col    col2       clear
col    sql_text   clear

set verify on

4. s1

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

select count(*) from &tab;

5. scn

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

select open_mode,scn_to_timestamp(CURRENT_SCN) from v$database;

6. scn_hist

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

select thread,time_mp,to_char(time_dp,'dd-mm-yy hh24:mi:ss') 
as time,scn_bas from smon_scn_time where 
time_dp in (select time_dp from smon_scn_time) ;

7. sdb_link

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

set echo on

drop public database link TOSDB;
create public database link TOSDB connect to ONSEL identified by adj0511 using 'CSS_SDB';

set echo off

8. seg

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

set    verify off

col    owner                    for a10
col    ts_name                  for a23
col    SEGMENT_NAME             for a43 new_value 1
col    p_name                   for a20
col    max_ext                  for 999999999
col    mbytes                   for 9999999
col    blocks                   for 9,999,999

start  arg2.sql

SELECT OWNER||'.'||segment_name as  segment_name
     , partition_name           as  p_name
     , TABLESPACE_NAME          as  ts_name
     , bytes/1024/1024          as  mbytes
     , blocks
     , INITIAL_EXTENT/1024/1024 as  INIT
     , NEXT_EXTENT/1024/1024    as  next
     , extents                  as  extents
     , header_file              as  header_file
FROM   dba_segments
WHERE  segment_name LIKE upper('&&1')||'%'
and    segment_type like upper('&&2')||'%'
ORDER  BY segment_name, partition_name
/

col    owner                    clear
col    ts_name                  clear
col    SEGMENT_NAME             clear
col    p_name                   clear
col    mbytes                   clear
col    max_ext                  clear
col    blocks                   clear

set    verify on

9. seg2

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

col owner                  for a10
col ts_name                for a23
col SEGMENT_NAME           for a30
col p_name                 for a15
col max_ext                for 999999999
col mbytes                 for 9999999
set verify off

SELECT OWNER||'.'||segment_name segment_name, partition_name p_name, TABLESPACE_NAME ts_name, bytes/1024/1024 mbytes, 
       INITIAL_EXTENT/1024/1024 "INITIAL", NEXT_EXTENT/1024/1024 next, extents, header_file
FROM   dba_segments
WHERE  segment_name LIKE upper('&&1')||'%'
--and segment_type like upper('&&2')||'%'
--and partition_name like '%2005%'
ORDER BY segment_name, partition_name
/

set verify on
col owner                  clear
col ts_name                clear
col SEGMENT_NAME           clear
col p_name                 clear
col mbytes                 clear
col max_ext                clear

10. sel

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

select * from &&tab;

11. sess

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

set    verify  off

start  arg1

col    machine for a34

break  on INST_ID on username on machine

select inst_id,username,machine,count(1) cnt
from   gv$session
where  type <> 'BACKGROUND'
and    username like upper('&1') || '%'
group  by INST_ID,machine,username
order  by 1,2,3
/

col    machine clear
set    verify  on

12. shm

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

select (select to_char(sum(BYTES)/1024,'999,999,999,999') f
rom v$sgastat where POOL = 'shared pool' and name = 'free memory') free_k,
       (select to_char(sum(BYTES)/1024,'999,999,999,999') 
from v$sgastat where POOL = 'shared pool') total
from dual
/

13. shrink

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

alter rollback segment &roll_id shrink to &si
/

14. sid

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

col program    for a30
col username   for a20
select s.sid, s.serial#,s.process,p.spid,s.program, s.username,s.terminal,s.status
from   v$session s,v$process p
where  s.paddr=p.addr
and    s.sid = &sid
/

15. sid_prc

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

select b.osuser,b.process,b.terminal,b.username,sid,SERIAL#,
      (select sql_text from v$sqlarea where address = b.sql_address and hash_value = b.sql_hash_value) sql_text
from   v$session b
where  b.process='&prc'
/

16. sid_sql

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

select b.osuser,b.process,b.terminal,b.username,sid,SERIAL#,b.status,program,to_char(LOGON_TIME,'yyyymmddhh24miss') LOGON_TIME,
      (select sql_text from v$sqlarea where address = b.sql_address and hash_value = b.sql_hash_value) sql_text
from   v$session b
where  b.sid=&1
/

17. sid_sql1

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

select b.osuser,b.process,b.terminal,b.username,
      (select sql_text from v$sqlarea where address = b.sql_address and hash_value = b.sql_hash_value) sql_text
from   v$session b
where  b.sid=&1
/

18. sid1

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

col program    for a30
col username   for a20
select s.sid, s.serial#,s.process,p.spid,s.program, s.username,s.terminal
from   v$session s,v$process p
where  s.paddr=p.addr
and    s.sid = &1
/

19. sort

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

col PROGRAM     for a20 truncate
col osuser      for a10 truncate
col username    for a10 truncate
col MODULE      for a10 truncate
col event       for a20 truncate
col sid         for a10 truncate
col TABLESPACE  for a10 truncate

SELECT b.sid||','||b.serial# sid,b.program,osuser ,b.username ,--MODULE,
       TABLESPACE, SEGTYPE, BLOCKS*ts.blocksize/1024/1024 mbytes, SEGFILE#,
       w.p3,
       w.event 
FROM v$sort_usage a , v$session b, v$session_wait w ,sys.ts$ ts
WHERE a.SESSION_ADDR = b.saddr 
AND b.sid = w.sid 
AND a.TABLESPACE = ts.name 
ORDER BY a.extents DESC
;

col PROGRAM clear
col osuser clear
col username clear
col MODULE clear
col event clear
col sid clear
col TABLESPACE clear

20. sort1

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

set line 150
col inst_id for 999
col program for a10
col sid for 9999
col osuser for a10
col blocks for 9999999999
col sql_text for a60

SELECT a.inst_id,a.username,a.program, a.sid, a.serial#, a.osuser, b.blocks, (b.blocks*8/1024) "Size(MB)", c.sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
and a.inst_id = b.inst_id
and a.inst_id = c.inst_id
ORDER BY b.tablespace, b.blocks;

21. space_usage

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

DECLARE
    v_unformatted_blocks    NUMBER;
    v_unformatted_bytes     NUMBER;
    v_fs1_blocks            NUMBER;
    v_fs1_bytes             NUMBER;
    v_fs2_blocks            NUMBER;
    v_fs2_bytes             NUMBER;
    v_fs3_blocks            NUMBER;
    v_fs3_bytes             NUMBER;
    v_fs4_blocks            NUMBER;
    v_fs4_bytes             NUMBER;
    v_full_blocks           NUMBER;
    v_full_bytes            NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
   segment_owner       =>'CORE_D_ADM'
  ,segment_name        =>'TB_PMPAD01'
  ,segment_type        =>'TABLE'
  ,unformatted_blocks  => v_unformatted_blocks
  ,unformatted_bytes   => v_unformatted_bytes
  ,fs1_blocks          => v_fs1_blocks
  ,fs1_bytes           => v_fs1_bytes
  ,fs2_blocks          => v_fs2_blocks
  ,fs2_bytes           => v_fs2_bytes
  ,fs3_blocks          => v_fs3_blocks
  ,fs3_bytes           => v_fs3_bytes
  ,fs4_blocks          => v_fs4_blocks
  ,fs4_bytes           => v_fs4_bytes
  ,full_blocks         => v_full_blocks
  ,full_bytes          => v_full_bytes
);

DBMS_OUTPUT.PUT_LINE('v_unformatted_blocks = ' || v_unformatted_blocks);
DBMS_OUTPUT.PUT_LINE('v_unformatted_bytes  = ' || v_unformatted_bytes);
DBMS_OUTPUT.PUT_LINE('v_fs1_blocks         = ' || v_fs1_blocks);
DBMS_OUTPUT.PUT_LINE('v_fs1_bytes          = ' || v_fs1_bytes);
DBMS_OUTPUT.PUT_LINE('v_fs2_blocks         = ' || v_fs2_blocks);
DBMS_OUTPUT.PUT_LINE('v_fs2_bytes          = ' || v_fs2_bytes);
DBMS_OUTPUT.PUT_LINE('v_fs3_blocks         = ' || v_fs3_blocks);
DBMS_OUTPUT.PUT_LINE('v_fs3_bytes          = ' || v_fs3_bytes);
DBMS_OUTPUT.PUT_LINE('v_fs4_blocks         = ' || v_fs4_blocks);
DBMS_OUTPUT.PUT_LINE('v_fs4_bytes          = ' || v_fs4_bytes);
DBMS_OUTPUT.PUT_LINE('v_full_blocks        = ' || v_full_blocks);
DBMS_OUTPUT.PUT_LINE('v_full_bytes         = ' || v_full_bytes);

END;
/

set serveroutput on

22. spid

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

set verify off
accept spid prompt 'Please enter spid ( ex : 111 ) : '
select sid from v$session s , v$process p where p.spid = &spid and p.addr = s.paddr
;
set verify on

23. splan

-- writer : ezis.cloud (noname)
-- Description  :  view execute plan 
-- Requirements : 

set    verify off head off
set    long 99999 trimspool on

start  arg
define sql_id=&1
define spoolf=.toto.sql
--     define format='advanced allstats all -alias -projection -outline'
define format='advanced allstats last -alias -projection -outline'

select * from table(dbms_xplan.display_cursor ('&sql_id',null,'&format'));

prompt  
prompt sql_fulltext : &spoolf
set    heading off timing off feedback off pages 0 termout off time off
col    parsing_schema_name new_value parsing_schema_name noprint
col    sql_fulltext  clear
col    sql_fulltext  word_wrap
set    long          327670
set    longchunksize 327670
set    lines         32767
spool  &spoolf
select parsing_schema_name,sql_fulltext from v$sql where sql_id = '&sql_id' and rownum=1;
spool off
@login
.
set termout on
prompt parsing_schema_name : [ alter session set current_schema=&parsing_schema_name ; ]

set    heading on timing on feedback on pages 9999
col    parsing_schema_name clear
col    sql_fulltext        clear
set    verify on
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]