check_oracle

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(你的SQL_ID)); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(你的SQL_ID, NULL, NULL, ADVANCED)); -- -- 准备工作SQL*Plus 全局格式设置 -- set linesize 300 pagesize 9999 long 99999 colsep | trimspool on verify off feedback off col name format a30 col value format a60 col description format a60 col file_name format a60 col member format a60 col owner format a20 col object_name format a30 col segment_name format a30 col tablespace_name format a30 col sql_text format a80 col error format a30 col username format a25 col host_name format a30 col comments format a60 col action_time format a25 alter session set nls_date_formatyyyy-mm-dd hh24:mi:ss; -- -- 一、 数据库基本信息与实例巡检 -- ---- 1.1 数据库版本与组件 -- 查看数据库完整版本信息 select * from v$version; -- 查看数据库组件及状态 column COMP_NAME for a40 column VERSION for a20 column STATUS for a20 select comp_name, version, status from dba_registry; --查看Oracle选项信息 column Option_Name for a40 column Installed for a20 select parameter as Option_Name,value as Installed from v$option order by value desc; -- 查看数据库补丁历史 (适用 10g, 11g) column action for a20 column version for a30 column COMMENTS for a40 select to_char(action_time,yyyy-mm-dd hh24:mi:ss) action_time, action, version, comments from sys.dba_registry_history order by 1 desc; --查看数据库补丁历史 (适用 12c, 19c, 21c, 23ai, 26ai) select patch_id, action, status, action_time, description from dba_registry_sqlpatch order by action_time desc; -- 查看多租户 CDB/PDB 信息 (适用 12c 及以上) select name,cdb,con_id,open_mode from v$database; select con_id,name,open_mode,total_size/1024/1024/1024 total_size_gb from v$pdbs order by con_id; -- os负载统计 column stat_name format a25 column value format 999,999,999,999.99 select inst_id,stat_name,value from gv$osstat order by inst_id,stat_name; ---- 1.2 数据库与实例配置 -- 查看 RAC/单机 实例运行状态及节点信息 column host_name for a30 select inst_id, instance_name, host_name, version, status, logins, startup_time from gv$instance order by inst_id; -- 查看数据库基础模式属性 (归档、闪回、强制日志等) column NAME for a10 column LOG_MODE for a10 column OPEN_MODE for a15 column PROTECTION_MODE for a20 column DATABASE_ROLE for a25 column FLASHBACK_ON for a15 select dbid, name, log_mode, open_mode, protection_mode, database_role, force_logging, flashback_on from v$database; -- 检查数据库是否使用 SPFILE 启动 select decode(value,null,pfile,spfile) spfile_type,value from v$parameter where namespfile; --数据库目录对象 column DIRECTORY_NAME for a30 column DIRECTORY_PATH for a70 select owner,directory_name,directory_path from dba_directories order by owner,directory_name; --dblink 检查 set lines 260 column OWNER for a20 column DB_LINK for a20 column USERNAME for a20 column HOST for a20 column CREATED for a20 select owner,db_link,username,host,created from dba_db_links order by owner,db_link; --数据库告警日志目录 column name for a25 column VALUE for a80 select name,value from v$diag_info; -- 字符集检查 column PARAMETER for a30 column VALUE for a30 select inst_id, parameter, value from gv$nls_parameters where parameter in (NLS_LANGUAGE,NLS_TERRITORY,NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET); -- 1.9 数据库时区 column SESSIONTIMEZONE for a20 select dbtimezone,sessiontimezone from dual; ---- 1.3 重要初始化参数与隐含参数 -- 查看核心资源与性能参数配置 select inst_id, name, value, isdefault from gv$parameter where name in (processes,sessions,db_cache_size,shared_pool_size,pga_aggregate_target,sga_target,memory_target,undo_retention,log_archive_dest_1,compatible) order by name, inst_id; -- 检查系统关键隐含参数 col name for a45 col description for a60 select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx b.indx and a.ksppinm in (_PX_use_large_pool,_clusterwide_global_transactions,_gc_defer_time,_resource_manager_always_off,_resource_manager_always_on,_serial_direct_read,_optimizer_use_feedback,_bloom_pruning_enabled,_gc_policy_time,_bloom_filter_enabled,_gc_read_mostly_locking,_gc_undo_affinity,_undo_autotune,_optimizer_extended_cursor_sharing_rel,_optimizer_extended_cursor_sharing,_optimizer_adaptive_cursor_sharing); -- -- 二、 物理结构与存储空间巡检 -- ---- 2.1 控制文件与日志文件 -- 查看控制文件位置及大小 column NAME for a60 select name, status, block_size*file_size_blks/1024/1024 as size_mb from v$controlfile order by name; -- 查看在线重做日志组及成员 (大小、状态) set linesize 260 column MEMBER for a50 column STATUS for a20 select l.thread#,l.group#,l.sequence#,l.bytes/1024/1024 size_mb,l.members,l.archived,l.status,f.member from v$log l join v$logfile f on l.group#f.group# order by l.group#,f.member; -- 统计最近 7 天日志切换频率 (每天每小时切换次数) select to_char(first_time,yyyy-mm-dd) day, to_char(first_time,hh24) hour, count(*) switches from v$log_history where first_time trunc(sysdate-7) group by to_char(first_time,yyyy-mm-dd), to_char(first_time,hh24) order by 1,2; SET LINESIZE 300 SET PAGESIZE 100 COL DATE FOR A10 COL 00 FOR 9999 COL 01 FOR 9999 COL 02 FOR 9999 COL 03 FOR 9999 COL 04 FOR 9999 COL 05 FOR 9999 COL 06 FOR 9999 COL 07 FOR 9999 COL 08 FOR 9999 COL 09 FOR 9999 COL 10 FOR 9999 COL 11 FOR 9999 COL 12 FOR 9999 COL 13 FOR 9999 COL 14 FOR 9999 COL 15 FOR 9999 COL 16 FOR 9999 COL 17 FOR 9999 COL 18 FOR 9999 COL 19 FOR 9999 COL 20 FOR 9999 COL 21 FOR 9999 COL 22 FOR 9999 COL 23 FOR 9999 COL TOTAL FOR 99999 WITH log_stats AS ( SELECT TRUNC(first_time) log_date, 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, COUNT(*) total FROM v$log_history WHERE first_time SYSDATE - 7 GROUP BY TRUNC(first_time) ) SELECT * FROM ( SELECT TO_CHAR(log_date,MM-DD) DATE, h00 00,h01 01,h02 02,h03 03,h04 04,h05 05,h06 06,h07 07, h08 08,h09 09,h10 10,h11 11,h12 12,h13 13,h14 14,h15 15, h16 16,h17 17,h18 18,h19 19,h20 20,h21 21,h22 22,h23 23, total TOTAL FROM log_stats UNION ALL SELECT Average, ROUND(AVG(h00)),ROUND(AVG(h01)),ROUND(AVG(h02)),ROUND(AVG(h03)), ROUND(AVG(h04)),ROUND(AVG(h05)),ROUND(AVG(h06)),ROUND(AVG(h07)), ROUND(AVG(h08)),ROUND(AVG(h09)),ROUND(AVG(h10)),ROUND(AVG(h11)), ROUND(AVG(h12)),ROUND(AVG(h13)),ROUND(AVG(h14)),ROUND(AVG(h15)), ROUND(AVG(h16)),ROUND(AVG(h17)),ROUND(AVG(h18)),ROUND(AVG(h19)), ROUND(AVG(h20)),ROUND(AVG(h21)),ROUND(AVG(h22)),ROUND(AVG(h23)), ROUND(AVG(total)) FROM log_stats UNION ALL SELECT Total, SUM(h00),SUM(h01),SUM(h02),SUM(h03),SUM(h04),SUM(h05),SUM(h06),SUM(h07), SUM(h08),SUM(h09),SUM(h10),SUM(h11),SUM(h12),SUM(h13),SUM(h14),SUM(h15), SUM(h16),SUM(h17),SUM(h18),SUM(h19),SUM(h20),SUM(h21),SUM(h22),SUM(h23), SUM(total) FROM log_stats ) ORDER BY CASE WHEN DATEAverage THEN 2 WHEN DATETotal THEN 3 ELSE 1 END, DATE; ---- 2.2 表空间与数据文件 -- 查看表空间使用率及空间余量 select a.tablespace_name, round(a.bytes/1024/1024,2) total_mb, round((a.bytes-nvl(b.bytes,0))/1024/1024,2) used_mb, round(nvl(b.bytes,0)/1024/1024,2) free_mb, round((a.bytes-nvl(b.bytes,0))/a.bytes*100,2) pct_used from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a left join (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b on a.tablespace_name b.tablespace_name order by pct_used desc; -- 查看数据文件信息 set linesize 260 column FILE_NAME for a50 column TABLESPACE_NAME for a20 select file_id,file_name,tablespace_name,bytes/1024/1024 size_mb,autoextensible,maxbytes/1024/1024 max_mb,status from dba_data_files order by tablespace_name,file_id; -- 临时表空间使用率 select d.tablespace_name, round((f.bytes_freef.bytes_used)/1024/1024,2) total_mb, round(nvl(p.bytes_used,0)/1024/1024,2) used_mb, round(nvl(p.bytes_used,0)/(f.bytes_freef.bytes_used)*100,2) pct_used from v$temp_space_header f, dba_temp_files d, v$temp_extent_pool p where f.tablespace_name() d.tablespace_name and f.file_id() d.file_id and p.file_id() d.file_id; -- 临时表空间数据文件信息 select file_id,file_name,tablespace_name,bytes/1024/1024 size_mb,autoextensible,maxbytes/1024/1024 max_mb,status from dba_temp_files order by tablespace_name,file_id; -- UNDO 表空间状态与使用情况 select b.tablespace_name, b.file_name, b.autoextensible, a.retention, b.bytes/1024/1024 total_mb, round((b.bytes-nvl(c.bytes,0))/b.bytes*100,2) pct_used from dba_tablespaces a, dba_data_files b left join (select file_id, sum(bytes) bytes from dba_free_space group by file_id) c on b.file_id c.file_id where a.tablespace_name b.tablespace_name and a.contents UNDO; ---- 2.3 ASM 磁盘与磁盘组 -- ASM 磁盘组空间状态 column NAME for a15 column STATE for a15 column TYPE for a15 select group_number, name, state, type, total_mb, free_mb, round((total_mb-free_mb)/nullif(total_mb,0)*100,2) pct_used from v$asm_diskgroup order by group_number; -- ASM 磁盘路径与离线状态 column NAME for a15 column PATH for a30 column FAILGROUP for a20 select group_number, disk_number, name, path, failgroup, total_mb, free_mb, mode_status, state from v$asm_disk order by group_number, disk_number; --asm rebalance 检查 select group_number,operation,state,power,actual,sofar,est_work,est_rate,est_minutes from v$asm_operation; -- -- 三、 备份、恢复与高可用架构 (RMAN/FRA) -- ---- 3.1 归档与闪回区 (FRA) -- 查看闪回区空间使用率 column NAME for a50 select name, space_limit/1024/1024/1024 limit_gb, space_used/1024/1024/1024 used_gb, round(space_used/nullif(space_limit,0)*100,2) pct_used from v$recovery_file_dest; -- 查看闪回区内部各类文件占比 select file_type, percent_space_used, percent_space_reclaimable, number_of_files from v$flash_recovery_area_usage; ---- 3.2 RMAN 备份状态 -- 检查最近 7 天的 RMAN 备份任务状态 column START_TIME for a20 column ELAPSED for a20 column STATUS for a15 column INPUT_TYPE for a15 column OUTPUT_DEVICE_TYPE for a20 column INPUT_BYTES_DISPLAY for a20 column OUTPUT_BYTES_DISPLAY for a20 select start_time, time_taken_display elapsed, status, input_type, output_device_type, input_bytes_display, output_bytes_display from v$rman_backup_job_details where start_time sysdate-7 order by start_time desc; -- 检查 RMAN 备份的控制文件与 SPFILE select bs.recid bs_key, bp.piece#, bp.status, bs.controlfile_included, sp.spfile_included, bs.completion_time from v$backup_set bs join v$backup_piece bp on bs.set_stamp bp.set_stamp and bs.set_count bp.set_count left join (select distinct set_stamp, set_count, YES spfile_included from v$backup_spfile) sp on bs.set_stamp sp.set_stamp where bp.status in (A,X) and bs.completion_time sysdate-7 order by bs.completion_time desc; ---- 3.3 Data Guard 同步状态 (针对 ADG/DG 环境) -- 检查 DG 物理备库的日志应用状态 (非DG环境返回空行) select process, status, thread#, sequence#, block#, blocks from v$managed_standby; select name,value,unit,time_computed from v$dataguard_stats; --standby redo log 检查 select thread#,group#,sequence#,bytes/1024/1024 size_mb,status from v$standby_log order by group#; -- -- 四、 性能调优与并发访问 -- ---- 4.1 内存与命中率 (SGA/PGA) --sga 使用情况 column VALUE for 999,999,999,999 select * from v$sga; --buffer cache 命中率 select round((1-(physical.value/(db_block.valueconsistent.value)))*100,2) hit_ratio from v$sysstat physical,v$sysstat db_block,v$sysstat consistent where physical.namephysical reads and db_block.namedb block gets and consistent.nameconsistent gets; --shared pool 命中率 select round(sum(pinhits)/sum(pins)*100,2) hit_ratio from v$librarycache; -- 检查 PGA 分配与建议值 select inst_id, round(sum(pga_alloc_mem)/1024/1024,2) pga_alloc_mb, round(sum(pga_used_mem)/1024/1024,2) pga_used_mb from gv$process group by inst_id; --pga 使用情况 column NAME for a50 select name,value/1024/1024 value_mb from v$pgastat order by name; --排序命中率 select round((mem.value/(mem.valuedisk.value))*100,2) sort_hit_ratio from v$sysstat mem,v$sysstat disk where mem.namesorts (memory) and disk.namesorts (disk); -- 提取占 PGA 内存最大的 10 个进程 column PROGRAM for a30 column SPID for a10 column PGA_USED_MEM for a20 column PGA_USED_MEM for 999,999,999,999 column PGA_ALLOC_MEM for a20 column PGA_ALLOC_MEM for 999,999,999,999 select * from (select inst_id, pid, spid, program, pga_used_mem, pga_alloc_mem from gv$process order by pga_alloc_mem desc) where rownum 10; ---- 4.2 等待事件与死锁/阻塞 (Row Lock)/SQL --当前等待事件 column EVENT for a30 column WAIT_CLASS for a20 column SECONDS_IN_WAIT for a20 column SECONDS_IN_WAIT for 999999999 select sid,serial#,username,event,wait_class,seconds_in_wait,state from v$session where wait_classIdle order by seconds_in_wait desc; --查看系统 Top 10 等待事件 column EVENT for a30 select * from (select event, total_waits, time_waited_micro/1000000 time_secs, average_wait from v$system_event where wait_class ! Idle order by time_waited_micro desc) where rownum 10; -- 查看当前行级锁阻塞 (阻塞者与被阻塞者) column BLOCKING_SQL for a20 column WAITER_SQL for a20 column WAITER_EVENT for a30 select s1.inst_id, s1.sid blocking_sid, s1.serial# blocking_serial, s1.sql_id blocking_sql, s2.inst_id, s2.sid waiter_sid, s2.serial# waiter_serial, s2.sql_id waiter_sql, s2.event waiter_event from gv$session s1, gv$session s2 where s1.sid s2.blocking_session and s1.inst_id s2.blocking_instance; -- 查看表级锁 set linesize 260 column USERNAME for a20 column OBJECT_NAME for a40 select l.inst_id, l.oracle_username as username, l.session_id as sid, o.object_name, decode(l.locked_mode, 0, none, 1, null, 2, row-s (ss), 3, row-x (sx), 4, share, 5, s/row-x (ssx), 6, exclusive, unknown) as lock_type from gv$locked_object l, dba_objects o where l.object_id o.object_id; --长事务检查 select s.sid,s.serial#,s.username,t.start_time,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.ses_addrs.saddr order by t.start_time; ---- 4.3 物理 IO 与大表全表扫描 -- 物理 IO 统计 (基于数据文件) column INSTANCE_NAME for a20 column TABLESPACE_NAME for a20 column FILE_NAME for a50 select * from (select i.instance_name, df.tablespace_name, df.file_name, fs.phyrds, fs.phywrts from dba_data_files df join gv$filestat fs on df.file_id fs.file# join gv$instance i on fs.inst_id i.inst_id order by fs.phyrds desc) where rownum 20; -- 统计大表全表扫描比例 select c.instance_name, a.value large_table_scans, b.value small_table_scans, round(100*a.value/greatest((a.valueb.value),1),2) pct_large_scans from gv$sysstat a, gv$sysstat b, gv$instance c where a.nametable scans (long tables) and b.nametable scans (short tables) and a.inst_idc.inst_id and b.inst_idc.inst_id; --表统计信息过旧 select owner,table_name,last_analyzed,stale_stats from dba_tab_statistics where stale_statsYES and last_analyzed sysdate - 3 order by last_analyzed; --aio/io 性能 column FILETYPE_NAME for a30 select file_no,filetype_name,small_read_megabytes,small_write_megabytes,large_read_megabytes,large_write_megabytes from v$iostat_file order by small_read_megabytes desc; column NAME for a50 select d.name, i.asynch_io from v$datafile d, v$iostat_file i where d.file# i.file_no and i.filetype_name Data File; ---- 4.4 对象碎片、行迁移、失效对象 -- 检查行迁移与行链接超 10% 的表 select owner, table_name, num_rows, chain_cnt, round((chain_cnt/greatest(num_rows, 1))*100, 2) pct_chained, avg_row_len from dba_tables where chain_cnt 0 and num_rows 0 and (chain_cnt/greatest(num_rows, 1))*100 10 and owner not in (SYS,SYSTEM,SYSMAN,DBSNMP); -- 失效对象汇总 select owner, object_type, status, count(*) cnt from dba_objects where status ! VALID group by owner, object_type, status order by 4 desc; --recyclebin 使用情况 select owner,original_name,type,space/1024/1024 size_mb,droptime from dba_recyclebin order by space desc; -- 无法扩展的对象 (到达 MAXEXTENTS 或空间不足) column OWNER for a15 column SEGMENT_NAME for a30 column SEGMENT_TYPE for a20 column NEXT_EXTENT for 999,999,999,999 column MAX_EXTENTS for 999,999,999,999 select ds.owner, ds.tablespace_name, ds.segment_name, ds.segment_type, ds.next_extent, ds.extents, ds.max_extents from dba_segments ds left join (select tablespace_name, max(bytes) max_free from dba_free_space group by tablespace_name) dfs on ds.tablespace_name dfs.tablespace_name where (ds.next_extent nvl(dfs.max_free, 0) or ds.extents ds.max_extents) and ds.owner not in (SYS,SYSTEM); -- 大于 2GB 且行数大于 1000 万的未分区大表 select t.owner, t.table_name, round(s.bytes/1024/1024/1024,2) size_gb, t.num_rows from dba_tables t join dba_segments s on t.owners.owner and t.table_names.segment_name where s.segment_typeTABLE and s.bytes 2*1024*1024*1024 and t.num_rows 10000000 and not exists (select 1 from dba_tab_partitions p where p.table_ownert.owner and p.table_namet.table_name); --表碎片分析 column owner format a20 column table_name format a30 column waste_mb format 999999.99 select t.owner, t.table_name, round((t.blocks - t.avg_space) * p.block_size / 1024 / 1024, 2) as waste_mb from dba_tables t cross join (select value as block_size from v$parameter where name db_block_size) p where t.owner not in (sys, system) and t.avg_space 50 and t.blocks 1000 order by waste_mb desc; -- awr快照检查 set linesize 200 column begin_interval_time format a35 column end_interval_time format a35 select * from (select dbid, instance_number, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum 10; -- awr 保留策略 column SNAP_INTERVAL for a25 column RETENTION for a25 select snap_interval,retention from dba_hist_wr_control; -- -- 五、 账户安全、权限与审计 -- ---- 5.1 用户状态与非默认系统账号 -- 检查业务用户及过期情况 (排除自带系统用户) column USERNAME for a20 column EXPIRY_DATE for a25 column PROFILE for a30 select username, account_status, expiry_date, default_tablespace, profile from dba_users where username not in (SYS,SYSTEM,DBSNMP,SYSMAN,OUTLN,XDB,WMSYS,OJVMSYS,APPQOSSYS,AUDSYS) order by account_status, username; -- 检查拥有 DBA 或 SYSDBA 权限的账号 column GRANTEE for a20 column GRANTED_ROLE for a20 column ADMIN_OPTION for a20 column DEFAULT_ROLE select grantee, granted_role, admin_option, default_role from dba_role_privs where granted_role in (DBA,SYSDBA) order by grantee; -- SYSTEM 表空间中的非系统业务对象 select * from (select owner, segment_name, segment_type, bytes/1024/1024 mb, extents from dba_segments where tablespace_name in (SYSTEM,SYSAUX) and owner not in (SYS,SYSTEM,DBSNMP,SYSMAN,OUTLN,XDB,WMSYS,AUDSYS) order by bytes desc) where rownum 20; ---- 5.2 默认密码漏洞 -- 检测默认密码-10G select username, account_status from dba_users where password in (E066D214D5421CCC,24ABAB8B06281B4C,D4C5016086B2DC6A,D4DF7931AB130E37); --检测默认密码-11G column USERNAME for a30 column PRODUCT for a50 select * from dba_users_with_defpwd; ---- 5.3 审计空间与连接数占用 --审计参数 column NAME for a30 column VALUE for a50 select name,value from v$parameter where name like %audit%; --unified audit -12C column POLICY_NAME for a30 column ENTITY_NAME for a30 select policy_name,enabled_option,entity_name from audit_unified_enabled_policies; --profile 配置 column LIMIT for a30 select profile,resource_name,limit from dba_profiles order by profile,resource_name; -- 审计表占用的表空间大小 select t.owner, t.table_name, t.tablespace_name, round(ss.bytes/1024/1024,2) size_mb from dba_tables t join dba_segments ss on t.table_namess.segment_name and t.ownerss.owner where t.table_name in (AUD$,FGA_LOG$); -- 检查当前连接数及利用率 select a.inst_id, a.current_logins, b.max_processes, round(a.current_logins/b.max_processes*100,2) pct_used from (select inst_id, count(*) current_logins from gv$session group by inst_id) a join (select inst_id, to_number(value) max_processes from gv$parameter where nameprocesses) b on a.inst_id b.inst_id; --job检查 column JOB_NAME for a30 column NEXT_RUN_DATE for a35 column LAST_START_DATE for a35 select owner,job_name,enabled,state,last_start_date,next_run_date from dba_scheduler_jobs order by owner,job_name; --失败 job column OWNER for a15 column STATUS for a10 column ADDITIONAL_INFO for a40 column LOG_DATE for a30 select owner,job_name,status,error#,additional_info,log_date from dba_scheduler_job_run_details where statusSUCCEEDED order by log_date desc; --dba job 状态旧版本 column LOG_USER for a15 column WHAT for a40 select job,log_user,last_date,next_date,failures,broken,what from dba_jobs order by job; 六、 操作系统底层巡检命令集 (注根据服务器平台在系统终端执行相应的命令。已补充并完善近10年主流操作系统的巡检覆盖) 6.1 LINUX (含 RHEL, CentOS, Oracle Linux 6/7/8/9) # OS版本与内核信息 uname -a lsb_release -a # 或 cat /etc/os-release # CPU与内存资源 cat /proc/cpuinfo | grep processor | wc -l cat /proc/meminfo | grep -i memtotal free -m # 磁盘与文件系统 df -Th df -i fdisk -l # 查看裸盘分布 # 网络信息 ip addr netstat -nr # 或 ip route cat /etc/hosts # 性能监控 vmstat 1 5 top -b -n 1 | head -n 20 # Oracle 进程与集群状态 ps -ef | grep pmon crsctl status res -t 6.2 AIX (IBM) # OS版本 oslevel -s # 硬件架构 (CPU/MEM) prtconf | grep -i cpu prtconf | grep -i mem lsattr -El sys0 -a realmem # 磁盘与文件系统 df -g lspv lsps -a # Swap检查 # 网络信息 ifconfig -a netstat -rn cat /etc/hosts # 性能监控 vmstat 1 5 topas 6.3 HP-UX # OS版本及硬件信息 uname -a machinfo # 磁盘与文件系统 bdf swapinfo -tam # 网络信息 ifconfig -a netstat -rn cat /etc/hosts # 性能监控 vmstat 1 5 glance -f /tmp/glance.out -i 1 -j 5 6.5 全平台 Oracle 应用级命令 (Oracle Grid / Opatch) # 监听检查 lsnrctl status lsnrctl services # 补丁与 OPatch $ORACLE_HOME/OPatch/opatch lsinventory $GRID_HOME/OPatch/opatch lsinventory # 集群健康度 (GI 环境适用) crs_stat -t crsctl check crs ocrcheck asmcmd lsdg