Oracle指令碼收集【不定時更新】

PiscesCanon發表於2017-07-19
--查詢回滾段資訊
  1. select rownum,
  2.         sys.dba_rollback_segs.segment_name Name,
  3.         v$rollstat.extents Extents,
  4.         v$rollstat.rssize Size_in_Bytes,
  5.         v$rollstat.xacts XActs,
  6.         v$rollstat.gets Gets,
  7.         v$rollstat.waits Waits,
  8.         v$rollstat.writes Writes,
  9.         sys.dba_rollback_segs.status status
  10.    from v$rollstat, sys.dba_rollback_segs, v$rollname
  11.   where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
  12.     and v$rollstat.usn(+) = v$rollname.usn
  13.   order by rownum;

--10g之前,通過如下語句得到目標trace檔案
  1. col TRACE_FILE_NAME for a100
  2. set linesize 200
  3. select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
  4.              from (select p.spid
  5.                   from v$mystat m, v$session s, v$process p
  6.                  where m.statistic# = 1
  7.                    and s.sid = m.sid
  8.                    and p.addr = s.paddr) p,
  9.                (select t.instance
  10.                   from v$thread t, v$parameter v
  11.                 where v.name = 'thread'
  12.                  and (v.value = 0 or t.thread# = to_number(v.value))) i,
  13.              (select value from v$parameter where name = 'user_dump_dest') d;

--mount狀態下查詢隱藏引數
  1. col name for a50
  2. col value for a20
  3. col describ for a100
  4. set linesize 200
  5.     SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  6. FROM SYS.x$ksppi x, SYS.x$ksppcv y
  7. WHERE x.inst_id = USERENV ('Instance')
  8. AND y.inst_id = USERENV ('Instance')
  9. AND x.indx = y.indx
  10. AND upper(x.ksppinm) like '%_AUTOTUNE%';


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30174570/viewspace-2142313/,如需轉載,請註明出處,否則將追究法律責任。

相關文章