dba 常用管理脚本收集（测）

dba 常用管理脚本收集:
############################################################################################
Space.sql
set time on
set lines 500
set pages 100
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999 Heading "Size (Mb)"
col free_ts_size form 99999999 Heading "Free (Mb)"
col used_ts_size form 99999999 Heading "Used (Mb)"
col used_pct form 99999 Heading "% Used"
col free_pct form 99999 Heading "% Free"
col warning form a10 Heading "Message"
break on report
compute sum label total of tot_ts_size on report
compute sum label total of used_ts_size on report
compute sum label total of free_ts_size on report
(select  df.tablespace_name tspace
,       round(sum(fs.bytes_free + fs.bytes_used) / 1024 / 1024, 2) tot_ts_size
,       round(sum(fs.Bytes_used) / 1024 / 1024, 2)  used_ts_size
,       round(sum(fs.bytes_free) / 1024 / 1024, 2)  free_ts_size
,       round(sum(fs.Bytes_used ) * 100 / sum((fs.bytes_free + fs.bytes_used))) used_pct
,       round(sum(fs.Bytes_free ) * 100 / sum((fs.bytes_free + fs.bytes_used))) free_pct
,      decode(sign(sum(round(((fs.bytes_free + fs.bytes_used)-fs.bytes_free)*100/(fs.bytes_free + fs.bytes_used))) - 80), 1, '
!ALERT', '') warning
from   SYS.V_$TEMP_SPACE_HEADER fs
,      dba_temp_files df
where fs.tablespace_name(+) = df.tablespace_name
  and fs.file_id(+) = df.file_id
group by df.tablespace_name
union
SELECT df.tablespace_name tspace
,      df.bytes/(1024*1024) tot_ts_size
,      round((df.bytes-sum(fs.bytes))/(1024*1024)) used_ts_size
,      sum(fs.bytes)/(1024*1024) free_ts_size
,      round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct
,      round(sum(fs.bytes)*100/df.bytes) free_pct
,      decode(sign(round((df.bytes-sum(fs.bytes))*100/df.bytes) - 80), 1, '!ALERT', '') warning
FROM dba_free_space fs
, (select tablespace_name, sum(bytes) bytes
   from dba_data_files
   group by tablespace_name
   ) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes)
union
(select tablespace_name tspace,
1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning from dba_data_files
group by tablespace_name
minus
select tablespace_name tspace,1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning
from dba_free_space
group by tablespace_name)
order by 4
;
############################################################################################
ops.sql
set line 150;
col target for a35
col EST_COMPLETION_TIME for a20
col SOFAR for a14
col sid_serial for a10
set pages 1000
SELECT /*+ rule */ a.sid||','||a.serial#  "sid_serial", b.status, a.target || a.opname target , a.TOTALWORK, a.SOFAR SOFAR, a.TIME_REMAINING "TIME_REMAIN",
to_char(start_time+(sysdate-start_time) /(a.sofar/a.totalwork),'dd-mon-yy:hh:mi:ss') Est_completion_time,
round((a.sofar/a.totalwork)*100,3) pct_complete,
ELAPSED_SECONDS "ELAPS_SECS" FROM V$SESSION_LONGOPS a, V$SESSION b
where a.time_remaining > 0
and a.sid=b.sid and b.status='ACTIVE' 
order by a.time_remaining desc
/
############################################################################################
top_sqls


SET LINESIZE 500
SET PAGESIZE 100
col sid_serial for a10
col sql_text for a30
col osuser for a10
SELECT *
FROM   (SELECT /*+ rule */ sid||','||serial# sid_serial,Substr(a.sql_text,1,550) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) read_pr_exe,
               a.buffer_gets buff_g,
               a.disk_reads disk_r,
               a.executions exec,
               a.sorts,
               a.address,osuser
        FROM   v$sqlarea a,v$session b
   where  a.address = b.sql_address and b.status = 'ACTIVE'
        ORDER BY 3 DESC)
WHERE  rownum <= &1
/
############################################################################################
top_sessions.sql


COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20


SELECT NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.sid,
       a.serial#,
       c.value AS &1,
       a.lockwait,
       a.status,
       a.module,
       a.machine,
       a.program,
       TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session a,
       v$sesstat c,
       v$statname d
WHERE  a.sid        = c.sid
AND    c.statistic# = d.statistic#
AND    d.name       = DECODE(UPPER('&1'), 'READS', 'session logical reads',
                                          'EXECS', 'execute count',
                                          'CPU',   'CPU used by this session',
                                                   'CPU used by this session')
ORDER BY c.value DESC;
############################################################################################
undo_used.sql


set pages 10000 lines 200
col username heading "User"
col name format a22 wrapped heading "Undo Segment Name"
col xidusn heading "Undo|Seg #"
col xidslot heading "Undo|Slot #"
col xidsqn heading "Undo|Seq #"
col ubafil heading "File #"
col ubablk heading "Block #"
col start_time format a20 word_wrapped heading "Started"
col status format a8 heading "Status"
col blk format 999,999,999 heading "KBytes"
col used_urec heading "Rows"


select /*+ rule */ start_time, username, r.name,  
ubafil, ubablk, t.status, (used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r, v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
############################################################################################
cpu_cost.sql




SELECT /*+ Rule */ 'SID : '||sid||chr(10)||
'Serial# '||serial#||chr(10)||
'Username : '||username||chr(10)||
'Logon Time : '||logon_time||chr(10)||
'Last Called (in Secs) :'||last_call_et||chr(10)||
'Status : '||status||chr(10)||
'SQL Address : '||address||chr(10)||
'HASH Value : '||hash_value||chr(10)||
'Buffer Gets : '||buffer_gets||chr(10)||
'Executions : '||executions||chr(10)||
'Buffer Gets / Execution :'||buffer_gets/executions||chr(10)||
'Text of SQL : '||sql_text
from ( select sid, serial#, username, logon_time, last_call_et, address, hash_value, buffer_gets, executions,
buffer_gets/executions,sql_text, status
    FROM v$sqlarea, v$session
   WHERE buffer_gets > 50000 and executions>0
and sql_address = address
and sql_hash_value = hash_value
order by 8
)
/
############################################################################################
library_pin.sql




select /*+ ordered */ w1.sid  waiting_session,
h1.sid  holding_session,h1.username,
w.kgllktype lock_or_pin,
        w.kgllkhdl address,
decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  'Unknown') mode_held, 
decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  'Unknown') mode_requested
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
  (((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
   and
     (((w.kgllkmod = 0) or (w.kgllkmod= 1))
     and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
  and  w.kgllktype =  h.kgllktype
  and  w.kgllkhdl =  h.kgllkhdl
  and  w.kgllkuse     =   w1.saddr
  and  h.kgllkuse     =   h1.saddr
/


  SELECT  /*+ rule */ 'alter system kill session ' ||chr(39)||sid||','||SERIAL#||chr(39)||' immediate ;'
          FROM x$kglpn p, v$session s
         WHERE p.kglpnuse=s.saddr
           AND kglpnhdl in (select P1RAW  from gv$session_wait where state='WAITING'
and event like 'library cache lock' or event like 'library cache pin' )
/
############################################################################################
--temp tbs used
SELECT d.tablespace_name "Name", 
                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
            FROM sys.dba_tablespaces d, 
                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
           WHERE d.tablespace_name = a.tablespace_name(+) 
             AND d.tablespace_name = t.tablespace_name(+) 
             AND d.extent_management like 'LOCAL' 
             AND d.contents like 'TEMPORARY'
select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;


GV$TEMP_SPACE_HEADER
select /*+ ordered use_nl(hc) */ hc.inst_id, ts.name, hc.ktfthctfno, (hc.ktfthcsz - hc.ktfthcfree)*ts.blocksize, (hc.ktfthcsz - hc.ktfthcfre
e), hc.ktfthcfree*ts.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.onl
ine$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0

############################################################################################

 SELECT e.event_name event,
         e.total_waits - NVL (b.total_waits, 0) waits,
         DECODE (
            e.total_waits - NVL (b.total_waits, 0),
            0, TO_NUMBER (NULL),
            DECODE (
               e.total_timeouts - NVL (b.total_timeouts, 0),
               0, TO_NUMBER (NULL),
                 100
               * (e.total_timeouts - NVL (b.total_timeouts, 0))
               / (e.total_waits - NVL (b.total_waits, 0))))
            pctto,
         (e.time_waited_micro - NVL (b.time_waited_micro, 0)) / 1000000 time,
         DECODE (
            (e.total_waits - NVL (b.total_waits, 0)),
            0, TO_NUMBER (NULL),
            ( (e.time_waited_micro - NVL (b.time_waited_micro, 0)) / 1000)
            / (e.total_waits - NVL (b.total_waits, 0)))
            avgwt,
         DECODE (e.wait_class, 'Idle', 99, 0) idle
    FROM dba_hist_system_event b, dba_hist_system_event e
   WHERE     b.snap_id(+) = &bid
         AND e.snap_id = &eid
         --AND b.dbid(+) = :dbid
         --AND e.dbid = :dbid
         AND b.instance_number(+) = 1
         AND e.instance_number = 1
         AND b.event_id(+) = e.event_id
         AND e.total_waits > NVL (b.total_waits, 0)
         AND e.event_name NOT IN
                ('smon timer',
                 'pmon timer',
                 'dispatcher timer',
                 'dispatcher listen timer',
                 'rdbms ipc message')
ORDER BY idle,
         time DESC,
         waits DESC,
         event

############################################################################################

############################################################################################