Oracle 常用 SQL 大全——诊断、管理、优化、运维一次收藏

系列：Oracle 数据库全栈运维实战系列（VMware 版）· 日常运维篇
适用：Oracle 19c（绝大多数兼容 11g~23ai）
说明：按场景分类，全部可直接复制执行；标注权限要求

一、实例与数据库诊断

— 实例概览（DBA 权限）
SELECT instance_name, host_name, version, status, database_status,
TO_CHAR(startup_time,'YYYY-MM-DD HH24:MI') started, logins
FROM v$instance;

— 数据库信息
SELECT name, db_unique_name, open_mode, log_mode, platform_name,
TO_CHAR(created,'YYYY-MM-DD') created
FROM v$database;

— 参数（DBA）
SHOW PARAMETER;
SELECT name, value FROM v$parameter WHERE isdefault='FALSE' ORDER BY name;

— 内存结构
SELECT component, current_size/1024/1024 cur_mb, min_size/1024/1024 min_mb
FROM v$sga_dynamic_components WHERE current_size>0 ORDER BY 2 DESC;

二、会话与连接

— 会话总览（DBA）
SELECT username, status, machine, program, COUNT(*) cnt
FROM v$session GROUP BY username, status, machine, program ORDER BY cnt DESC;

— 当前正在执行 SQL 的会话（DBA）
SELECT s.sid, s.serial#, s.username, s.status,
ROUND(s.last_call_et,0) exec_sec, q.sql_id, SUBSTR(q.sql_text,1,100) sql_text
FROM v$session s JOIN v$sql q ON s.sql_id=q.sql_id
WHERE s.status='ACTIVE' AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

— 杀会话（DBA）
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

— 会话等待事件（DBA）
SELECT s.sid, s.event, s.seconds_in_wait, s.wait_class, s.state
FROM v$session s WHERE s.type='USER' AND s.status='ACTIVE';

三、锁与阻塞

— 锁汇总（DBA）
SELECT type, COUNT(*) cnt, SUM(DECODE(lmode,0,1,0)) waiting
FROM v$lock GROUP BY type;

— 阻塞链（DBA）
SELECT w.sid wait_sid, w.username wait_user, w.event, w.seconds_in_wait,
b.sid block_sid, b.username block_user, b.status
FROM v$session w
LEFT JOIN v$session b ON w.blocking_session=b.sid
WHERE w.blocking_session IS NOT NULL;

— 定位行锁对象（DBA）
SELECT l.sid, o.owner, o.object_name, o.object_type
FROM v$locked_object l JOIN dba_objects o ON l.object_id=o.object_id;

四、SQL 与执行计划

— Top 耗时 SQL（DBA，按累计）
SELECT sql_id, ROUND(elapsed_time/1e6) elapsed_sec, executions, ROUND(elapsed_time/DECODE(executions,0,1,executions)/1e3) avg_ms,
SUBSTR(sql_text,1,80) sql_text
FROM v$sqlarea
WHERE elapsed_time>0 AND executions>0 AND command_type IN (2,3,6,7)
ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;

— Top 物理读 SQL
SELECT sql_id, ROUND(disk_reads/1e6) disk_reads_m, SUBSTR(sql_text,1,80) sql_text
FROM v$sqlarea ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

— 查看执行计划（DBA）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',0,'ALLSTATS LAST'));

五、表空间与存储

— 使用率（所有表空间，DBA）
SELECT b.tablespace_name,
ROUND(b.bytes/1024/1024) total_mb,
ROUND((b.bytes–NVL(a.free,0))/1024/1024) used_mb,
ROUND((b.bytes–NVL(a.free,0))/b.bytes*100,1) used_pct,
ROUND(NVL(a.free,0)/1024/1024) free_mb
FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4 DESC;

— 表/索引大小 Top（DBA）
SELECT owner, segment_name, segment_type, ROUND(SUM(bytes)/1024/1024) mb
FROM dba_segments WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, segment_name, segment_type
ORDER BY 4 DESC FETCH FIRST 20 ROWS ONLY;

— 空间不足 Top（<5% 剩余）
SELECT tablespace_name, ROUND((bytes–free)/bytes*100,1) used_pct
FROM (
SELECT b.tablespace_name, b.bytes, NVL(a.free,0) free
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) b
LEFT JOIN (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a
USING (tablespace_name))
WHERE (bytes–free)/bytes > 0.95 ORDER BY 2 DESC;

六、UNDO / REDO / 归档

— UNDO 状态（DBA）
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) used_mb
FROM dba_undo_extents WHERE status='ACTIVE' GROUP BY tablespace_name;

— 日志组
SELECT group#, thread#, sequence#, bytes/1024/1024 mb, members, status FROM v$log;

— 归档量（近 24h）
SELECT TO_CHAR(first_time,'YYYY-MM-DD') day, COUNT(*) cnt, ROUND(SUM(blocks*block_size)/1024/1024) mb
FROM v$archived_log WHERE first_time > SYSDATE–7 GROUP BY TO_CHAR(first_time,'YYYY-MM-DD') ORDER BY 1;

— 归档满告警检查（ORA-00257 前置）
SELECT dest_id, destination, status, error FROM v$archive_dest WHERE status<>'INACTIVE';

七、对象与权限管理

— 查表结构
SELECT column_name, data_type, data_length, nullable
FROM dba_tab_columns WHERE owner='SCOTT' AND table_name='EMP' ORDER BY column_id;

— 索引清单
SELECT index_name, index_type, uniqueness, tablespace_name, status
FROM dba_indexes WHERE owner='SCOTT' AND table_name='EMP';

— 用户权限
SELECT grantee, privilege, admin_option FROM dba_sys_privs WHERE grantee='APP_USER';
SELECT grantee, owner, table_name, privilege FROM dba_tab_privs WHERE grantee='APP_USER';

— 授权与回收（DBA）
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON scott.emp TO app_user;
REVOKE DELETE ON scott.emp FROM app_user;

— 数据字典刷新统计（DBA）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');

八、性能与等待

— 系统等待 Top（DBA）
SELECT event, wait_class, total_waits, ROUND(time_waited_micro/1e6,1) wait_sec,
ROUND(time_waited_micro/DECODE(total_waits,0,1,total_waits)/1e3,2) avg_ms
FROM v$system_event WHERE wait_class<>'Idle' AND total_waits>0
ORDER BY time_waited_micro DESC FETCH FIRST 15 ROWS ONLY;

— 硬解析率
SELECT ROUND(hard/(total+1e–9)*100,2) hard_parse_pct FROM (
SELECT SUM(CASE WHEN name='parse count (hard)' THEN value END) hard,
SUM(CASE WHEN name='parse count (total)' THEN value END) total
FROM v$sysstat WHERE name IN ('parse count (hard)','parse count (total)'));

九、运维例行

— 日志切换频率（每小时）
SELECT TO_CHAR(first_time,'HH24') hr, COUNT(*) switches
FROM v$log_history WHERE first_time > SYSDATE–1
GROUP BY TO_CHAR(first_time,'HH24') ORDER BY 1;

— 最近 10 条 ORA 错误（19c）
SELECT TO_CHAR(origin_timestamp,'MM-DD HH24:MI') tm, SUBSTR(message_text,1,90) msg
FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-%'
ORDER BY origin_timestamp DESC FETCH FIRST 10 ROWS ONLY;

— AWR 快照
SELECT snap_id, TO_CHAR(begin_interval_time,'MM-DD HH24:MI') begin_tm
FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;

十、常用 SQL 场景速查表

本文章属于《Oracle 数据库全栈运维实战系列》日常运维篇。欢迎收藏、交流。