No image

Oracle Monitoring Scripts 7

1. pv_sys

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

set    verify off

col    GRANTEE    for a20
col    PRIVILEGE  for a45
col    ROLES      for a20

set    termout    off
start  arg2
set    termout    on

SELECT GRANTEE, privilege, admin_option, DECODE(grantee, 'PUBLIC', 'PUBLIC', ' ') roles
FROM   SYS.dba_sys_privs
WHERE  GRANTEE IN (upper('&&1'), 'PUBLIC')
UNION
SELECT r.GRANTEE,s.privilege, s.admin_option, r.granted_role
FROM   SYS.dba_sys_privs s, SYS.dba_role_privs r
WHERE  s.grantee= r.granted_role
AND    r.GRANTEE IN (upper('&&1'), 'PUBLIC')
UNION
Select drp.grantee ,drp.granted_role ,drp.admin_option ,drp.default_role
FROM   dba_role_privs drp
WHERE  drp.grantee in (upper('&&1'), 'PUBLIC')
ORDER  BY 1,2
/

col    GRANTEE    clear
col    PRIVILEGE  clear
col    ROLES      clear

set    verify on

2. pv_tab

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

set    verify off

col    owner       for a20
col    table_name  for a20
col    grantee     for a20
col    grantor     for a20
col    PRIVILEGE   for a20

set    termout     off
start  arg2
set    termout     on

SELECT *
FROM   dba_tab_privs
WHERE  table_name = upper('&&1')
order  by owner,grantee,PRIVILEGE
/

col    grantee     clear
col    grantor     clear
col    PRIVILEGE   clear
col    owner       clear
col    table_name  clear
set    verify on

3. pv_user

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

set    verify  off

set    termout off
alter  session set nls_date_format='&dft_nls_date_format' ;
set    termout on
start  arg2

col    username           for  a18
col    default_tablespace for  a35
col    privilege          for  a35
col    account_status     for  a24

break  on username on default_tablespace on created on account_status on temporary_tablespace

select username,account_status,default_tablespace, temporary_tablespace, created,p.privilege 
  from dba_users u,
      (select grantee,privilege from dba_sys_privs
       union all
       select grantee,granted_role from dba_role_privs) p
 where u.username =    p.grantee (+)
   and u.username like upper('&1')||'%'
   and u.username not  in 
    ( 'SYS','SYSTEM','DBSNMP','DIP','OUTLN','TSMSYS','WMSYS','ORACLE_OCM','FLOWS_030000'
     ,'FLOWS_FILES','SI_INFORMTN_SCHEMA','XDB','XS$NULL','APPQOSSYS'
     ,'ORDPLUGINS', 'ORDSYS','ANONYMOUS','APEX_PUBLIC_USER'
     ,'EXFSYS','MDSYS','MGMT_VIEW','SYSMAN')
order  by username,p.privilege 
/

clear  breaks

col    username           clear
col    default_tablespace clear
col    privilege          clear
col    account_status     clear

set    verify  on

4. px_hist

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

set lines 200
select ss.begin_interval_time, sh.*
from dba_hist_sysstat sh, dba_hist_snapshot ss
where ss.dbid = sh.dbid
and ss.instance_number = sh.instance_number
and ss.snap_id = sh.snap_id
and ss.begin_interval_time 
between to_timestamp('2008-08-04 00:00:00','YYYY-MM-DD HH24:MI:SS') 
and to_timestamp('2008-08-05 06:00:00','YYYY-MM-DD HH24:MI:SS')
and stat_name like 'Parallel operations downgraded%'
order by sh.instance_number, sh.stat_name, ss.snap_id;

5. px_hm

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

select * from gv$px_process_sysstat
WHERE statistic like '%Servers Highwater%'
order by inst_id;

6. px_ses

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

select name , value from v$sysstat  where name like '%arallel%'; 
select distinct qcsid, degree , req_degree from v$px_session;

7. px_sess

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

set line 200
col module format a28
col name format a40
select ss.inst_id, ss.sid, ss.serial#, ss.module, ss.sql_id, sn.name, st.value  
  from gv$sesstat st, v$statname sn, gv$session ss
 where sn.name like 'Parallel operations downgraded%'
   and st.statistic# = sn.statistic#
   and st.inst_id = ss.inst_id
   and st.sid = ss.sid
   and st.sid in (select sid from v$session where status = 'ACTIVE' and type <> 'BACKGROUND')
   and st.sid in (select sid from v$session where status = 'ACTIVE' and module not like '%racgimon%')
   and st.sid not in (select sid from v$session_wait where event in ('rdbms ipc message','jobq slave wait'))
   and st.sid not in ( select SYS_CONTEXT ('USERENV','SID' ) from dual )
   and st.value > 0
 order by ss.inst_id;

8. px_sessall

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

set line 200
col module format a35
col name format a45
select ss.inst_id, ss.sid, ss.serial#, ss.module, ss.sql_id, sn.name, st.value  
  from gv$sesstat st, v$statname sn, gv$session ss
 where sn.name like 'Parallel operations downgraded%'
   and st.statistic# = sn.statistic#
   and st.inst_id = ss.inst_id
   and st.sid = ss.sid (+)
   and st.value > 0
 order by ss.inst_id;

9. qc

-- writer : ezis.cloud (noname)
-- Description  : parallel execution session cordinate + slave
-- Requirements : 

set    verify off

col    osuser       for a12 trunc
col    qcsid        for  99999
col    program      for a12 trunc
col    event        for a18 trunc
col    module       for a25 trunc
col    command      for a10 trunc
col    username     for a10 trunc
col    sid          for a12
col    sql_id       for a13
col    status       for a8
col    elap_tm      for 999.99
col    ROW_WAIT_OBJ for a22

break  on QCSID

SELECT x.qcsid
      ,y.sid||','||y.serial# sid
      ,y.osuser
      ,y.username
      ,y.program
      ,w.event
      ,y.status
      , s.value
      ,y.module
      ,sql_id
      ,(sysdate-sql_exec_start)*24*60  as elap_tm
      ,(select command_name from v$sqlcommand where command_type = y.command) as command
      ,(select object_name from dba_objects where object_id = ROW_WAIT_OBJ# and rownum = 1) as ROW_WAIT_OBJ
FROM   v$px_session x, v$session y, v$session_wait w, v$sesstat s
WHERE  x.serial# = y.serial#
and    x.sid = y.sid
--     and x.QCSID  = (SELECT qcsid FROM V$PX_SESSION x WHERE sid = 1) 
AND    y.sid = w.sid(+)
and    y.sid = s.sid
and    s.STATISTIC# = 14
ORDER  BY x.qcsid,nvl(server_set,0),nvl(server#,0) asc
/

clear bre
col   osuser       clear
col   qcsid        clear
col   program      clear
col   event        clear
col   module       clear
col   command      clear
col   username     clear
col   sid          clear
col   sql_id       clear
col   status       clear
col   elap_tm      clear
col   ROW_WAIT_OBJ clear

set   verify on

10. raw

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

SET VERIFY OFF

COL FILE_NAME   FOR A50

SELECT RTRIM(NVL(A.FILE_NAME,B.NAME),'_0123456789') FILE_NAME
      , COUNT(1) CNT
  FROM (
       SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
       UNION ALL
       SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES
       ) A
       RIGHT OUTER JOIN
       (
       SELECT '/dev/datavg/rSsfa_10g_'||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'SFADB_') CONNECT BY LEVEL <= 110
       UNION ALL
       SELECT '/dev/datavg/rSsfa_5g_' ||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'SFADB_') CONNECT BY LEVEL <= 20
       UNION ALL
       SELECT '/dev/datavg/rCcam_10g_'||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'CAMDB_') CONNECT BY LEVEL <= 30
       UNION ALL
       SELECT '/dev/datavg/rCcam_5g_' ||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'CAMDB_') CONNECT BY LEVEL <= 10
       ) B
       ON A.FILE_NAME = B.NAME
 WHERE A.TABLESPACE_NAME IS NULL
 GROUP BY RTRIM(NVL(A.FILE_NAME,B.NAME),'_0123456789')
 ORDER BY 1
/

SELECT * FROM (
SELECT B.NAME
  FROM (
       SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
       UNION ALL
       SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES
       ) A
       RIGHT OUTER JOIN
       (
       SELECT '/dev/datavg/rSsfa_10g_'||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'SFADB_') CONNECT BY LEVEL <= 110
       UNION ALL
       SELECT '/dev/datavg/rSsfa_5g_' ||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'SFADB_') CONNECT BY LEVEL <= 20
       UNION ALL
       SELECT '/dev/datavg/rCcam_10g_'||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'CAMDB_') CONNECT BY LEVEL <= 30
       UNION ALL
       SELECT '/dev/datavg/rCcam_5g_' ||LPAD(LEVEL,3,'0') NAME FROM DUAL WHERE EXISTS ( SELECT 1 FROM V$INSTANCE WHERE INSTANCE_NAME LIKE 'CAMDB_') CONNECT BY LEVEL <= 10
       ) B
       ON A.FILE_NAME = B.NAME
 WHERE A.TABLESPACE_NAME IS NULL
   AND B.NAME LIKE '%'||'&1.'||'%'
  ORDER BY 1)
WHERE ROWNUM <= 12
/

COL FILE_NAME   CLEAR

SET VERIFY ON

11. rb

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

col name    for a25
col hwmsize for 9999999999999

select n.usn,n.name, s.extents, s.rssize/1024 "rssize(kb)", s.optsize/1024 "optsize (kb)",
s.hwmsize, s.xacts, s.status
from v$rollname n, v$rollstat s
where n.usn = s.usn
/
col name clear
col hwmsize clear

12. rb_clean

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

select t.used_urec
from v$transaction t, v$session s
where t.addr = s.taddr
and s.sid = '&sid'
/

13. rb_extent

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

col usn for 9999999999
col name for a25
col tablespace_name for a10
col extents for 999,999
col rssize for 999,999,999,999


select rs.usn, name, drs.tablespace_name, EXTENTS, rssize
  from v$rollstat rs, v$rollname rn, dba_rollback_segs drs
  where rs.usn = rn.usn
    and drs.segment_name = rn.name
/

14. rb_size

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

# prompt = Average size of rollback segments = prompt 
The average number of extents from the prompt : 20 is appropriate

prompt bytes/1024/1024 "RBS SIZE(Mb)",
prompt          (initial_extent + next_extent * (extents - 1)) / 1024 / 1024  "RBS SIZE(Mb)"

set linesize 130

select substr(segment_name,1,15)"RBS",
       bytes/1024/1024"RBS SIZE(Mb)",
       (initial_extent + next_extent * (extents - 1)) / 1024 / 1024  "RBS SIZE(Mb)",
       extents
from   dba_segments
where segment_type = 'ROLLBACK'
/

15. rb_use

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

col name     for a10
col osuser   for a12
col sid      for 9999
col username for a10
col terminal for a20
select   l.sid, r.name, nvl(s.username,'no transaction') username, s.osuser "OS user", s.terminal
from     v$lock l, v$session s, v$rollname r
where    l.sid = s.sid(+) and trunc(l.id1/65536) = r.usn and l.type = 'TX' and l.lmode = 6
order by l.sid
/
col name     clear
col osuser   clear
col sid      clear
col username clear
col terminal clear

16. rb_used

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

COL NAME FORMAT A25
COL TABLESPACE_NAME FORMAT A16
COL EXTENTS FORMAT 99999
COL RSS_KB FORMAT 999,999,999

SELECT RS.USN
     , NAME
     , DRS.TABLESPACE_NAME
     , EXTENTS
     , RS.XACTS
     , RSSIZE/1024 RSS_KB
     , NEXT_EXTENT/1024 NEXT_KB
     , RS.STATUS
FROM V$ROLLSTAT RS
   , V$ROLLNAME RN
   , DBA_ROLLBACK_SEGS DRS
WHERE RS.USN = RN.USN
AND DRS.SEGMENT_NAME = RN.NAME
/

COL NAME CLEAR
COL TABLESPACE_NAME CLEAR
COL EXTENTS CLEAR
--COL RSSIZE CLEAR

17. rb_xact

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

select s.sid, s.serial#,d.xacts, r.name
from v$session s, v$rollstat d,v$transaction t,v$rollname r
where s.taddr=t.addr
and t.xidusn = d.usn
and r.usn=d.usn
order by r.name
/

18. recycle

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

col DROPTIME  for a20
col stmt  for a60
col owner for a16
col ts_name for a20

select distinct 'PURGE TABLE '
     ||owner || '.' || original_name || ';' stmt
      ,owner
      ,TS_NAME
      ,DROPTIME
from   dba_recyclebin
where  1=1
and    upper(type) like 'TABLE%'
and    TS_NAME like upper('&1') || '%'
order by owner,DROPTIME
/

select distinct 'PURGE TABLESPACE '
     ||TS_NAME || ' USER ' || OWNER || ';' stmt
from   dba_recyclebin
where  1=1
and    upper(type) like 'TABLE%'
and    TS_NAME like upper('&1') || '%'
/

col DROPTIME  clear
col stmt  clear
col owner clear
col ts_name clear

19. recycle_ts

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

col stmt  for a60

select distinct
'purge tablespace '
||rpad(ts_name,16,' ')
||' user '
||owner
||';' stmt
  from dba_recyclebin
 where 1=1
   and upper(type) like 'TABLE%'
   and TS_NAME like upper('&1') || '%'
/

col stmt  clear

20. refresh

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

set verify off
exec dbms_job.run(&jobno);
commit;
set verify on

21. reorg

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

# prompt ~~ - Block 사용율 낮은 table 

SELECT a.*
FROM (
      SELECT t.owner,t.table_name,
             TRUNC(t.AVG_ROW_LEN*t.num_rows/1024) use_data,
             s.bytes/1024 use_kbytes,
             s.extents,
             trunc((t.AVG_ROW_LEN*t.num_rows)*100/s.bytes,2) pct
      FROM dba_tables t, dba_segments s
      WHERE t.owner LIKE '%ADMIN'
      and t.owner = s.owner
      and t.table_name = s.segment_name
      and t.table_name not like 'MLOG%'
      and s.segment_type = 'TABLE'
      and t.AVG_ROW_LEN*t.num_rows > 0
      --and s.bytes > 100*1024*1024
      order by trunc((t.AVG_ROW_LEN*t.num_rows)*100/s.bytes,2)
     ) a
WHERE rownum <= 50
--ORDER BY owner, table_name
/

select *
from (
select owner,table_name,chain_cnt
from dba_tables
where owner like '%ADMIN'
and chain_cnt is not null
order by chain_cnt desc
)
where rownum <= 20;

22. rep_17

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

SELECT stat_date, MIN (start_time) start_time, MAX ( end_time ) end_time, 
       TO_CHAR(TRUNC(SYSDATE)+(TO_DATE(MAX (end_time),'hh24mi')-TO_DATE(MIN(start_time),'hh24mi')),'hh24:mi') elapsed,
	   MAX ( TO_CHAR(TO_NUMBER(TOT_TIME), '999')||','||a.service_id ) tot_time  
FROM stsvstat a, stbarelm b
WHERE  a.service_id = b.service_id
AND	   a.stat_date > TO_CHAR(SYSDATE-9,'yyyymmdd')
AND	   b.target_table IN ('F_STENTERM','F_STCLOSEM','F_STENTERM,F_STCLOSEM')
--AND    a.service_id NOT IN ('st2_bst2cho011','st2_bst2cho711')
GROUP BY stat_date
ORDER BY stat_date DESC;

col what for a65 trunc

SELECT a.job, to_char(a.LAST_DATE,'mmdd hh24:mi:ss') LAST_DATE,
       to_char(a.NEXT_DATE,'mmdd hh24:mi:ss') NEXT_DATE,
       TO_CHAR(TRUNC(SYSDATE)+(NVL(a.total_time,0) - NVL(b.total_time,0))/60/60/24,'hh24:mi:ss') elapsed,
       NVL(a.total_time,0) - NVL(b.total_time,0) sec,
       NVL(a.total_time,0) total_time, a.what
FROM 
     (SELECT * FROM jobs WHERE wrk_dt = TO_CHAR(SYSDATE,'yyyymmdd')) a,
     (SELECT * FROM jobs WHERE wrk_dt = TO_CHAR(SYSDATE-1,'yyyymmdd')) b
WHERE a.job = b.job (+)
--AND a.job = 751
AND NVL(a.total_time,0) - NVL(b.total_time,0) > 600
ORDER BY 4 DESC;

col what clear
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]