ITPub博客

首页 > 数据库 > Oracle > 8个DBA最常用的监控Oracle数据库的常用shell脚本 + 常用oracle监控查询

8个DBA最常用的监控Oracle数据库的常用shell脚本 + 常用oracle监控查询

Oracle 作者:宋祖强 时间:2015-11-22 23:01:38 0 删除 编辑
http://blog.csdn.net/tianlesoftware/article/details/4792798

1. 控制文件的查询
set echo off
set feedback off
set linesize 512
column name format a60

SELECT NAME, DECODE (STATUS, '', 'VALID', 'INVALID') "DEC"
FROM V$CONTROLFILE
ORDER BY NAME;

2. prompt Top Sessions by CPU consumption
set echo off
set feedback off
set linesize 512

column sid format 999     heading "SID"
column username format a20     heading "User Name"
column command format a20     heading "Command"
column osuser format a20     heading "OS User"
column process format a20     heading "OS Process"
column machine format a20     heading "Machine"
column value format 99,999  heading "CPU Time"

select 
s.sid sid,
s.username username,
UPPER(DECODE(command,
        1,'Create Table',2,'Insert',3,'Select',
        4,'Create Cluster',5,'Alter Cluster',6,'Update',
        7,'Delete', 8,'Drop Cluster', 9,'Create Index',
        10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
        13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
        16,'Drop Sequence', 17,'Grant', 18,'Revoke',
        19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
        22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
        25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
        28,'Rename', 29,'Comment', 30,'Audit',
        31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link',
        34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
        37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace',
        40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
        43,'Alter User', 44,'Commit', 45,'Rollback',
        46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
        49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
        52,'Create Role', 53,'Drop User', 54,'Drop Role',
        55,'Set Role', 56,'Create Schema', 57,'Create Control File',
        58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
        61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
        64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
        67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
        70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
        73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
        76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
        86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
        92,'Alter Function', 93,'Drop Function', 94,'Create Package',
        95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
        98,'Alter Package Body', 99,'Drop Package Body')) command,
s.osuser osuser,
s.machine machine,
s.process process,
t.value value
from
v$session s,
v$sesstat t,
v$statname n
where
        s.sid = t.sid
and
t.statistic# = n.statistic#
and
n.name = 'CPU used by this session'
and
t.value > 0
and
audsid > 0
order by
t.value desc;

3. prompt Database Basic Info
set echo off
set feedback off
set linesize 512

SELECT NAME, CREATED, LOG_MODE
  FROM V$DATABASE;

4.  prompt All Directories in Database

column directory_path format a80

SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH
FROM DBA_DIRECTORIES
ORDER BY OWNER, DIRECTORY_NAME;

5.prompt Database INIT.ORA Info
set echo off
set feedback off
set linesize 512

column value format a80
SELECT NAME, 
DECODE (TYPE, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') TYPE, 
VALUE, DESCRIPTION, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISDEFAULT,
ISMODIFIED, ISADJUSTED
FROM V$PARAMETER
ORDER BY NAME;

6. prompt Top Tablespaces by IO consumption
set echo off
set feedback off
set linesize 512

column file_name format a60     heading "Data-File Name"
column ts_name format a32     heading "Tablespace Name"
column stat_reads format 999,999,999,999  heading "Physical Reads"
column stat_writes format 999,999,999,999  heading "Physical Writes"
column stat_breads format 999,999,999,999  heading "Physical Blk-Reads"
column stat_bwrites format 999,999,999,999  heading "Physical Blk-Writes"
break on ts_name

select 
t.name   ts_name,
f.name file_name,
s.phyrds stat_reads,
s.phyblkrd stat_breads,
s.phywrts stat_writes,
s.phyblkwrt stat_bwrites
from  v$tablespace t,  v$datafile f,   v$filestat s
where  t.ts# = f.ts#  and  f.file# = s.file#
order by s.phyrds desc, s.phywrts desc;

7. prompt Top IO Waits in Database

column event  format a30
column segment_type format a10
column segment_name format a20

select event,segment_type,segment_name,file_id,block_id,blocks
from dba_extents, v$session_wait
where p1text='file#'
  and p2text='block#'
  and p1=file_id and
      p2 between block_id and block_id+blocks
order by segment_type,segment_name;

8. prompt Database Locks Being Held

column sid    format 999     heading "SID"
column username    format a10     heading "User Name"
column machine format a20     heading "Machine Name"
column object_name format a20     heading "Object Name"
column type format a4     heading "Type"
column lmode    format a20   heading "Current Lock Mode"
column request    format 9999999 heading "Request Mode"
column block    format 9999999 heading "Lock Blocking"

select 
s.sid sid,
s.username username,
s.machine machine,
l.type type,
o.object_name object_name,
DECODE(l.lmode,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Exlusive',
4,'Share',
5,'Sh/Row Exlusive',
6,'Exclusive') lmode,
DECODE(l.request,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Exlusive',
4,'Share',
5,'Sh/Row Exlusive',
6,'Exclusive') request,
l.block block
from  v$lock l,  v$session s,  dba_objects o
where  l.sid = s.sid  and  username != 'SYSTEM'  and  o.object_id(+) = l.id1;


9. prompt All Profiles in Database

SELECT DISTINCT PROFILE  FROM DBA_PROFILES;

10. prompt Database Rollback Segments

select
r.segment_name segment_name,
r.owner owner,
r.tablespace_name tablespace_name,
r.status status,
round(r.initial_extent/1024/1024) initial_extent,
round(r.next_extent/1024/1024) next_extent,
s.extents,0 extents,
ROUND(s.rssize/1024/1024) rssize,
s.xacts active_trans
from  dba_rollback_segs r,  v$rollname  n,  v$rollstat  s
where  r.segment_name = n.name   and  n.usn = s.usn;


11. prompt Rollback Segment Activity in Database

SELECT A.NAME, B.XACTS,
C.SID, C.SERIAL#,
C.USERNAME, D.SQL_TEXT
FROM V$ROLLNAME A, V$ROLLSTAT B, V$SESSION C, V$SQLTEXT D, V$TRANSACTION E
   WHERE A.USN = B.USN
AND B.USN = E.XIDUSN
AND C.TADDR = E.ADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
ORDER BY A.NAME, C.SID, D.PIECE;

12.prompt  Alter All Rollback Segments Offline(有点问题)

set echo off
set heading off
set feedback off
set term off

spool rbs_off.tmp

select 'set echo on' from dual;
select 'set feedback on' from dual;

select 'alter rollback segment '||segment_name||' offline;'
  from dba_rollback_segs
  where segment_name != 'SYSTEM'
    and status = 'ONLINE';

spool off
set term on

@rbs_off.tmp

13.prompt Alter All Rollback Segments Online(有点问题)

set echo off
set heading off
set feedback off
set term off

spool rbs_on.tmp

select 'set echo on' from dual;
select 'set feedback on' from dual;

select 'alter rollback segment '||segment_name||' online;'
  from dba_rollback_segs
  where segment_name != 'SYSTEM'
    and status = 'OFFLINE';

spool off
set term on

@rbs_on.tmp

14. prompt All Redo Logs in Database
 column member format a60

SELECT A.GROUP#, B.MEMBER, A.THREAD#, A.SEQUENCE#, A.BYTES, A.MEMBERS,
A.ARCHIVED, A.STATUS, A.FIRST_CHANGE#, A.FIRST_TIME
FROM V$LOG A, V$LOGFILE B
   WHERE A.GROUP# = B.GROUP#
ORDER BY A.GROUP#;

15.prompt All Roles in Database

SELECT ROLE, PASSWORD_REQUIRED  FROM DBA_ROLES;

16. 
prompt All Granted Roles in Database

break on GRANTED_ROLE skip 1

SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;

17. prompt All Roles Granted to Roles in Database

break on GRANTED_ROLE skip 1

SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;

18.prompt All Granted User Roles in Database

break on GRANTED_ROLE skip 1

SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE
FROM SYS.DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
 AND GRANTED_ROLE NOT IN (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'CONNECT',
'CTXAPP',
'DBA',
'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SELECT_CATALOG_ROLE',
'SNMPAGENT',
'WKADMIN',
'WKUSER'
)
ORDER BY GRANTED_ROLE, ADMIN_OPTION;

19.prompt Session Events

column event format a32

SELECT B.USERNAME, B.SID, B.SERIAL#, A.EVENT, A.TOTAL_WAITS,
A.TOTAL_TIMEOUTS, A.TIME_WAITED, A.AVERAGE_WAIT
FROM V$SESSION_EVENT A, V$SESSION B
WHERE A.SID = B.SID  ORDER BY 1;

20. prompt SGA Memory Map (overall)

column dummy      noprint
column area       format a20 heading 'Main SGA Areas'
column name       format a20
column pool       format a20
column bytes      format 999,999,999,999
column sum(bytes) format 999,999,999,999

break on report
compute sum of sum(bytes) on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)  FROM v$sgastat
WHERE pool is null and  name = 'db_block_buffers'   group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes)  FROM v$sgastat  WHERE pool = 'shared pool'  group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes)   FROM v$sgastat  WHERE pool = 'large pool'  group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes)   FROM v$sgastat  WHERE pool = 'java pool'  group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes)  FROM v$sgastat  WHERE pool is null and name = 'log_buffer'  group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)  FROM v$sgastat  WHERE pool is null and  name = 'fixed_sga'  group by name   ORDER BY 4 desc;

21. prompt SGA Memory Map (shared pool)

column area       format a20 heading 'Shared Pool Areas'

SELECT 'Shared Pool' area, name, sum(bytes)   FROM v$sgastat
WHERE pool = 'shared pool' and  name in ('library cache','dictionary cache','free memory','sql area')  group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes)   FROM v$sgastat
WHERE pool = 'shared pool' and   name not in ('library cache','dictionary cache','free memory','sql area')  group by pool  order by 3 desc;

22. prompt System Events

column event format a32
column total_waits     format 999,999,999,999 heading "Total Waits"
column total_timeouts  format 999,999,999,999 heading "Total Timeouts"
column time_waited     format 999,999,999,999 heading "Time Waited"
column average_wait    format 999,999,999,999 heading "Average Wait"

SELECT EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;

23.prompt Tablespace Basic Information
set echo off
set feedback off
set verify off
set linesize 512

set term off
COLUMN block_size NOPRINT new_value block_size
SELECT value block_size from v$parameter
 where name='db_block_size';

col Tablespace_name Heading 'Tablespace'
col Megs_Alloc      Heading 'Megs Alloc'
col Megs_Free       Heading 'Megs Free'
col Megs_Used       Heading 'Megs Used'
col Pct_Free        Heading 'Pct Free'
col Pct_Used        Heading 'Pct Used'
col Init_Ext        Heading 'Init Ext'
col Next_Ext        Heading 'Next Ext'
col Min_Ext         Heading 'Min Ext'
col Max_Ext         Heading 'Max Ext'
col Num_Segs        Heading 'Num Segs'
col Num_Exts        Heading 'Num Exts'

select c.tablespace_name,
       round(a.bytes/1048576) Megs_Alloc,
       round(b.bytes/1048576) Megs_Free,
       round((a.bytes-b.bytes)/1048576) Megs_Used,
       round(b.bytes/a.bytes * 100) Pct_Free,
       round((a.bytes-b.bytes)/a.bytes * 100) Pct_Used,
       round(c.initial_extent/1048576) Init_Ext,
       round(c.next_extent/1048576) Next_Ext,
       round(a.minbytes/1048576) Min_Ext,
       round(a.maxbytes/1048576) Max_Ext,
       nvl(d.num_segs,0) Num_segs,
       nvl(d.num_exts,0) Num_Exts
from (select tablespace_name,
             sum(a.bytes) bytes,
             min(a.bytes) minbytes,
             max(a.bytes) maxbytes
      from sys.dba_data_files a
      group by tablespace_name) a,
     (select a.tablespace_name,
             nvl(sum(b.bytes),0) bytes
      from sys.dba_data_files a,
           sys.dba_free_space b
      where a.tablespace_name = b.tablespace_name (+)
        and a.file_id         = b.file_id (+)
      group by a.tablespace_name) b,
      sys.dba_tablespaces c,
      (select tablespace_name, 
              count(distinct segment_name) num_segs,
              count(extent_id) num_exts
       from sys.dba_extents
       group by tablespace_name) d
where a.tablespace_name = b.tablespace_name(+)
  and a.tablespace_name = c.tablespace_name
  and a.tablespace_name = d.tablespace_name(+)
order by c.tablespace_name;

24. prompt Alter All Tablespaces Coalesce
set heading off
set feedback off

set term off
spool tspc_coal.tmp

select 'set echo on' from dual;
select 'set feedback on' from dual;

select 'alter tablespace '||tablespace_name||' coalesce;'
  from dba_tablespaces
  where contents not in ('TEMPORARY','UNDO');

spool off
set term on

@tspc_coal.tmp

25. prompt Tablspace Free Space Breakdown

col tablespace_name Heading 'Tablespace Name'
col over_100m       Heading '# > 100M'
col over_25m        Heading '# > 25M'
col over_10m        Heading '# > 10M'
col over_5m         Heading '# > 5M'
col over_1m         Heading '# > 1M'
col over_512k       Heading '# > 512K'
col under_512k      Heading '# < 521k' ;

select tablespace_name,
       sum(decode(sign(bytes-1024*1024*100),-1,0,1)) over_100m,
       (sum(decode(sign(bytes-1024*1024*025),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*100),-1,0,1))) over_25m,
       (sum(decode(sign(bytes-1024*1024*010),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*25),-1,0,1))) over_10m,
       (sum(decode(sign(bytes-1024*1024*005),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*10),-1,0,1))) over_5m,
       (sum(decode(sign(bytes-1024*1024*001),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*5),-1,0,1))) over_1m,
       (sum(decode(sign(bytes-1024*512*001),-1,0,1)) -
            sum(decode(sign(bytes-1024*1024*1),-1,0,1))) over_512k,
      sum(decode(sign(bytes-1024*512*001),-1,1,0)) under_512k
from dba_free_space
group by tablespace_name;

26.prompt Temporary Tablespace User Counts
   prompt Default Tablespace User Counts
set echo off
set feedback off
set linesize 512

column count(username) heading '# USERS'

select default_tablespace, count(username)
from dba_users
where username not in ('PUBLIC','_NEXT_USER')
group by default_tablespace;

select temporary_tablespace, count(username)
from dba_users
where username not in ('PUBLIC','_NEXT_USER')
group by temporary_tablespace;

27.prompt Alter All Tablespaces Offline
set echo off
set heading off
set feedback off

set term off
spool tspc_off.tmp

select 'set echo on' from dual;
select 'set feedback on' from dual;

select 'alter tablespace '||tablespace_name||' offline;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','OUTLN')
  and status = 'ONLINE';

spool off
set term on

@tspc_off.tmp

28.prompt Alter All Tablespaces Online

set echo off
set heading off
set feedback off

set term off
spool tspc_on.tmp

select 'set echo on' from dual;
select 'set feedback on' from dual;

select 'alter tablespace '||tablespace_name||' online;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','OUTLN')
  and status = 'OFFLINE';

spool off
set term on

@tspc_on.tmp

29. prompt All Users in Database
set echo off
set feedback off
set linesize 512

column count(b.object_id) heading '# OBJECTS'

select a.username,
       a.account_status,
       a.default_tablespace,
       a.temporary_tablespace,
  count(b.object_id)
from dba_users a, dba_objects b
where a.username = b.owner(+)
group by a.username,
         a.account_status, 
         a.default_tablespace,
         a.temporary_tablespace;

30. prompt Database Instance and Version Info

set echo off
set feedback off
set heading off

select 'Database Instance   = '||name from v$database;
select 'Database Version    = '||banner from v$version where rownum = 1;
select 'Database Block Size = '||value from v$parameter where name = 'db_block_size';

31. prompt Tablespace Object Breakdown Information

column count(*) heading '# OBJECTS'
column sum(bytes) heading 'BYTES'

select tablespace_name, owner, segment_type, count(*), sum(bytes)  from sys.dba_extents
group by tablespace_name, owner, segment_type
order by tablespace_name, owner, segment_type;

32.prompt Current Date and Time

select '*** Time = '||to_char(sysdate,'DD-MON-YY HH:MI:SS')|| ' ***' from dual;

33.prompt Database Sessions Active

column machine format a20
column osuser format a20
column module format a20

select machine, process, osuser, username,
       schemaname, status, lockwait, sid,
       serial#, module, action
from v$session  
where username is not null and osuser is not null  order by machine, osuser, username,  schemaname, status, module;

34.prompt User Connected As

select '*** User = '||user||' ***' from dual;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1843982/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Data Guard 环境
请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277166