script：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
;
---------------------------------------------------------------------------------------------------------------------------------------------

Lock.sql

set lines 150
set pages 2000
col OBJECT_NAME format  a30
col OBJECT_TYPE format a10
col LOCKED_MODE format 99
col ORACLE_USERNAME format  a20
col OS_USER_NAME format  a20
col PROCESS format  a20
SELECT /*+ rule */ DECODE(request, 0,'HOLDER','WAITER'), sid , lmode,  TYPE, ctime FROM v$LOCK
WHERE (id1, id2, TYPE ) IN (SELECT id1, id2, TYPE FROM v$LOCK WHERE request>0)
ORDER BY id1, request ;

--------------------------------------------------------------------------------------------------------------------------------------------------

lo.sql

col USERNAME for a15
col SQL_TEXT for a40
set line 130
set pages 400
select sesion.sid,sesion.serial#,
        sesion.username,-- optimizer_mode,
        hash_value, address, osuser,--cpu_time, elapsed_time,osuser,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address    = sqlarea.address
and sesion.username is not null
and sesion.sid =&sid 
/
--------------------------------------------------------------------------------------------------------------------------------------------------

Sid.sql

select spid,sid,a.serial#,b.program,osuser,machine,process from v$session a,v$process b
where a.paddr=b.addr and b.spid in (&spid)
/

---------------------------------------------------------------------------------------------------------------------------------------------

Spid.sql

select spid,sid,a.serial#,b.program,osuser,machine,process,status from v$session a,v$process b
where a.paddr=b.addr and a.sid in (&sid) 
/

----------------------------------------------------------------------------------------------------------------------------------------

SW.sql

col event for a34
set pages 100
set lines 130
select sid,event,p1,p2,p3 from v$session_wait where state='WAITING' and event not like '%messag%' order by event
/
select event,count(*) from v$session_wait group by event
/

----------------------------------------------------------------------------------------------------------------------------------------------

lops.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
/
-----------------------------------------------------------------------------------------------------------------------------------------------
rollback_info.sql

SELECT * FROM V$FAST_START_TRANSACTIONS
/

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
  FROM v$session a, v$transaction b
  WHERE a.saddr = b.ses_addr;
SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk 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
/

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

time_remaining.sql

SELECT   SID,
         DECODE (totalwork,
                 0, 0,
                 ROUND (100 * sofar / totalwork, 2)
                ) "Percent",
         MESSAGE "Message", start_time, elapsed_seconds, time_remaining/60 "Time Remaining-Mins"
    FROM v$session_longops WHERE time_remaining > 0 and sid=&sid
/

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

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        =