本鲨单实列日常巡检脚本涉及部分DG 代码超长.
来源：互联网 发布：新版淘宝购物评级在哪 编辑：程序博客网 时间：2024/04/30 15:59
只要修改部分就可以 开头部分
#修改export ORACLE_BASE=/u01/app/oracleexport ORACLE_SID=orclexport SERVICE_NAMES=orcl_pdexport DBUNAME=orcl_pdexport HOST_NAME=oraclemainexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1export TNS_ADMIN=$ORACLE_HOME/network/adminexport TRACE=$ORACLE_BASE/diag/rdbms/$DBUNAME/$ORACLE_SID/tracelisnter_log=$ORACLE_BASE/diag/tnslsnr/$HOST_NAME/listenerdiag_log=$ORACLE_BASE/diag/rdbmsdump_log=$ORACLE_BASE/admin/$ORACLE_SID/dpdumpaudit_log=$ORACLE_BASE/admin/$ORACLE_SID/adumparchive_log=/u01/archiveThreadID=1flash_dir=backup_dir=/u01/backup/user='shark'archivelog_backup_set='/u01/backup/orcl_archivelog_*'Rman_backup_set='/u01/backup/orcl_datafile_level_*'#修改截止
和结尾部分 收邮件人地址. 当然要保证你的LINUX服务器上能用MAIL 命令发生出邮件.
mail -s `date +%Y%m%d%H`'-'`hostname`_DayCheck shark@139.com < $LOGFILE
#!/bin/bash#11.2.0.1 #修改export ORACLE_BASE=/u01/app/oracleexport ORACLE_SID=orclexport SERVICE_NAMES=orcl_pdexport DBUNAME=orcl_pdexport HOST_NAME=oraclemainexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1export TNS_ADMIN=$ORACLE_HOME/network/adminexport TRACE=$ORACLE_BASE/diag/rdbms/$DBUNAME/$ORACLE_SID/tracelisnter_log=$ORACLE_BASE/diag/tnslsnr/$HOST_NAME/listenerdiag_log=$ORACLE_BASE/diag/rdbmsdump_log=$ORACLE_BASE/admin/$ORACLE_SID/dpdumpaudit_log=$ORACLE_BASE/admin/$ORACLE_SID/adumparchive_log=/u01/archiveThreadID=1flash_dir=backup_dir=/u01/backup/user='shark'archivelog_backup_set='/u01/backup/orcl_archivelog_*'Rman_backup_set='/u01/backup/orcl_datafile_level_*'#修改截止LogName="/var/log/sa/sa`date --date='yesterday' +%d`"export LANG=zh_CN.UTF-8export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"LOGFILE=/home/oracle/dbscripts/logs/ORACLE_MAIN_`date +%F_%H_%M`.txt
#http://blog.csdn.net/zengmuanshaexec >> $LOGFILEecho "ORACLE_MAIN EVERY DAY CHECK:"dateecho " "echo "All Process Number:" ps -ef | wc -lecho "System Process Number: " ps -ef| awk '{print $1}'|grep root | grep -v grep |wc -lecho "Grid RAC Process Number:" ps -ef |awk '{print $1}'|grep grid | grep -v grep |wc -lecho "Oracle User Procuess Number:"ps -ef |awk '{print $1}'|grep oracle | grep -v grep |wc -lecho "Oracle DB BackGround Process Number:" ps -ef| grep ora_ |grep -v grep | wc -lecho "Oracle DB Client Process Link Number:"ps -ef |grep LOCAL=NO | grep -v grep | wc -lecho "Oracle User NO DB Procuess Number:"ps -ef|grep oracle|grep -v root|grep -v LOCAL=NO|grep -v ora_|wc -lecho "Other User Process Number: " ps -ef| awk '{print $1}'|grep -v root | grep -v oracle| grep -v grid| grep -v grep |wc -lecho "检查结论: 正常"echo -eecho "系统运行了时间:"uptime | awk '{print($2,$3,$4,$5)}'echo -eecho "当前连接用户数:"uptime | awk '{print($6,$7)}'echo -eecho "当前系统负载情况 一分钟,五分钟,十五分钟:"echo "比如1.73 说明超负荷73%"uptime | awk '{print($9,$10,$11,$12,$13)}'echo -eecho "NUMA:"numactl --showecho -enumactl --hardwareecho " " echo "数据库重要进程是否存在?" ps -ef|grep -E ora_ckpt_\|ora_smon_\|ora_cjq\|ora_dbrm_\|ora_lgwr\|asm_pmon\|ora_pmon|grep -v grepecho "检查结论:正常" echo " " echo "监听进程状态如何?"$ORACLE_HOME/bin/lsnrctl statusecho "检查结论:正常"echo " "echo "系统挂接点容量大小"df -hecho "检查结论:正常"echo -eecho " "echo "诊断跟踪LOG大小"du -h --max-depth=1 $diag_logecho "检查结论:正常"echo " " echo "监听跟踪LOG大小" du -h --max-depth=1 $lisnter_logecho "检查结论:正常"echo " "echo "审计LOG大小"du -h --max-depth=1 $audit_logecho "检查结论:正常"echo " "echo "DUMP LOG大小"du -h --max-depth=1 $dump_logecho "检查结论:正常"echo " "echo "系统内存大小,剩余量"free -mecho "检查结论:正常"echo -eecho "共享段"ipcs -lipcs -a echo -eecho " "echo "检查备份脚本执行是否成功"ls -htrl $Rman_backup_set |tail -1echo "检查结论:正常"echo -eecho " "echo "归档日志备份是否成功"ls -htrl $archivelog_backup_set|tail -1echo "检查结论:正常"source /home/oracle/.bash_profileexport LANG=zh_CN.UTF-8export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"sqlplus -s / as sysdba <<EOFset sqlprompt ''set heading onset wrap onset pagesize 1000set linesize 250set echo offset feedback offset timing offset colsep ' 'set autot offcol NAME format a6col LOG_MODE format a10col OPEN_MODE format a10col PROTECTION_MODE format a20col DATABASE_ROLE format a13col SWITCHOVER_STATUS format a15col SUPPLEMENTAL_LOG_DATA_MIN format a15col FORCE_LOGGING format a10col CURRENT_SCN format 9999999999999set tab offset head offselect chr(10) from dual;set head onprompt "List Rman Backup Detail"col input format a12col output format a12col status format a9col week format a15SELECT start_time,end_time,to_char(start_time,'day') as week,input,output,status,input_type,zip_ratioFROM( SELECT START_TIME, END_TIME, INPUT_BYTES_DISPLAY as input, OUTPUT_BYTES_DISPLAY as output, STATUS, INPUT_TYPE, ROUND(COMPRESSION_RATIO*100,2) as zip_ratio FROM V\$RMAN_BACKUP_SUBJOB_DETAILS ORDER BY START_TIME DESC) WHERE ROWNUM<35;set head offselect chr(10) from dual;set head onprompt "检查结论:正常"col name format a30prompt "Rman Config Info"col value format a70SELECT * FROM V\$RMAN_CONFIGURATION;set head offselect chr(10) from dual;set head onprompt "检查数据库状态"col name format a20col log_mode format a12COL OPEN_MODE FORMAT A30SELECT NAME, LOG_MODE, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, CURRENT_SCN FROM V\$DATABASE;col db_unique_name format a30col flashback_on format a12SELECT DB_UNIQUE_NAME, ARCHIVELOG_COMPRESSION, DATAGUARD_BROKER, GUARD_STATUS, FLASHBACK_ONFROM V\$DATABASE;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "归档日志目的状态"col DEST_NAME format a20col STATUS format a8col TYPE format a10col DATABASE_MODE format a15col RECOVERY_MODE format a23col PROTECTION_MODE format a20col DESTINATION format a15col SYNCHRONIZED format a15col GAP_STATUS format a10SELECT DEST_NAME, STATUS, TYPE, DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE, DESTINATION, SYNCHRONIZED, GAP_STATUS FROM V\$ARCHIVE_DEST_STATUS A WHERE A.DEST_ID < 3;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "灾备日志应用差异数"select m.max_sequenc,a.max_applied,d.max_deleted,r.max_archive,m.max_sequenc-a.max_applied from(select max(sequence#) as max_sequenc from v\$archived_log where thread#=$ThreadID ) m,(select max(sequence#) as max_applied from v\$archived_log where applied='YES' and thread#=$ThreadID) a,(select max(sequence#) as max_deleted from v\$archived_log where deleted='YES' and thread#=$ThreadID) d,(select max(sequence#) as max_archive from v\$archived_log where archived='YES' and thread#=$ThreadID) r;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "检查无效索引"SELECT OWNER, INDEX_NAME, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE STATUS = ('INVALID') AND OWNER = upper('$user')UNION ALLSELECT INDEX_OWNER AS OWNER, INDEX_NAME, PARTITION_NAME AS TABLE_NAME, STATUS FROM DBA_IND_PARTITIONS WHERE SUBPARTITION_COUNT = 0 AND STATUS = 'UNUSABLE' AND INDEX_OWNER = upper('$user')UNION ALLSELECT INDEX_OWNER AS OWNER, INDEX_NAME, SUBPARTITION_NAME AS TABLE_NAME, STATUS STATUS FROM DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE' AND INDEX_OWNER = upper('$user');set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "检查无效对象"col object_name format a30col owner format a10SELECT OWNER,OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, STATUS FROM DBA_OBJECTS WHERE OWNER NOT IN ('ANONYMOUS','APEX_030200', 'SHARK', 'APE_PUBLIC_USER','APPQOSSYS') AND OWNER NOT IN ('CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDDATA','MGMT_VIEW') AND OWNER NOT IN ('OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS') AND OWNER NOT IN ('OWBSYS_AUDIT','PDEONE','PUBLIC','SCOTT','SI_INFORMTN_SCHEMA','SPATITAL_CSW_ADMIN_USER') AND OWNER NOT IN ('SPATITAL_WFS_ADMIN_USR','SYS','SYSTEM','SYSMAN','WMSYS','XDB','XS$NULL') AND STATUS <> 'VALID' AND OBJECT_NAME NOT IN('PDTYPES','P_CHECK_BATCH','P_DUP_FEE_CFG','P_TRANS_IN','P_TRANS_OUT') ORDER BY LAST_DDL_TIME ASC;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "表空间大小,使用率,剩余量" SELECT TABLESPACE_NAME as Space_Name, TOTAL_SPACE as Max_GB, FILE_MAX_SPACE as File_GB, TOTAL_USED_SPACE as Used_GB, MAX_USED_RATE as Used_Rate FROM (SELECT D.TABLESPACE_NAME, TOTAL_SPACE-FREE_SPACE AS TOTAL_USED_SPACE, TOTAL_SPACE, FILE_MAX_SPACE, ROUND(((TOTAL_SPACE - FREE_SPACE) / FILE_MAX_SPACE) * 100, 2) MAX_USED_RATE FROM ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_SPACE, ROUND(SUM(MAXBYTES) / (1024 * 1024 * 1024), 2) FILE_MAX_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) D, ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, USED_SPACE AS TOTAL_USED_SPACE, TOTAL_SPACE, FILE_MAX_SPACE, ROUND(NVL(USED_SPACE, 1) / NVL(FILE_MAX_SPACE, 1) * 100, 2) MAX_USED_RATE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_SPACE, ROUND(SUM(MAXBYTES) / (1024 * 1024 * 1024), 2) FILE_MAX_SPACE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES_USED) / (1024 * 1024 * 1024), 2) USED_SPACE FROM V\$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ) WHERE MAX_USED_RATE > 5 ORDER BY MAX_USED_RATE DESC;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "检查 运行的JOB"col job_name format a30col job_action format a30SELECT JOB_NAME,JOB_ACTION,RUN_COUNT,FAILURE_COUNT FROM DBA_SCHEDULER_JOBS WHERE owner= upper('$user');prompt "检查结论:正常"col what format a45col instance format 99col log_user format a6col broken format a6col next_sec format a10select job,INSTANCE,log_user,what,total_time,broken,failures next_date,next_sec from dba_jobs;prompt "检查结论:正常"col job_name format a25col cpu_used format a20col owner format a10col status format a10col run_date format a20SELECT LOG_ID, TO_CHAR(LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') RUN_DATE, OWNER, JOB_NAME, STATUS, CPU_USED FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE OWNER IN ( UPPER('$user'),UPPER('$user')) AND LOG_DATE > TRUNC(SYSDATE) ORDER BY 2 ASC; set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual; set head onprompt "自动任务情况:"alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss:ff';alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS:FF';col client_name format a32col consumer_group format a30col window_group format a32col servICE_name format a15col attributes format a55SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,WINDOW_GROUP,SERVICE_NAME,ATTRIBUTES FROM DBA_AUTOTASK_CLIENT; col window_next_time format a26col WINDOW_ACTIVE format a13SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;col job_info format a50col job_name format a30col window_name format a17col job_status format a11col job_duration format a13col window_start_time format a26col job_start_time format a26SELECT CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION,JOB_ERROR,JOB_INFO FROM ( SELECT CLIENT_NAME, WINDOW_NAME, WINDOW_START_TIME, JOB_NAME, JOB_STATUS, JOB_START_TIME, JOB_DURATION, JOB_ERROR, JOB_INFO, ROW_NUMBER() OVER(PARTITION BY CLIENT_NAME ORDER BY JOB_START_TIME DESC) AS RN FROM DBA_AUTOTASK_JOB_HISTORY ) TWHERE RN <= 10;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head off select chr(10) from dual; set head on prompt "内存动态组件分配情况:"col component for a25SELECT COMPONENT, CURRENT_SIZE / 1024 / 1024 / 1024 AS CURRENT_GB, MIN_SIZE / 1024 / 1024 / 1024 AS MINGB, MAX_SIZE / 1024 / 1024 / 1024 AS MAXGB, USER_SPECIFIED_SIZE / 1024 / 1024 / 1024 AS SPECIFIEDMB, OPER_COUNT, LAST_OPER_TYPE, LAST_OPER_MODE, LAST_OPER_TIME, GRANULE_SIZE / 1024 / 1024 as GRANULE_MB FROM V\$MEMORY_DYNAMIC_COMPONENTS; set head off select chr(10) from dual; prompt "检查结论:正常" set head offselect chr(10) from dual; set head onprompt "PGA内存分配释放:"col name format a40col value format 9999,9999,9999,9999.99col unit format a10select name, value, unit,(case when unit = 'bytes' then trunc(value / 1024 / 1024 / 1024, 3) else NULL end) as UNIT_GB from V\$PGASTAT;set head offselect chr(10) from dual; set head on prompt "检查PGA工作区执行情况:"select name, value, round(100 * (value / decode( (select sum(value) from v\$sysstat where name like 'workarea execution%'), 0, null, (select sum(value) from v\$sysstat where name like 'workarea execution%'))), 4) as Rate from v\$sysstat where name like 'workarea executions%';set head offselect chr(10) from dual; set head onprompt "工作区域命中次数:"SELECT LOW_OPTIMAL_SIZE / 1024 LOW_KB, (HIGH_OPTIMAL_SIZE + 1) / 1024 HIGH_KB, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS FROM V\$SQL_WORKAREA_HISTOGRAM WHERE TOTAL_EXECUTIONS != 0;set head offselect chr(10) from dual; set head on prompt "检查PGA内存使用情况:"SELECT A.PROGRAM,COUNT(PID),ROUND(SUM(PGA_USED_MEM / 1024 / 1024),3)AS PGA_USE_MB,ROUND(SUM(PGA_ALLOC_MEM / 1024 / 1024),3)AS PGA_ALLOC_MB,ROUND(SUM(PGA_FREEABLE_MEM / 1024 / 1024),3)AS PGA_FREE_MB,ROUND(SUM(PGA_MAX_MEM / 1024 / 1024),3)AS PGA_MAX_MBFROM V\$PROCESS AINNER JOIN V\$SESSION BON A.ADDR = B.PADDRGROUP BY A.PROGRAMORDER BY PGA_MAX_MB DESC;set head offselect chr(10) from dual;prompt "检查结论:正常" col NEWTEXT format a100col SQL_ID FORMAT A15COL EXE FormAT 9999,9999,9999col ms format 999,999set head offselect chr(10) from dual;set head onprompt "执行次数最多的SQL:"SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,MS FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(ELAPSED_TIME)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))/1000) AS MS FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY EXE DESC ) WHERE ROWNUM < 11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "内存读取最多的"col memory_get_mb format 999,999,999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,MEMORY_GET_MB FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(BUFFER_GETS)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))*8/1024) AS MEMORY_GET_MB FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY MEMORY_GET_MB DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "硬盘读取最多的"col disk_reads_mb format 999,999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,DISK_READS_MB FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(DISK_READS)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))*8/1024) AS DISK_READS_MB FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY DISK_READS_MB DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "整体时间最多的"col elapsed_time_second format 999,999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,ELAPSED_TIME_SECOND FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(ELAPSED_TIME)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))/1000/1000) AS ELAPSED_TIME_SECOND FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY ELAPSED_TIME_SECOND DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "CPU时间最多的"col cpu_time_second format 999,999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,CPU_TIME_SECOND FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(CPU_TIME)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))/1000/1000) AS CPU_TIME_SECOND FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY CPU_TIME_SECOND DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "排序最多的"col sorts format 9999,9999,9999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,SORTS FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(sorts)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))) AS sorts FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY sorts DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "解析最多的"col parse_calls format 9999,9999,9999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,PARSE_CALLS FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(PARSE_CALLS)) AS PARSE_CALLS FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY PARSE_CALLS DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "用户IO等待最多的"col user_io_waite_seconds format 999,999SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,USER_IO_WAITE_SECONDS FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(USER_IO_WAIT_TIME)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))/1000000) AS USER_IO_WAITE_SECONDS FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY USER_IO_WAITE_SECONDS DESC ) WHERE ROWNUM < 11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on col rows_processed format 9999,9999,9999prompt "处理数据最多的"SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,ROWS_PROCESSED FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(ROWS_PROCESSED)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))) AS ROWS_PROCESSED FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY ROWS_PROCESSED DESC ) WHERE ROWNUM < 11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on col concurrency_wait_time_ms format 999,999prompt "并发等待最多的"SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,CONCURRENCY_WAIT_TIME_MS FROM ( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(CONCURRENCY_WAIT_TIME)/SUM(DECODE(EXECUTIONS,0,1,EXECUTIONS))/1000) AS CONCURRENCY_WAIT_TIME_MS FROM V\$SQL GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY CONCURRENCY_WAIT_TIME_MS DESC ) WHERE ROWNUM < 11; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on col "整体微妙" format 999,999prompt "重要性SQL" SELECT SQL_ID,RPAD(TEXT,100,' ') AS NEWTEXT,EXE,"整体微妙"FROM( SELECT SQL_ID, SUBSTR(SQL_TEXT, 1, 100) AS TEXT, SUM(EXECUTIONS) EXE, ROUND(SUM(ELAPSED_TIME) / SUM(DECODE(EXECUTIONS, 0, 1, EXECUTIONS))) AS "整体微妙" FROM V\$SQL WHERE SQL_ID IN ('3g84r6ptyfq6q', '76pns091r3a50', '94vfur4vk6d9y', 'fnjwsvqu2ubc4', '96j8mqktg8awc', '7s23b7adgdsva', 'dkzqdzhbyn0c0', 'gg1g9grybw7vu', '5fhy27g21ck59', 'fhmbn8154sy95', 'fsthx933ucyfm', 'fu4v11a2n93ak', '1gwj0ash8t2zv', '5b5bmcaa75f3b', '8c7qpyaphxp49', '80zarxz2hdp7t', 'c6wqsac2jtsrh', '4xtu0gkggdz73', '0c3awf5jwu04d', '1q8xmykg0ukbz', '2ggwubyc8fqat', 'ctsqvum2vqvrr', '60dng3n4vaw8b', '588r0474qz0n9', '4zm7zpfgwr39g', '0sjfvqqsav8nr', '65xw638ka0r0u', '6ht9zsgs5m8cq','2vq4wtk4szgs5') GROUP BY SQL_ID, SUBSTR(SQL_TEXT, 1, 100) ORDER BY 4 DESC);set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "超过30分钟被取消的SQL"COL USERNAME FORMAT A10COL MODULE FORMAT A20SELECT USERNAME, MODULE, SQL_ID, SQL_EXEC_START, ROUND(ELAPSED_TIME / 1000000) AS SECONDS, ROUND(CPU_TIME/1000000) AS "CPU_SECOND", ROUND(BUFFER_GETS*8/1024) AS "MEM_MB", ROUND(PHYSICAL_READ_BYTES/1024/1024) AS "DISK_MB", ROUND(CONCURRENCY_WAIT_TIME/1000000) AS "CON_WAIT_SEC", ROUND(CLUSTER_WAIT_TIME/1000000) AS "RAC_WAIT_SEC", ROUND(USER_IO_WAIT_TIME/1000000) AS "IO_WAIT_SEC" --BINDS_XML FROM V\$SQL_MONITOR M WHERE MODULE = 'JDBC Thin Client' AND STATUS = 'DONE (ERROR)' ORDER BY SQL_EXEC_START ASC;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on col xvalue format 9999,9999,9999PROMPT "XVALUE 累积总次数"prompt "逻辑读最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('LOGICAL READS') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "物理读最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('physical reads') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "直接路径读最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('physical reads direct') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "段扫描最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('segment scans') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "行锁等待最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('row lock waits') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "缓冲等待最多的段" SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = LOWER('buffer busy waits') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "事务等待最多的段"SELECT * FROM(SELECT OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME, SUM(VALUE) xvalue FROM V\$SEGMENT_STATISTICS WHERE STATISTIC_NAME = ('ITL waits') GROUP BY OBJECT_NAME,OBJECT_TYPE, SUBOBJECT_NAME ORDER BY 4 DESC )WHERE ROWNUM <11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "审计表大小和表空间"COL SEGMENT_NAME FORMAT A30SELECT SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024 AS KB FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'AUD%' ; set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 模式大小"SELECT ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user');set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 索引和表大小"SELECT SEGMENT_TYPE,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user') GROUP BY SEGMENT_TYPE order by segment_type,gb desc;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 普通索引大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user')AND SEGMENT_TYPE='INDEX' GROUP BY SEGMENT_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 分区索引大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user') AND SEGMENT_TYPE='INDEX PARTITION' GROUP BY SEGMENT_NAME,PARTITION_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 子分区索引大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user') AND SEGMENT_TYPE='INDEX SUBPARTITION' GROUP BY SEGMENT_NAME,PARTITION_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 普通表大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user') AND SEGMENT_TYPE='TABLE' GROUP BY SEGMENT_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 分区表大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user')AND SEGMENT_TYPE='TABLE PARTITION' GROUP BY SEGMENT_NAME,PARTITION_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 子分区表大小TOP10"SELECT * FROM (SELECT SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(BYTES)/1024/1024/1024) AS GB FROM DBA_SEGMENTS WHERE OWNER=upper('$user') AND SEGMENT_TYPE='TABLE SUBPARTITION' GROUP BY SEGMENT_NAME,PARTITION_NAME ORDER BY GB DESC) WHERE ROWNUM<11 ;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 表总行数"col NUM_ROWS format 9999,9999,9999SELECT SUM(NUM_ROWS) NUM_ROWS FROM DBA_TABLES WHERE OWNER=upper('$user');set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "$user 表行数TOP10"col NUM_ROWS format 9999,9999,9999SELECT * FROM (SELECT TABLE_NAME,SUM(NUM_ROWS) AS NUM_ROWS FROM DBA_TABLES WHERE OWNER=upper('$user') AND NUM_ROWS>0 GROUP BY TABLE_NAME ORDER BY 2 DESC) WHERE ROWNUM<11;set head offselect chr(10) from dual;prompt "检查结论:正常" set head offselect chr(10) from dual;set head on prompt "归档数和大小"SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD') AS FDATA, COUNT(*) AS ARCHIVE_NUM, ROUND(SUM(BLOCKS * BLOCK_SIZE / 1024 / 1024 / 1024), 0) AS SIZE_GB FROM V\$ARCHIVED_LOG X WHERE X.NAME IS NULL GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD')ORDER BY FDATA ASC; set head offselect chr(10) from dual;prompt "检查结论:正常"set head offselect chr(10) from dual; set head onprompt "每小时日志切换:"col week format a6col date_d format a10col h0 format 999col h1 format 999col h2 format 999col h3 format 999col h4 format 999col h5 format 999col h6 format 999col h7 format 999col h8 format 999col h9 format 999col h10 format 999col h11 format 999col h12 format 999col h13 format 999col h14 format 999col h15 format 999col h16 format 999col h17 format 999col h18 format 999col h19 format 999col h20 format 999col h21 format 999col h22 format 999col h23 format 999SELECT to_char(TRUNC(FIRST_TIME),'YYYY-MM-DD') DATE_D,TO_CHAR(FIRST_TIME, 'Dy') WEEK,COUNT(1) TOTAL,ROUND(24*60/COUNT(1), 2) AVG_SWITH_MINUTE,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '00', 1, 0)) h0,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '01', 1, 0)) h1,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '02', 1, 0)) h2,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '03', 1, 0)) h3,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '04', 1, 0)) h4,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '05', 1, 0)) h5,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '06', 1, 0)) h6,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '07', 1, 0)) h7,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '08', 1, 0)) h8,SUM(DECODE(TO_CHAR(FIRST_TIME, 'hh24'), '09', 1, 0)) h9,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)) h23FROM V\$LOG_HISTORYGROUP BY TRUNC(FIRST_TIME), TO_CHAR(FIRST_TIME, 'Dy')ORDER BY 1 DESC; set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual;set head onprompt "每日各种操作量统计"col fdate for a12col instance_number for 99col command_name for a15col fetches_num for a20col sorts_num for a20col exec_num for a20col parse_num for a20col buffergets_num for a20col diskread_num for a20SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD') AS FDATE , A.INSTANCE_NUMBER, N.COMMAND_NAME, TO_CHAR(SUM(FETCHES_DELTA),'9,9999,9999,9999') AS FETCHES_NUM, TO_CHAR(SUM(SORTS_DELTA),'9,9999,9999,9999') AS SORTS_NUM, TO_CHAR(SUM(EXECUTIONS_DELTA),'9,9999,9999,9999') AS EXEC_NUM, TO_CHAR(SUM(PARSE_CALLS_DELTA),'9,9999,9999,9999') AS PARSE_NUM, TO_CHAR(SUM(ROWS_PROCESSED_DELTA),'9,9999,9999,9999') AS ROWS_NUM, TO_CHAR(SUM(DISK_READS_DELTA),'9,9999,9999,9999') AS DISKREAD_NUM, TO_CHAR(SUM(BUFFER_GETS_DELTA),'9,9999,9999,9999') AS BUFFERGETS_NUM FROM DBA_HIST_SQLSTAT A INNER JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_ID INNER JOIN DBA_HIST_SQLTEXT C ON A.SQL_ID = C.SQL_ID INNER JOIN DBA_HIST_SQLCOMMAND_NAME N ON C.COMMAND_TYPE = N.COMMAND_TYPE WHERE B.BEGIN_INTERVAL_TIME >= trunc(sysdate-1) and B.END_INTERVAL_TIME < trunc(sysdate)GROUP BY TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD') , A.INSTANCE_NUMBER,N.COMMAND_NAMEORDER BY A.INSTANCE_NUMBER,EXEC_NUM DESC; set head offselect chr(10) from dual; set head oncol week format a6col fdate format a12col f_sel_rate format a10col f_upd_rate format a10col f_inse_rate format a10col f_del_rate format a9col f_plsql_rate format a11col f_meth_rate format a10col f_locktable_rate format a9col f_upsert_rate format a9col f_creatable_rate format a5 SELECT FDATE,TO_CHAR(TO_DATE(FDATE,'YYYY-MM-DD'),'DAY') AS WEEK, SUM(TOTAL_EXEC) AS TOTAL_EXEC, SUM(F_SELECT) AS F_SELECT , ROUND(SUM(F_SELECT)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_SEL_RATE, SUM(F_UPDATE) AS F_UPDATE , ROUND(SUM(F_UPDATE)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_UPD_RATE, SUM(F_INSERT) AS F_INSERT , ROUND(SUM(F_INSERT)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_INSE_RATE, SUM(F_DELETE) AS F_DELETE , ROUND(SUM(F_DELETE)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_DEL_RATE, SUM(F_PL_SQL_EXECUTE) AS F_PL_SQL_EXECUTE , ROUND(SUM(F_PL_SQL_EXECUTE)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_PLSQL_RATE, SUM(F_CALL_METHOD) AS F_CALL_METHOD , ROUND(SUM(F_CALL_METHOD)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_METH_RATE, SUM(F_LOCK_TABLE) AS F_LOCK_TABLE , ROUND(SUM(F_LOCK_TABLE)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_LOCKTABLE_RATE, SUM(F_UPSERT) AS F_UPSERT , ROUND(SUM(F_UPSERT)/SUM(TOTAL_EXEC)*100,2)||'%' AS F_UPSERT_RATE, SUM(NVL(F_CREATE_TABLE,0)) AS F_CREATE_TABLE, ROUND(SUM(NVL(F_CREATE_TABLE,0))/SUM(TOTAL_EXEC)*100,2)||'%' AS F_CREATABLE_RATE FROM( SELECT FDATE， (CASE WHEN COMMAND_NAME ='SELECT' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_SELECT, (CASE WHEN COMMAND_NAME ='UPDATE' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_UPDATE, (CASE WHEN COMMAND_NAME ='INSERT' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_INSERT, (CASE WHEN COMMAND_NAME ='DELETE' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_DELETE, (CASE WHEN COMMAND_NAME ='PL/SQL EXECUTE' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_PL_SQL_EXECUTE, (CASE WHEN COMMAND_NAME ='CALL METHOD' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_CALL_METHOD, (CASE WHEN COMMAND_NAME ='LOCK TABLE' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_LOCK_TABLE, (CASE WHEN COMMAND_NAME ='UPSERT' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_UPSERT, (CASE WHEN COMMAND_NAME ='CREATE TABLE' THEN REPLACE(EXEC_NUM,',',NULL) END) AS F_CREATE_TABLE, SUM(REPLACE(EXEC_NUM,',',NULL)) AS TOTAL_EXEC FROM ( SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD') AS FDATE , A.INSTANCE_NUMBER, N.COMMAND_NAME, SUM(FETCHES_DELTA) AS FETCHES_NUM, SUM(SORTS_DELTA) AS SORTS_NUM, SUM(EXECUTIONS_DELTA) AS EXEC_NUM, SUM(PARSE_CALLS_DELTA) AS PARSE_NUM, SUM(ROWS_PROCESSED_DELTA) AS ROWS_NUM, SUM(DISK_READS_DELTA) AS DISKREAD_NUM, SUM(BUFFER_GETS_DELTA) AS BUFFERGETS_NUM FROM DBA_HIST_SQLSTAT A INNER JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_ID INNER JOIN DBA_HIST_SQLTEXT C ON A.SQL_ID = C.SQL_ID INNER JOIN DBA_HIST_SQLCOMMAND_NAME N ON C.COMMAND_TYPE = N.COMMAND_TYPE WHERE B.BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE-35) AND B.END_INTERVAL_TIME < TRUNC(SYSDATE) GROUP BY TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD') , A.INSTANCE_NUMBER,N.COMMAND_NAME ) T GROUP BY FDATE,COMMAND_NAME,EXEC_NUM ) GROUP BY FDATE,TO_CHAR(TO_DATE(FDATE,'YYYY-MM-DD'),'DAY') ORDER BY 1 DESC;set head offselect chr(10) from dual; set head onprompt "游标:"col parameter format a30col value format 999999col usage format a10SELECT 'session_cached_cursors' PARAMETER,LPAD(VALUE,5) VALUE,DECODE(VALUE,0,' n/a',TO_CHAR(100*USED /VALUE,'990')||'%') USAGEFROM (SELECT MAX(S.VALUE) USED FROM V\$STATNAME N,V\$SESSTAT S WHERE N.NAME='session cursor cache count' AND S.STATISTIC#=N.STATISTIC#), (SELECT VALUE FROM V\$PARAMETER WHERE NAME='session_cached_cursors') UNION ALL SELECT 'open_cursors', LPAD(VALUE,5), TO_CHAR(100 * USED/VALUE,'990')||'%' FROM (SELECT MAX(SUM(S.VALUE)) USED FROM V\$STATNAME N, V\$SESSTAT S WHERE N.NAME IN ('opened cursors current','session cursor cache cunt') AND S.STATISTIC#=N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V\$PARAMETER WHERE NAME='open_cursors');set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual; set head onprompt "外键没有建索引:"COL 外键表 FORMAT A31COL 外键名 FORMAT A31COL 关联表 FORMAT A31COL 关联表字段 FORMAT A31SELECT C.TABLE_NAME "外键表", C.CONSTRAINT_NAME "外键名", T.TABLE_NAME "关联表", ACC.COLUMN_NAME "关联表字段"FROM ALL_CONSTRAINTS T, ALL_CONSTRAINTS C, ALL_CONS_COLUMNS ACCWHERE C.R_CONSTRAINT_NAME = T.CONSTRAINT_NAMEAND C.TABLE_NAME = ACC.TABLE_NAMEAND C.CONSTRAINT_NAME = ACC.CONSTRAINT_NAMEAND ACC.OWNER='OSSC'AND NOT EXISTS (SELECT '1' FROM ALL_IND_COLUMNS AID WHERE AID.TABLE_NAME = ACC.TABLE_NAME AND AID.COLUMN_NAME = ACC.COLUMN_NAME)ORDER BY C.TABLE_NAME;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual; set head onprompt "索引超过200M的碎化度:"SELECT IDX.OWNER OWNER, IDX.TABLE_NAME TABLENAME, IDX.INDEX_NAME INDEX_NAME, COL_LEN, AVG_ROW_LEN, ROUND(IDX.COL_LEN/TBL.AVG_ROW_LEN*100,4) AS IND_TAB_LEN_RATE, IND_SIZE_MB, TAB_SIZE_MB, ROUND(IDX.IND_SIZE_MB/TBL.TAB_SIZE_MB*100,4) AS IND_TAB_SIZE_RATE, ROUND((IDX.IND_SIZE_MB/TBL.TAB_SIZE_MB)/(IDX.COL_LEN/TBL.AVG_ROW_LEN)*100,4) AS IND_SIZE_LEN_RATE_RATEFROM ( SELECT I.OWNER OWNER,I.INDEX_NAME INDEX_NAME,I.TABLE_OWNER TABLE_OWNER,I.TABLE_NAME TABLE_NAME,S1.BYTES/1024/1024 AS IND_SIZE_MB, SUM(S1.BLOCKS) BLOCKS,SUM(C.COLUMN_LENGTH) AS COL_LEN FROM DBA_SEGMENTS S1,DBA_INDEXES I,DBA_IND_COLUMNS C WHERE S1.OWNER=I.OWNER AND S1.SEGMENT_NAME=I.INDEX_NAME AND I.index_name=C.INDEX_NAME AND C.COLUMN_LENGTH > 0 AND I.OWNER ='OSSC' GROUP BY I.OWNER,I.INDEX_NAME,I.TABLE_OWNER,I.TABLE_NAME,S1.BYTES ) IDX, ( SELECT T.OWNER OWNER,T.TABLE_NAME,T.AVG_ROW_LEN,S2.BYTES/1024/1024 AS TAB_SIZE_MB,SUM(S2.BLOCKS) BLOCKS FROM DBA_SEGMENTS S2,DBA_TABLES T WHERE S2.OWNER=T.OWNER AND S2.SEGMENT_NAME=T.TABLE_NAME AND T.AVG_ROW_LEN > 0 AND T.OWNER =upper('$user') GROUP BY T.OWNER,T.TABLE_NAME,T.AVG_ROW_LEN,S2.BYTES ) TBL WHERE IDX.TABLE_OWNER=TBL.OWNER AND IDX.TABLE_NAME=TBL.TABLE_NAME AND IDX.IND_SIZE_MB/TBL.TAB_SIZE_MB > IDX.COL_LEN/TBL.AVG_ROW_LEN AND IDX.IND_SIZE_MB > 100 ORDER BY 7 DESC;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" set head offselect chr(10) from dual; set head onprompt "低选择率的索引:"SELECT I.TABLE_NAME, I.INDEX_NAME, I.DISTINCT_KEYS, t.num_rows, ROUND(I.DISTINCT_KEYS / T.NUM_ROWS*100,5)||'%' AS SELECT_RATE FROM DBA_INDEXES I, DBA_TABLES T WHERE I.TABLE_NAME = T.TABLE_NAME AND t.OWNER=upper('$user') AND T.NUM_ROWS>0 and I.DISTINCT_KEYS / T.NUM_ROWS <0.5 ORDER BY SELECT_RATE ASC;set head offselect chr(10) from dual;set head onprompt "检查结论:正常" exit;EOFecho " "echo "告警文件的告警内容"OLDLANG=$LANGexport LANG=en_US.UTF-8cat $TRACE/alert_$ORACLE_SID.log | grep -E ^ORA-\|Reconfig\|.trc\|abort\|Shut\|^Start\|error\|Instance\|Suspend\|Resuming\|Deadlock\|Global\|"`date "+%b %d"`"\|"`date -d last-day "+%b %d"`"|grep -v Starting|grep -v Auto-tuning|grep -v Fatal|grep -v Tnsexport LANG=$OLDLANGecho "检查结论:正常"#echo " "#export LANG=en_US.UTF-8#echo "ASM LOG"#cat $ASMTRACE/alert_+ASM1.log | grep -E ^ORA-\|"`date "+%b %d"`"\|"`date -d last-day "+%b %d"`"#echo "检查结论:正常"echo -eecho "系统内存信息"cat /proc/meminfoecho "检查结论:正常"echo -eecho "系统内存区域信息"cat /proc/zoneinfoecho "检查结论:正常"export LANG=$OLDLANGecho -eecho "系统中进程使用内存TOP30"ps auxw|head -1;ps auxw|sort -rn -k4|head -30echo "检查结论:正常"echo -eecho "系统中进程使用虚拟内存TOP30"COUNT=30printf "%$(tput cols)s\n" | tr ' ' '=';printf "Memory%-6sPID%-5sUser%-7sCommand\n";printf "%$(tput cols)s\n" | tr ' ' '-';ps -eo size,pid,user,command | sed "1 d" | sort -rn | if [[ -n $COUNT ]]; then head -n $COUNT; else cat; fi | \awk ' { units[1024**2] = "GB"; units[1024] = "MB"; units[1] = "KB"; for (x = 1024**3; x >= 1; x /= 1024) { if ($1 >= x) { if (x < 1024) { printf ("%-6.0f %-4s ", $1/x, units[x]); } else { printf ("%-6.2f %-4s ", $1/x, units[x]); } break; } } } { printf ("%-7s %-10s ", $2, $3); } { for (x = 4; x <= NF; x++) { printf ("%s ", $x); } print ("\r"); }';printf "%$(tput cols)s\n"|tr ' ' '=';echo -eecho "进程实际独占物理内存大小："#!/bin/bash# Get current swap usage for all running processes# writted by zengfankunfunction GetProcRam {SUM=0OVERALL=0for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; doPID=`echo $DIR | cut -d / -f 3`if [ -d $DIR ]; thenPROGNAME=`ps -p $PID -o cmd --no-headers`RAM_PAGES=`cat $DIR/statm | awk '{print $2}'`SHARD_PAGES=`cat $DIR/statm | awk '{print $3}'`let REAL_PAGES=$RAM_PAGES-$SHARD_PAGESREAL_RAM=$(expr $REAL_PAGES \* 4 )if [ $REAL_RAM -gt 0 ] ; thenecho -e "$PID\0011$REAL_RAM\0011$PROGNAME"filet SUM=$SUM+$REAL_RAMfidoneecho "TOTAL RAM USED:$(expr $SUM / 1024) MB"}echo -e "PID\0011REAL_RAM_USED(KB)\0011PROGNAME"GetProcRam |sort -rn -k2#!/bin/bash# Get current swap usage for all running processes# writted by zengfankun http://blog.csdn.net/zengmuanshaecho -eecho "SWAP磁盘上的进程和占用大小："function getswap {SUM=0OVERALL=0for DIR in `find /proc/ -maxdepth 1 -type d | egrep "^/proc/[0-9]"` ; doPID=`echo $DIR | cut -d / -f 3`PROGNAME=`ps -p $PID -o comm --no-headers`for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk '{ print $2 }'`dolet SUM=$SUM+$SWAPdoneif [ $SUM -gt 0 ]; then echo -e "$PID\0011$SUM\0011$PROGNAME"filet OVERALL=$OVERALL+$SUMSUM=0doneecho "Overall swap used: $OVERALL"}echo -e "PID\0011SWAP USED (BYTES)\0011PROGNAME"getswap |sort -rn -k2 date#发送邮件 mail -s `date +%Y%m%d%H`'-'`hostname`_DayCheck shark@paymentbillsservice.com < $LOGFILE 0 0
- 本鲨单实列日常巡检脚本涉及部分DG 代码超长.
- 巡检工具涉及脚本注解
- [Python] Oracle rac dg 巡检脚本
- oracle on linux 巡检脚本-部分
- ORACLE 巡检脚本
- AIX巡检脚本
- ORACLE 巡检脚本
- linux日常巡检脚本
- ORACLE 巡检脚本
- 数据库巡检脚本
- Aix巡检脚本
- sql server 巡检脚本
- AIX巡检脚本
- Server 数据库巡检脚本
- 数据库巡检脚本
- linux 巡检脚本
- AIX巡检脚本
- oracle 巡检脚本
- poj1258Agri-Net（最小生成树）
- 如何生成 hprof文件 + weblogic启动文件配置
- 客户端的IP地址伪造、CDN、反向代理、获取的那些事儿
- RSA算法原理（二）
- Erasure Coding技术在Hadoop中的实现
- 本鲨单实列日常巡检脚本涉及部分DG 代码超长.
- onNewIntent调用时机
- 把rpm package 安装到ubuntu上
- iPhone ZBar库 中文乱码解决方法--重新编译libzbar.a
- List集合去重复
- Redis redis.clients.jedis.exceptions.JedisDataException: ERR Operation against a key holding the wr
- 简单的log4j.properties示例
- javapns 推送ios消息
- iOS 使用 AFNetwork && Alamofire 实时监测网络状况
