oracle UG,ORACLE DBA实践之六十：oracle快照监控脚本3（转自CNOUG）

监控以下大类：

监控脚本1

1、数据库实例信息

2、调度和作业信息

3、存储

监控脚本24、undo段

5、备份

6、闪回技术

7、性能

8、自动工作量仓库(AWR)

监控脚本3

9、会话

10、安全

11、对象

监控脚本4

12、OLAP

13、数据泵

14、网络

15、复制

每一大类下又分了很多小项，如存储大类下就分了表空间，数据文件，数据库增长率等，执行该基本后会自动产生一个html文件，放在执行sqlplus时所在的目录下，在xp系统上默认是x:\document

and settings\用户名。用了觉得好的就顶一下！在pl/sql

dev下运行，生产的html报告格式可能会有问题，请直接在sql*plus下执行

--

+============================================================================+

--

| |

--

| <<<<>>>> |

--

| |

--

+============================================================================+

prompt

prompt

size="+2" face="Arial,Helvetica,Geneva,sans-serif"

color="#663300">Sessions

--

+----------------------------------------------------------------------------+

--

| - CURRENT SESSIONS

- |

--

+----------------------------------------------------------------------------+

prompt

name="current_sessions">

prompt

face="Arial,Helvetica,Geneva,sans-serif"

color="#336699">Current

Sessions

---

align="left" width="460">

CLEAR COLUMNS BREAKS COMPUTES

COLUMN instance_name_print FORMAT

a45 HEADING

'Instance

Name' ENTMAP off

COLUMN thread_number_print FORMAT

a45 HEADING

'Thread

Number' ENTMAP off

COLUMN

count FORMAT a45 HEADING 'Current No. of

Processes' ENTMAP off

COLUMN

value FORMAT a45 HEADING 'Max No. of

Processes' ENTMAP off

COLUMN

pct_usage FORMAT a45 HEADING '%

Usage' ENTMAP off

SELECT

'

align="center">

color="#336699">'

|| a.instance_name ||

'

' instance_name_print

, '

align="center">' ||

a.thread# || '

' thread_number_print

, '

align="center">' ||

TO_CHAR(a.count) || '

' count

, '

align="center">' ||

b.value || '

' value

, '

align="center">' || TO_CHAR(ROUND(100*(a.count /

b.value), 2)) ||

'%

' pct_usage

FROM

(select count(*) count,

a1.inst_id, a2.instance_name, a2.thread#

from gv$session a1

, gv$instance a2

where a1.inst_id = a2.inst_id

group by a1.inst_id

, a2.instance_name

, a2.thread#) a

, (select value, inst_id from gv$parameter where

name='processes') b

WHERE

a.inst_id =

b.inst_id

ORDER BY

a.instance_name;

prompt

[

class="noLink"

href="#top">Top

]

--

+----------------------------------------------------------------------------+

--

| - USER SESSION MATRIX

- |

--

+----------------------------------------------------------------------------+

prompt

name="user_session_matrix">

prompt

face="Arial,Helvetica,Geneva,sans-serif"

color="#336699">User

Session

Matrix

---

align="left" width="460">

prompt User sessions (excluding

SYS and background processes)

CLEAR COLUMNS BREAKS COMPUTES

COLUMN instance_name_print FORMAT

a75 HEADING 'Instance

Name' ENTMAP off

COLUMN thread_number_print FORMAT

a75 HEADING 'Thread

Number' ENTMAP off

COLUMN

username FORMAT

a79 HEADING 'Oracle

User' ENTMAP off

COLUMN

num_user_sess FORMAT 999,999,999,999 HEADING

'Total Number of Logins' ENTMAP

off

COLUMN

count_a FORMAT

999,999,999 HEADING 'Active

Logins' ENTMAP off

COLUMN

count_i FORMAT

999,999,999 HEADING 'Inactive

Logins' ENTMAP off

COLUMN

count_k FORMAT

999,999,999 HEADING 'Killed

Logins' ENTMAP off

BREAK ON report ON instance_name_print ON

thread_number_print

SELECT

'

align="center">

color="#336699">'

|| i.instance_name ||

'

' instance_name_print

, '

align="center">

color="#336699">'

||

i.thread# ||

'

' thread_number_print

, '

align="left">

color="#000000">' || NVL(sess.username, '[B.G.

Process]') ||

'

'

username

,

count(*) num_user_sess

, NVL(act.count,

0) count_a

, NVL(inact.count,

0) count_i

, NVL(killed.count, 0) count_k

FROM

gv$session sess

,

gv$instance i

,

(SELECT count(*) count, NVL(username, '[B.G. Process]') username,

inst_id

FROM gv$session

WHERE status = 'ACTIVE'

GROUP BY username,

inst_id) act

,

(SELECT count(*) count, NVL(username, '[B.G. Process]') username,

inst_id

FROM gv$session

WHERE status = 'INACTIVE'

GROUP BY username,

inst_id) inact

,

(SELECT count(*) count, NVL(username, '[B.G. Process]') username,

inst_id

FROM gv$session

WHERE status = 'KILLED'

GROUP BY username,

inst_id) killed

WHERE