日常点检

来源：这里教程网时间：2026-03-03 18:58:40 作者：

-- JOB 检查 select t.owner, t.job_name, t.status, to_char(t.actual_start_date, 'yyyy-mm-dd hh24:mi:ss'), t.additional_info, t.error# from dba_scheduler_job_run_details t where t.status = 'FAILED' --job名称 order by log_date desc; -------------------------------------------------------------- select t.owner, t.job_name, t.status, count(t.job_name) errorsum, to_char(max(t.actual_start_date), 'yyyy-mm-dd hh24:mi:ss') actual_start_date, t.error#, t.additional_info from dba_scheduler_job_run_details t where t.status = 'FAILED' and t.actual_start_date > sysdate - 1 group by t.owner, t.job_name, t.status, t.additional_info, t.error# order by actual_start_date desc; -------------------------------------------------------------- -- ALERT 检查当天报错 WITH diag_alert_ext AS (SELECT /*+ materialize */ originating_timestamp, message_text FROM v$diag_alert_ext WHERE originating_timestamp > systimestamp - INTERVAL '1' DAY) SELECT originating_timestamp, message_text FROM diag_alert_ext WHERE message_text LIKE '%Error%' OR message_text LIKE '%Fail%' OR message_text LIKE '%WARNING%' OR message_text LIKE '%Invalid%' OR message_text LIKE '%ORA-%' OR message_text LIKE '%Global Enqueue Services%' OR message_text LIKE '%dead%' ORDER BY originating_timestamp DESC; -- RMAN 检查 set line222 set pagesize1000 col status for a10 col input_type for a20 col INPUT_BYTES_DISPLAY for a10 col OUTPUT_BYTES_DISPLAY for a10 col TIME_TAKEN_DISPLAY for a10 select input_type, status, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss'), to_char(end_time, 'yyyy-mm-dd hh24:mi:ss'), input_bytes_display, output_bytes_display, time_taken_display, COMPRESSION_RATIO from v$rman_backup_job_details order by 3 desc; -- 表空间检查 set line222 set pagesize1000 SELECT tablespace_name, round(used_space * 8 / 1024, 2) used_space, round(tablespace_size * 8 / 1024, 2) tablespace_size, round(used_percent, 2) || '%' used_percents FROM dba_tablespace_usage_metrics ORDER BY used_percent DESC; -- ASM 检查 set line222 select name, state, offline_disks, type, total_mb, free_mb, case when round((1 - (free_mb / total_mb)) * 100, 2) > 90 then to_char(round((1 - (free_mb / total_mb)) * 100, 2), 999.99) else to_char(round((1 - (free_mb / total_mb)) * 100, 2), 999.99) end as "%FULL" from v$asm_diskgroup; -- Archive log 检查 SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23 , COUNT(*) TOTAL FROM v$log_history a where SYSDATE - first_time < 35 GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by 1; -- 临时表空间检查 select df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl(FS.UsedSpace, 0) "Used(MB)", (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)", round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)" FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace FROM dba_TEMP_files GROUP BY tablespace_name) df, (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace FROM gV$temp_extent_pool GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+); -- 检查主机磁盘空间 !df -Th -- 分区表检查 SELECT t2.table_owner, t1.table_name, t1.max_partition_name, t2.high_value FROM (SELECT table_name, MAX(partition_name) AS max_partition_name FROM dba_tab_partitions GROUP BY table_name) t1, (SELECT table_owner, table_name, partition_name, high_value FROM dba_tab_partitions WHERE table_owner NOT IN ('SYS', 'SYSAUX', 'SYSTEM', 'AUDSYS')) t2 WHERE t1.table_name = t2.table_name AND t1.max_partition_name = t2.partition_name ORDER BY 1, 2; -- 控制文件检查 col name format a40 select name,status from v$controlfile; -- TOP 10 物理读 SQL col sql_text format a30 SELECT * FROM (SELECT disk_reads, username, parsing_user_id, sql_id, executions, elapsed_time / 1000000, sql_text FROM v$sql, dba_users WHERE user_id = parsing_user_id ORDER BY disk_reads DESC) WHERE rownum <= 10; -- TOP 10 逻辑读 SQL SELECT * FROM (SELECT buffer_gets, username, parsing_user_id, sql_id, executions, elapsed_time / 1000000, sql_text FROM v$sql, dba_users WHERE user_id = parsing_user_id ORDER BY buffer_gets DESC) WHERE rownum <= 10; -- TOP 10 执行时间 SQL SELECT * FROM (SELECT buffer_gets, username, parsing_user_id, sql_id, executions, elapsed_time / 1000000, sql_text FROM v$sql, dba_users WHERE user_id = parsing_user_id ORDER BY elapsed_time DESC) WHERE rownum <= 10; -- 无效对象检查 set line222 set pagesize1000 COLUMN owner FORMAT a85 COLUMN object_name FORMAT a30 COLUMN object_type FORMAT a20 COLUMN status FORMAT a75 SELECT owner , object_name , object_type , DECODE( status , 'VALID' , status , status) status FROM dba_objects WHERE status <> 'VALID' ORDER BY owner , object_name; -- 检查并行度 col table_name format a35 col index_name format a35 Rem Tables that have Indexes with not the same DOP Rem !!!!! This command can take some time to execute !!! Rem ---------------------------------------------------; set lines 150 select substr(t.owner,1,15) Owner , t.table_name , substr(trim(t.degree),1,7) Degree , substr(trim(t.instances),1,9) Instances, i.index_name , substr(trim(i.degree),1,7) Degree , substr(trim(i.instances),1,9) Instances from all_indexes i, all_tables t where ( trim(i.degree) != trim(t.degree) or trim(i.instances) != trim(t.instances) ) and i.owner = t.owner and i.table_name = t.table_name; DG 检查主库： sql>archive log list 备库： select process,status,thread#,sequence# from v$managed_standby;

编辑推荐：

返回列表

日常点检

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推