Oracle 数据库信息搜集脚本 持续更新

OracleRunStatusGet.bat

@echo off
@echo -----------------------------------
@echo 搜集数据库运行信息
@echo -----------------------------------
set sid=
set /p sid=请输入数据库名:

set ORACLE_SID=%sid%

sqlplus / as sysdba @OracleRunStatusGet.sql

pause

 

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

OracleRunStatusGet.sql

 

set serverout on;
spool OracleRunStatusGetLog.txt
declare
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'));
dbms_output.put_line('信息搜集开始');
end;
/
set pagesize 500;
set linesize 500;

select  '------ 数据库版本信息 ------' from dual;
select * from v$version;

select '------ 数据库运行状态 ------' from dual;
select name,open_mode from v$database;

select '------ shared pool 共享池 ------' from dual;
select pool,name as "库缓存",bytes from v$sgastat a where a.NAME = 'library cache';
select pool,name as "空闲的内存区",bytes from v$sgastat a where a.pool = 'shared pool' and a.NAME = 'free memory';
select pool,name as "数据字典",bytes from v$sgastat a where a.NAME = 'row cache';
select case
           when name = 'parse count (total)' then
            'sql总解析次数'
           when name = 'parse count (hard)' then
            'sql硬解析次数'
           when name = 'parse count (failures)' then
            'sql解析失败次数'
           else
            name
         end name,value from v$sysstat where name like 'parse%';
select count(*) as "Lib Cache Chain Chunk总数" from x$ksmsp;

select '------ 查看所有数据文件 ------' from dual;
select file_name from dba_data_files
union
select file_name from dba_temp_files
union
select name from v$controlfile
union
select value from v$parameter where name='spfile'
union
select member from v$logfile;

select '------ 查看正在运行的job ------' from dual;
select djr.sid sess,
       djr.job jid,
       dj.log_user subu,
       dj.priv_user secd,
       dj.what proc,
       to_char(djr.last_date, 'MM/DD') lsd,
       substr(djr.last_sec, 1, 5) lst,
       to_char(djr.this_date, 'MM/DD') nrd,
       substr(djr.this_sec, 1, 5) nrt,
       djr.failures fail
  from sys.dba_jobs dj, sys.dba_jobs_running djr
 where djr.job = dj.job;

select '------ 查看数据库中全部session ------' from dual;
select sid,
       serial#,
       sql_hash_value,
       PREV_HASH_VALUE,
       username,
       program,
       machine,
       process
  from v$session;

select '------ 查看表空间使用百分比 ------' from dual;
SELECT d.tablespace_name Name,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M,
     TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999')||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') Used_M,
     TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')||'%' Used
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
 AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name Name,
     TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M,
     TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') Used_M,
     TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00')||'%' Used
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) 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'
 order by 4 desc;

select '------ 查找消耗CPU资源高的语句 ------' from dual;
select b.sql_id, b.hash_value, substr(b.sql_text, 0, 100) as "sqltext"
  from (select HASH_VALUE，BUFFER_GETS / EXECUTIONS LIO
          from v$sqlarea
         Where EXECUTIONS <> 0
         order by 2 desc) a,
       v$sql b
 where a.hash_value = b.HASH_VALUE
   and rownum < 11;

select '------ 查找消耗磁盘IO高的语句 ------' from dual;
  Select  b.sql_id, b.hash_value, substr(b.sql_text, 0, 100) as "sqltext"
    From (Select HASH_VALUE，DISK_READS / EXECUTIONS WIO
            from v$sqlarea
           Where EXECUTIONS <> 0
           order by 2 desc) a,
       v$sql b
 where a.hash_value = b.HASH_VALUE
   and rownum < 11;

spool off