🍦 0. Oracle数据库日常巡检脚本

-- ====================================================
-- Oracle 11g 数据库日常巡检脚本
-- 说明：执行前请确保有足够的权限（DBA 或 SELECT ANY DICTIONARY）
-- ====================================================

set pages 200 lines 200
col event for a40
col sql_text for a60
col username for a20
col tablespace_name for a20
col file_name for a50

1. 数据库基本信息

select name, open_mode, log_mode, platform_name, version_full 
from v$database;

2. 实例状态与启动时间

select instance_name, status, startup_time, host_name 
from v$instance;

3. 当前会话数（活跃/总）

select 
  (select count(*) from v$session) as total_sessions,
  (select count(*) from v$session where status='ACTIVE' and type!='BACKGROUND') as active_sessions
from dual;

4. 表空间使用率（Top 5）

select * from (
  select tablespace_name, 
         round(used_space*100/tablespace_size,2) used_pct,
         round(tablespace_size/1024/1024,2) size_mb,
         round(used_space/1024/1024,2) used_mb
  from dba_tablespace_usage_metrics
  order by used_pct desc
) where rownum <= 5;

5. 等待事件（Top 5，非空闲）

select event, total_waits, round(time_waited_micro/1000000,2) wait_sec
from (
  select event, total_waits, time_waited_micro
  from v$system_event
  where wait_class != 'Idle'
  order by time_waited_micro desc
) where rownum <= 5;

6. 当前阻塞会话（锁等待）

select blocking_session, sid, username, event, seconds_in_wait
from v$session
where blocking_session is not null;

7. Top 5 CPU 消耗SQL（v$sql）

select sql_id, substr(sql_text,1,60) sql_text, 
       round(cpu_time/1000000,2) cpu_sec, executions
from (
  select sql_id, sql_text, cpu_time, executions
  from v$sql
  where cpu_time > 1000000
  order by cpu_time desc
) where rownum <= 5;

8. 行迁移/链接统计（累计）

select name, value 
from v$sysstat 
where name = 'table fetch continued row';

9. 索引碎片（最近分析的索引，删除率>20%）

-- 需先对索引执行 ANALYZE INDEX ... VALIDATE STRUCTURE，否则无数据
select name, height, lf_rows, lf_blks, del_lf_rows,
       round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,2) del_pct
from index_stats
where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,2) > 20
  and rownum <= 10;

10. 失效索引

select owner, index_name, table_name, status
from dba_indexes
where status = 'UNUSABLE';

11. 最近7天RMAN备份记录

select session_key, input_type, status, start_time, 
       round(elapsed_seconds/60,2) duration_min
from v$rman_backup_job_details
where start_time > sysdate - 7
order by start_time desc;

12. 未备份的归档日志数量

select count(*) unbacked_archivelogs
from v$archived_log
where backup_count = 0;

13. 拥有DBA角色的用户

select grantee, granted_role, admin_option, default_role
 from dba_role_privs
 where granted_role = 'DBA';

14. 拥有SYSDBA权限的用户（密码文件）

select username, sysdba, sysoper
 from v$pwfile_users
 where sysdba = 'TRUE';

15. 检查最近的alert日志错误

-- 需要设置合适的日志路径，此处仅示例
-- 可通过 ADRCI 或直接查看文件
select value from v$diag_info where name = 'Diag Trace';