oracle数据库监控脚本

set linesize 1000 pagesize 2000
col file_name for a50;
col file_id for 999;
col STATUS for a10;
col tablespace_name for a22;
select file_name,TABLESPACE_NAME,AUTOEXTENSIBLE,STATUS from dba_data_files order by 1;

SELECT A.TABLESPACE_NAME,A.BYTES /1024/1024 TOTAL,B.BYTES /1024/1024 USED, C.BYTES /1024/1024 FREE ,  (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
        FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
        WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME order by 5;

select df.tablespace_name,COUNT (*) datafile_count,
       ROUND(sum(df.total_mb) / 1048576,2) total_mb,
       ROUND(sum(df.current_mb) / 1048576,2) current_mb,
       ROUND((sum(df.current_mb) - sum(free.free_mb)) / 1048576,2) used_mb,
       ROUND(((sum(df.current_mb) - sum(free.free_mb)) / sum(df.total_mb)) * 100,2) used_pct
FROM (select tablespace_name,file_id,sum(decode(autoextensible, 'YES', maxbytes, 'NO', bytes)) total_mb,sum(bytes) current_mb from dba_data_files group by tablespace_name,file_id) df,
 (SELECT   tablespace_name, file_id,SUM (BYTES) free_mb FROM dba_free_space GROUP BY tablespace_name,file_id) free 
WHERE df.tablespace_name = free.tablespace_name(+) and df.file_id=free.file_id(+)
group by df.tablespace_name ORDER BY 6;

col owner for a30;
select owner,count(*) from dba_segments where tablespace_name='SYSTEM' group by owner;

column username format a15;
select username, default_tablespace, temporary_tablespace from dba_users
 where default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM';

select TABLESPACE_NAME,STATUS from dba_tablespaces;

col name for a30;
col value for a30
select name,value from v$parameter where name in ('undo_management', 'undo_retention', 'undo_tablespace');

select * from v$log;

col host_name for a20
SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;
SELECT inst_id, dbid, NAME, TO_CHAR (created, 'yyyy-mm-dd hh24:mi:ss') created, log_mode, TO_CHAR (version_time, 'yyyy-mm-dd hh24:mi:ss') version_time,open_mode FROM gv$database;

col resource_name for a30;
SELECT * FROM gv$resource_limit;

select tablespace_name,status from dba_tablespaces;

SELECT  TO_CHAR (first_time, 'yyyy-mm-dd') DAY, COUNT (*) switch_times, SUM(DECODE (TO_CHAR(first_time,'hh24'),'00',1,0)) h00, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'01',1,0)) h01,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'02',1,0)) h02,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'03',1,0)) h03,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'04',1,0)) h04,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'05',1,0)) h05, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'06',1,0)) h06,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'07',1,0)) h07,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'08',1,0)) h08,
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'09',1,0)) h09, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'10',1,0)) h10, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'11',1,0)) h11, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'12',1,0)) h12, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'13',1,0)) h13, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'14',1,0)) h14, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'15',1,0)) h15, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'16',1,0)) h16, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'17',1,0)) h17, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'18',1,0)) h18, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'19',1,0)) h19, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'20',1,0)) h20, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'21',1,0)) h21, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'22',1,0)) h22, 
SUM (DECODE (TO_CHAR (first_time, 'hh24'),'23',1,0)) h23 
FROM v$log_history 
WHERE first_time > TRUNC (SYSDATE - 30) 
GROUP BY ROLLUP (TO_CHAR (first_time, 'yyyy-mm-dd'));

col path for a30;
select name,path,total_mb,free_mb,GROUP_NUMBER,MOUNT_STATUS,STATE from v$asm_disk order by 1;

select inst_id,name,total_mb,free_mb,GROUP_NUMBER,STATE from gv$asm_diskgroup order by 1,2;

SELECT a.tablespace_name, (a.BYTES)/1024/1024 total, (a.bytes - nvl(b.bytes, 0))/1024/1024 free
 FROM (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) b
WHERE a.tablespace_name = b.tablespace_name(+);

create pfile='/tmp/pfile.ora' from spfile;
ho cat '/tmp/pfile.ora';