本文整理了 Oracle DBA 日常巡检中最常用的 74 个 SQL 脚本，覆盖环境配置、表空间、ASM、归档日志、索引、锁管理、性能诊断、ADG 备库等 13 大场景。开箱即用，一键收藏！
📋 目录导航
| 类别 | 脚本数 | 适用场景 |
|---|---|---|
| 一、基础环境配置类 | 5 | 会话美化、环境配置 |
| 二、表空间管理类 | 5 | 容量巡检、扩容收缩 |
| 三、ASM 磁盘管理类 | 3 | ASM 磁盘组监控 |
| 四、归档日志管理类 | 5 | 归档清理、增量统计 |
| 五、索引管理类 | 5 | 索引查看、状态检查 |
| 六、约束查询类 | 1 | 约束信息查询 |
| 七、分区表管理类 | 3 | 分区信息查看 |
| 八、会话与锁管理类 | 6 | 会话监控、锁分析 |
| 九、性能诊断优化类 | 24 | Top SQL、执行计划、AWR |
| 十、ADG 备库运维类 | 7 | ADG 同步状态管理 |
| 十一、RMAN 备份归档类 | 4 | 备份策略执行 |
| 十二、事务与参数查询类 | 2 | 参数、事务查看 |
| 十三、元数据查询类 | 4 | 表结构、对象查询 |
一、基础环境配置类
1.1 设置终端超时
功能：禁用终端超时，防止会话断开
适用：Linux 环境 Oracle
export TMOUT=0
1.2 登录数据库并美化提示符
功能：以 sysdba 身份登录，查询服务名，设置美化提示符
适用：Oracle 日常维护
sqlplus "/ as sysdba"
-- 美化提示符
set sqlprompt _user'@'_connect_identifier>
set serveroutput on
-- 查看服务名
show parameter service_name
1.3 设置时间格式
功能：统一会话时间显示格式为
yyyy-mm-dd hh24:mi:ss
适用：Oracle 日常维护
ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
1.4 设置中文编码
功能：解决 Oracle 客户端中文乱码问题
适用：Linux 环境 Oracle 中文环境
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
1.5 查询并生成编码配置
功能：自动提取数据库字符集，生成 NLS 配置命令
适用：Oracle 字符集检查
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" <<!
col export_command for a60
SELECT 'export NLS_LANG='||
REPLACE(WM_CONCAT(text),',','') AS export_command
FROM (
SELECT CASE WHEN PARAMETER='NLS_LANGUAGE' THEN VALUE||'_' END||
CASE WHEN PARAMETER='NLS_TERRITORY' THEN VALUE||'.' END||
CASE WHEN PARAMETER='NLS_CHARACTERSET' THEN VALUE END text
FROM nls_database_parameters
WHERE PARAMETER IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET')
);
SELECT '中文字符集测试通过，执行EXPORT_COMMAND! ' AS NLS_TEST FROM dual;
EXIT;
!
二、表空间管理类
2.1 查询所有表空间（含临时表空间）
功能：一键巡检所有表空间容量、使用率，按使用率排序
适用：Oracle 日常巡检
SET LINESIZE 300
COL PCT_USED FOR A15
COL PCT_FREE FOR A15
COL tablespace_name FOR A30
SELECT a.tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024) Alloc_Mbytes,
ROUND(NVL(b.bytes_free, 0) / 1024 / 1024) Free_Mbytes,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024) Used_Mbytes,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) || '%' Pct_Free,
100 - ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) || '%' Pct_Used,
ROUND(maxbytes / 1048576) Max_Mbytes
FROM (SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY f.tablespace_name) a,
(SELECT f.tablespace_name, SUM(f.bytes) bytes_free
FROM dba_free_space f
GROUP BY f.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576) Alloc_Mbytes,
ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576) Free_Mbytes,
ROUND(SUM(NVL(p.bytes_used, 0)) / 1048576) Used_Mbytes,
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) /
SUM(h.bytes_used + h.bytes_free)) * 100) || '%' Pct_Free,
100 - ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) /
SUM(h.bytes_used + h.bytes_free)) * 100) || '%' Pct_Used,
ROUND(SUM(f.maxbytes) / 1048576) Max_bytes
FROM sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY PCT_USED;
2.2 查询指定表空间
功能：精准查询指定表空间的容量及空闲空间
适用：按需检索特定表空间
SET LINESIZE 300
COL PCT_USED FOR A15
COL PCT_FREE FOR A15
COL tablespace_name FOR A30
SELECT a.tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024) Alloc_Mbytes,
ROUND(NVL(b.bytes_free, 0) / 1024 / 1024) Free_Mbytes,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024) Used_Mbytes,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) || '%' Pct_Free,
100 - ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) || '%' Pct_Used,
ROUND(maxbytes / 1048576) Max_Mbytes
FROM (SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY f.tablespace_name) a,
(SELECT f.tablespace_name, SUM(f.bytes) bytes_free
FROM dba_free_space f
GROUP BY f.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name LIKE UPPER('%&tablespace_name%')
UNION ALL
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576) Alloc_Mbytes,
ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576) Free_Mbytes,
ROUND(SUM(NVL(p.bytes_used, 0)) / 1048576) Used_Mbytes,
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) /
SUM(h.bytes_used + h.bytes_free)) * 100) || '%' Pct_Free,
100 - ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) /
SUM(h.bytes_used + h.bytes_free)) * 100) || '%' Pct_Used,
ROUND(SUM(f.maxbytes) / 1048576) Max_bytes
FROM sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
提示：运行时输入表空间名称即可
2.3 生成表空间扩容 SQL
功能：自动生成符合规范的表空间扩容语句
适用：表空间扩容操作
SET SERVEROUTPUT ON
DECLARE
tbs_name VARCHAR2(200) := '&input_tablespace_name';
v_result VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => NULL);
DBMS_OUTPUT.PUT_LINE('-- list tablespace ' || UPPER(tbs_name) || ' datafiles');
FOR rec IN (SELECT file_id, file_name,
CASE WHEN bytes / 1024 / 1024 / 1024 >= 1
THEN ROUND(bytes / 1024 / 1024 / 1024, 1) || 'GB'
ELSE ROUND(bytes / 1024 / 1024, 1) || 'MB' END AS bytes
FROM (SELECT file_id, file_name, bytes FROM dba_data_files
WHERE tablespace_name = UPPER(tbs_name)
UNION ALL
SELECT file_id, file_name, bytes FROM dba_temp_files
WHERE tablespace_name = UPPER(tbs_name)
ORDER BY file_id)) LOOP
v_result := rec.file_id || ' ' || rec.file_name || ' ' || rec.bytes;
DBMS_OUTPUT.PUT_LINE(v_result);
END LOOP;
FOR rec IN (SELECT 'ALTER TABLESPACE ' || LOWER(tbs_name) || ' ADD ' ||
CASE WHEN typ = 'ntmp_file' THEN 'DATAFILE ' ELSE 'TEMPFILE ' END ||
'''' || file_name || ''' SIZE ' ||
CASE WHEN bytes / 1024 / 1024 / 1024 > 20 THEN '30G'
WHEN bytes / 1024 / 1024 / 1024 BETWEEN 10 AND 20 THEN '20G'
WHEN bytes / 1024 / 1024 / 1024 >= 1 AND bytes / 1024 / 1024 / 1024 < 10 THEN '10G'
ELSE '2G' END || ';' AS sql_text
FROM (SELECT 'ntmp_file' AS typ, file_name, bytes FROM dba_data_files
WHERE tablespace_name = UPPER(tbs_name)
AND file_id = (SELECT MAX(file_id) FROM dba_data_files
WHERE tablespace_name = UPPER(tbs_name))
UNION ALL
SELECT 'tmp_file' AS typ, file_name, bytes FROM dba_temp_files
WHERE tablespace_name = UPPER(tbs_name)
AND file_id = (SELECT MAX(file_id) FROM dba_temp_files
WHERE tablespace_name = UPPER(tbs_name)))) LOOP
v_result := rec.sql_text;
DBMS_OUTPUT.PUT_LINE('-- add datafile sql text');
DBMS_OUTPUT.PUT_LINE(v_result);
END LOOP;
END;
/
2.4 生成表空间收缩 SQL
功能：分析闲置空间，自动生成文件收缩命令
适用：释放无用空间
SET LINESIZE 400
SET PAGESIZE 200
COL tablespace_name FOR A20
COL cmd FOR A100
SELECT ttt1.tablespace_name,
ttt1.currsize_mb,
ttt2.FREE_MB AS tbs_total_free,
ttt1.savings_mb,
ttt1.cmd
FROM (SELECT *
FROM (SELECT a.tablespace_name,
CEIL(blocks * blksize / 1024 / 1024) currsize_mb,
CEIL(blocks * blksize / 1024 / 1024) -
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) savings_mb,
'ALTER DATABASE DATAFILE ' || a.file_id ||
' RESIZE ' ||
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) || 'M;' cmd
FROM (SELECT * FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tablespace_name') a,
(SELECT file_id, MAX(block_id + blocks - 1) hwm
FROM DBA_EXTENTS GROUP BY file_id) b,
(SELECT TO_NUMBER(value) blksize
FROM V$PARAMETER WHERE name = 'db_block_size')
WHERE a.file_id = b.file_id(+)
AND CEIL(blocks * blksize / 1024 / 1024) -
CEIL((NVL(hwm, 1) * blksize) / 1024 / 1024) > 0)
WHERE savings_mb > 0) ttt1
LEFT JOIN (SELECT tablespace_name,
ROUND(SUM(bytes / 1024 / 1024)) AS FREE_MB
FROM dba_free_space
WHERE tablespace_name = '&tablespace_name'
GROUP BY tablespace_name) ttt2
ON ttt1.tablespace_name = ttt2.tablespace_name
ORDER BY ttt1.tablespace_name;
2.5 监控表空间增长趋势
功能：监控表空间每日使用量，计算增长增量，预判扩容需求
适用：容量规划
WITH tmp AS (
SELECT rtime,
SUM(tablespace_usedsize_kb) tablespace_usedsize_kb,
SUM(tablespace_size_kb) tablespace_size_kb
FROM (SELECT rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024/1024/1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024/1024/1024 tablespace_size_kb
FROM dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
WHERE e.tablespace_id = g.TS#
AND f.tablespace_name = g.NAME
AND f.contents NOT IN ('TEMPORARY','UNDO'))
GROUP BY rtime)
SELECT tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_GB
FROM tmp,
(SELECT MAX(rtime) rtime FROM tmp GROUP BY SUBSTR(rtime, 1, 10)) t2
WHERE t2.rtime = tmp.rtime;
三、ASM 磁盘管理类
3.1 查询 ASM 磁盘组
功能：查看磁盘组总容量、空闲、使用率及状态
适用：Oracle ASM 环境
SET LINESIZE 200
COL name FOR A20
SELECT name,
total_mb,
free_mb,
TRUNC((total_mb - free_mb) / total_mb, 2) * 100 || '%' AS used
FROM v$asm_diskgroup;
3.2 查询 ASM 磁盘详情
功能：查看每块磁盘的路径、名称、容量信息
适用：定位单块磁盘异常
SET PAGESIZE 200
COL path FOR A30
COL name FOR A15
SELECT TOTAL_MB, FREE_MB, path, name
FROM v$asm_disk;
3.3 查询 ASM 使用率（格式化）
功能：格式化展示磁盘组容量，自动单位换算
适用：容量巡检报告
SET LINESIZE 300
COL name FOR A8
COL total_size FOR A15
COL free_size FOR A15
COL used_size FOR A15
COL used_pct FOR A15
COL state FOR A20
SELECT group_number, name, state, type,
CASE WHEN total_mb >= 1024
THEN total_mb / 1024 || 'G'
ELSE total_mb || 'M' END AS total_size,
CASE WHEN free_mb >= 1024
THEN ROUND((free_mb / 1024), 2) || 'G'
ELSE free_mb || 'M' END AS free_size,
CASE WHEN (total_mb - free_mb) >= 1024
THEN ROUND(((total_mb - free_mb) / 1024), 2) || 'G'
ELSE total_mb - free_mb || 'M' END AS used_size,
CASE WHEN total_mb = 0
THEN '0%'
ELSE ROUND(((total_mb - free_mb) / total_mb * 100), 2) || '%' END AS used_pct
FROM v$asm_diskgroup
ORDER BY name;
四、归档日志管理类
4.1 删除 2 天前归档日志
功能：RMAN 批量清理旧归档，一键执行
适用：归档空间清理
DELETE NOPROMPT ARCHIVELOG UNTIL TIME "SYSDATE-2" INCARNATION CURRENT;
⚠️ 注意：执行前请确认归档已备份
4.2 查询归档日增量
功能：统计每日归档生成量（GB），定位归档突增日期
适用：归档量分析
ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:mi';
SELECT TRUNC(COMPLETION_TIME) AS day,
SUM(Mb) / 1024 AS day_gb
FROM (SELECT name, COMPLETION_TIME, BLOCKS * BLOCK_SIZE / 1024 / 1024 Mb
FROM v$archived_log
WHERE COMPLETION_TIME BETWEEN TRUNC(SYSDATE) - &end_day AND SYSDATE
AND DEST_ID = &archive_dest_n)
GROUP BY TRUNC(COMPLETION_TIME)
ORDER BY TRUNC(COMPLETION_TIME);
提示：输入
&end_day（天数）和&archive_dest_n（归档目标号）
4.3 查询归档小时增量
功能：按小时统计归档量，精准定位归档峰值时段
适用：小时级归档分析
ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:mi';
SET ECHO OFF
SET FEEDBACK OFF
SELECT logtime,
COUNT(*) log_cnt,
ROUND(SUM(blocks * block_size) / 1024 / 1024) mbsize
FROM (SELECT a.THREAD#,
TRUNC(first_time, 'hh') AS logtime,
a.BLOCKS, a.BLOCK_SIZE
FROM v$archived_log a
WHERE a.DEST_ID = &archive_deat_n
AND a.FIRST_TIME BETWEEN TO_DATE('&start_time','yyyy-mm-dd HH24:mi')
AND TO_DATE('&end_time','yyyy-mm-dd HH24:mi'))
GROUP BY logtime
ORDER BY logtime DESC;
4.4 查询 RAC 归档线程增量
功能：RAC 环境按线程统计归档量，区分节点归档异常
适用：Oracle RAC
ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:mi';
SELECT logtime,
thread#,
COUNT(*) log_cnt,
ROUND(SUM(blocks * block_size) / 1024 / 1024) mbsize
FROM (SELECT a.THREAD#,
TRUNC(first_time, 'hh') AS logtime,
a.BLOCKS, a.BLOCK_SIZE
FROM v$archived_log a
WHERE a.DEST_ID = &archive_desc_n
AND a.FIRST_TIME BETWEEN TO_DATE('&start_time','yyyy-mm-dd HH24:mi')
AND TO_DATE('&end_time','yyyy-mm-dd HH24:mi'))
GROUP BY logtime, thread#
ORDER BY thread# ASC, logtime DESC;
4.5 查询近 3 天每小时 RAC 归档
功能：免参巡检，近 3 天每小时各节点归档量
适用：RAC 归档巡检
SELECT thread# AS rac_thread,
TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour_period,
COUNT(*) AS archive_count,
ROUND(SUM(blocks * block_size) / 1024 / 1024, 2) AS total_size_mb
FROM v$archived_log
WHERE first_time >= SYSDATE - 3
AND archived = 'YES'
GROUP BY thread#, TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour_period, thread# DESC;
五、索引管理类
5.1 查询表索引列表
功能：查看指定表的所有索引、列、状态及并行度
适用：索引信息查看
COL degree FOR A2
SET LINE 200
COL COLUMN_NAME FOR A30
COL index_owner FOR A20
COL index_name FOR A32
COL table_name FOR A32
SELECT a.INDEX_OWNER, a.INDEX_NAME, a.TABLE_NAME, a.COLUMN_NAME,
b.STATUS, b.degree
FROM dba_ind_columns a, dba_indexes b
WHERE a.table_name = UPPER('&table_name')
AND a.index_name = b.index_name;
5.2 查询索引段大小
功能：统计表的索引占用空间，按用户排序
适用：定位大索引
COL owner FOR A20
COL table_name FOR A32
COL index_name FOR A32
SELECT a.owner, b.table_name, b.index_name, a.tablespace_name,
ROUND(SUM(a.bytes) / 1024 / 1024, 2) bytes_mb
FROM dba_segments a, dba_indexes b
WHERE b.table_name = UPPER('&table_name')
AND a.segment_name = b.index_name
GROUP BY a.owner, b.table_name, b.index_name, a.tablespace_name
ORDER BY a.owner;
5.3 查询索引列详情
功能：完整展示索引列属性（类型、非空、位置）
适用：索引结构分析
SET LINESIZE 300
SET PAGESIZE 200
COL TABLE_NAME FOR A25
COL INDEX_OWNER FOR A20
COL INDEX_NAME FOR A30
COL COLUMN_NAME FOR A25
COL COLUMN_POSITION FOR 99
COL col FOR A25
SELECT t.table_name,
i.INDEX_OWNER,
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
DECODE(t.DATA_TYPE,
'NUMBER', t.DATA_TYPE || '(' ||
DECODE(t.DATA_PRECISION, NULL, t.DATA_LENGTH || ')',
t.DATA_PRECISION || ',' || t.DATA_SCALE || ')'),
'DATE', t.DATA_TYPE,
t.DATA_TYPE || '(' || t.DATA_LENGTH || ')') || ' ' ||
DECODE(t.nullable, 'N', 'NOT NULL', NULL) col
FROM dba_ind_columns i, dba_tab_columns t
WHERE i.table_name = UPPER('&table_name')
AND i.owner = UPPER('&table_owner')
AND i.table_name = t.table_name
AND i.column_name = t.column_name
ORDER BY table_name, index_name, column_position;
5.4 查询索引状态
功能：检查索引状态、分区类型，识别失效索引
适用：索引健康检查
SET SERVEROUTPUT ON
SET LINESIZE 100
DECLARE
v_num NUMBER := 0;
v_owner VARCHAR2(30);
v_tabname VARCHAR2(30);
BEGIN
v_owner := UPPER('&table_owner');
v_tabname := UPPER('&table_name');
SELECT SUM(SUBPARTITION_COUNT) INTO v_num
FROM dba_tab_partitions
WHERE table_owner = v_owner AND table_name = v_tabname;
IF v_num != 0 THEN
FOR c IN (
SELECT DISTINCT a.table_owner, a.table_name, a.owner, a.index_name,
NVL(b.status, a.status) AS status,
NVL(c.LOCALITY, '**global**') AS index_mold
FROM dba_indexes a
LEFT JOIN dba_ind_subpartitions b ON a.index_name = b.index_name AND a.OWNER = b.index_owner
LEFT JOIN dba_part_indexes c ON c.OWNER = b.INDEX_OWNER AND c.INDEX_NAME = b.INDEX_NAME
WHERE a.table_owner = v_owner AND a.table_name = v_tabname
) LOOP
DBMS_OUTPUT.PUT_LINE(c.table_owner || ' ' || c.table_name || ' ' ||
c.owner || ' ' || c.index_name || ' ' ||
c.status || ' ' || c.index_mold);
END LOOP;
ELSE
FOR c IN (
SELECT DISTINCT a.table_owner, a.table_name, a.owner, a.index_name,
NVL(b.status, a.status) AS status,
NVL(c.LOCALITY, '**global**') AS index_mold
FROM dba_indexes a
LEFT JOIN dba_ind_partitions b ON a.index_name = b.index_name AND a.OWNER = b.index_owner
LEFT JOIN dba_part_indexes c ON c.OWNER = b.INDEX_OWNER AND c.INDEX_NAME = b.INDEX_NAME
WHERE a.table_owner = v_owner AND a.table_name = v_tabname
) LOOP
DBMS_OUTPUT.PUT_LINE(c.table_owner || ' ' || c.table_name || ' ' ||
c.owner || ' ' || c.index_name || ' ' ||
c.status || ' ' || c.index_mold);
END LOOP;
END IF;
END;
/
5.5 生成索引重建 SQL
功能：自动生成索引重建语句，含分区、并行参数
适用：索引重建
SET LINESIZE 200
SET PAGESIZE 50
SELECT 'CREATE ' ||
CASE WHEN b.UNIQUENESS = 'UNIQUE' THEN 'UNIQUE ' ELSE '' END ||
'INDEX ' || a.index_owner || '.' || a.index_name ||
' ON ' || a.table_owner || '.' || a.table_name ||
' (' || a.POSITION || ') TABLESPACE ' || b.tablespace_name || ' ' ||
b.LOCALITY || ' PARALLEL 12 ONLINE;' || CHR(10) ||
'ALTER INDEX ' || a.index_owner || '.' || a.index_name || ' NOPARALLEL;' AS rebuild_sql
FROM (SELECT i.table_owner, i.table_name, i.index_owner, i.INDEX_NAME,
LISTAGG(i.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY i.COLUMN_POSITION) AS POSITION
FROM dba_ind_columns i, dba_tab_columns t
WHERE t.owner = UPPER('&table_owner')
AND i.table_name = UPPER('&table_name')
AND i.table_name = t.table_name
AND i.column_name = t.column_name
AND i.table_owner = t.owner
GROUP BY i.table_owner, i.table_name, i.index_owner, i.INDEX_NAME) a,
(SELECT DISTINCT b.table_owner, b.table_name, b.owner, b.index_name,
b.UNIQUENESS, c.LOCALITY,
NVL(c.DEF_TABLESPACE_NAME, b.TABLESPACE_NAME) AS tablespace_name
FROM dba_indexes b
LEFT JOIN dba_part_indexes c ON b.index_name = c.index_name AND b.OWNER = c.OWNER
WHERE b.table_owner = UPPER('&table_owner')
AND b.table_name = UPPER('&table_name')) b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.table_name = b.TABLE_NAME;
六、约束查询类
6.1 查询表约束
功能：查看表的主键、外键、唯一键等约束信息
适用：约束分析
SET LINESIZE 200
SET PAGESIZE 100
COL owner FOR A20
COL constraint_name FOR A30
COL CONSTRAINT_TYPE FOR A5
COL table_name FOR A30
COL column_name FOR A30
SELECT a.owner, a.constraint_name, b.CONSTRAINT_TYPE,
a.table_name, a.column_name
FROM dba_cons_columns a, dba_constraints b
WHERE a.owner = UPPER('&owner')
AND a.table_name = UPPER('&table_name')
AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;
七、分区表管理类
7.1 查询分区表详情
功能：查看分区名称、位置及分区键高值
适用：分区信息查看
SET LINESIZE 500
SET PAGESIZE 200
COL table_owner FOR A10
COL table_name FOR A28
COL PARTITION_NAME FOR A35
COL PARTITION_POSITION FOR 9999
SELECT table_owner, table_name, PARTITION_NAME, PARTITION_POSITION, high_value
FROM dba_tab_partitions
WHERE TABLE_OWNER = UPPER('&table_owner')
AND TABLE_NAME = UPPER('&table_name');
7.2 查询分区表键
功能：查看分区键类型、数量及子分区信息
适用：分区规则分析
SET LINESIZE 200
COL table_name FOR A25
COL MASTER_PART FOR A10
COL partitioning_type FOR A10
COL part_col FOR A20
COL ifsubpart FOR A10
COL subpart_col FOR A20
SELECT DISTINCT p.table_name,
DECODE(p.partitioning_key_count, 1, 'one_master_key', 'many_master_keys') AS master_key_nums,
p.partitioning_type,
p.column_name AS part_col,
DECODE(NVL(q.subpartitioning_key_count, 0), 0, 'nosubpart', 1, 'subpart') AS ifsubpart,
q.subpartitioning_type,
q.column_name AS subpart_col
FROM (SELECT a.table_name, a.partitioning_type, b.column_name, a.partitioning_key_count
FROM dba_part_tables a, dba_part_key_columns b
WHERE a.table_name = b.NAME AND b.object_type = 'TABLE'
AND a.owner = UPPER('&table_owner')
AND a.table_name = UPPER('&table_name')) p,
(SELECT a.table_name, a.subpartitioning_type, b.column_name, a.subpartitioning_key_count
FROM dba_part_tables a, dba_subpart_key_columns b
WHERE a.table_name = b.NAME AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE'
AND a.owner = UPPER('&table_owner')
AND a.table_name = UPPER('&table_name')) q
WHERE p.table_name = q.table_name(+)
ORDER BY 5, 4, 1;
7.3 查询分区信息（极简版）
功能：快速查看分区基本信息
适用：分区快速查询
SET LINESIZE 500
SET PAGESIZE 200
COL table_owner FOR A10
COL table_name FOR A28
COL PARTITION_NAME FOR A12
COL PARTITION_POSITION FOR 999
SELECT table_owner, table_name, PARTITION_NAME, PARTITION_POSITION, high_value
FROM dba_tab_partitions
WHERE TABLE_OWNER = UPPER('&table_owner')
AND TABLE_NAME = UPPER('&table_name');
八、会话与锁管理类
8.1 查询表关联会话
功能：定位与指定表相关的活跃会话及等待事件
适用：定位慢会话
SELECT event, sql_id
FROM gv$session
WHERE sql_id IN (
SELECT sql_id
FROM gv$sql
WHERE UPPER(sql_text) LIKE '%&T_QRC_ORDER%'
)
AND wait_class <> 'Idle';
8.2 生成杀会话 SQL
功能：根据 SQL_ID 拼接 kill 会话语句
适用：终止异常会话
SELECT q'[ALTER SYSTEM KILL SESSION ']' || S.SID || ',' || S.SERIAL# ||
q'[ IMMEDIATE;]' AS sql_text
FROM V$SESSION S
WHERE S.sql_id = '&sql_id'
AND S.STATUS = 'ACTIVE';
8.3 查询会话锁
功能：查看锁持有、请求情况，定位阻塞会话
适用：锁分析
SET LINESIZE 200;
COL sql_id FORMAT A15
COL sid FORMAT 999999
COL serial# FORMAT 999999
COL username FORMAT A15
COL object_owner FORMAT A15
COL object_name FORMAT A25
COL locked_mode FORMAT A15
COL os_user_name FORMAT A15
SELECT s.inst_id, s.sql_id,
lo.session_id AS sid, s.serial#,
s.BLOCKING_SESSION,
NVL(lo.oracle_username, '(oracle)') AS username,
o.owner AS object_owner,
lo.object_id, o.object_name,
DECODE(lo.locked_mode,
0, 'None', 1, 'Null (NULL)', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share (S)',
5, 'S/Row-X (SSX)', 6, 'Exclusive (X)',
lo.locked_mode) locked_mode,
lo.os_user_name
FROM gv$locked_object lo, dba_objects o, gv$session s
WHERE lo.object_id = o.object_id
AND lo.session_id = s.sid
AND lo.inst_id = s.inst_id
ORDER BY 1, 3, 8;
8.4 查询锁类型
功能：分析锁类型、持有/请求状态，识别阻塞源
适用：锁类型分析
SET LINESIZE 120
COL type FORMAT A20
COL hold FORMAT A12
COL request FORMAT A12
COL BLOCK_OTHERS FORMAT A16
ALTER SESSION SET cursor_sharing = force;
SELECT /*+ RULE */
sid,
DECODE(type,
'MR', 'Media Recovery', 'RT', 'Redo Thread',
'UN', 'User Name', 'TX', 'Transaction',
'TM', 'DML', 'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction', 'CF', 'Control File',
'IS', 'Instance State', 'FS', 'File Set',
'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction',
'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch', 'RW', 'Row Wait',
'SQ', 'Sequence Number', 'TE', 'Extend Table',
'TT', 'Temp Table', 'TC', 'Thread Checkpoint',
'SS', 'Sort Segment', 'JQ', 'Job Queue',
'PI', 'Parallel operation', 'PS', 'Parallel operation',
'DL', 'Direct Index Creation', type) type,
DECODE(lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)) hold,
DECODE(request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(request)) request,
ID1, ID2, CTIME,
DECODE(block, 0, 'Not Blocking', 1, 'Blocking',
2, 'Global', TO_CHAR(block)) block_others
FROM v$lock
WHERE type <> 'MR';
8.5 查询锁等待链
功能：定位锁等待链，找出阻塞源头及被阻塞会话
适用：锁等待分析
SET LINESIZE 180
COL HOLD_SID FORMAT 99999
COL WAIT_SID FORMAT 99999
COL type FORMAT A20
COL hold FORMAT A12
COL request FORMAT A12
ALTER SESSION SET cursor_sharing = force;
SELECT /*+ ORDERED USE_HASH(H,R) */
H.SID HOLD_SID,
R.SID WAIT_SID,
DECODE(H.type, 'MR', 'Media Recovery', 'RT', 'Redo Thread',
'UN', 'User Name', 'TX', 'Transaction', 'TM', 'DML',
'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction',
'CF', 'Control File', 'IS', 'Instance State',
'FS', 'File Set', 'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction', 'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch',
'RW', 'Row Wait', 'SQ', 'Sequence Number',
'TE', 'Extend Table', 'TT', 'Temp Table',
'TC', 'Thread Checkpoint', 'SS', 'Sort Segment',
'JQ', 'Job Queue', 'PI', 'Parallel operation',
'PS', 'Parallel operation', 'DL', 'Direct Index Creation',
H.type) type,
DECODE(H.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(H.lmode)) hold,
DECODE(R.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(R.request)) request,
R.ID1, R.ID2, R.CTIME
FROM V$LOCK H, V$LOCK R
WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID
AND H.TYPE <> 'MR' AND R.TYPE <> 'MR'
AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE = R.TYPE
AND H.LMODE > 0 AND R.REQUEST > 0
ORDER BY 1, 2;
8.6 查询等待事件
功能：分析等待事件分布及活跃会话非空闲等待
适用：性能瓶颈溯源
SET LINESIZE 200
SET PAGESIZE 300
COL SESS FOR A12
COL p1 FOR 999999999999
COL p2 FOR 99999999999999
COL p3 FOR 999999999999
COL hash FOR 99999999999
COL machine FOR A14 TRUNCATED
COL STATE FOR A10 TRUNCATED
COL event FORMAT A26 TRUNCATE HEADING "Event Name"
COL total_waits FORMAT 999,999,990.00 HEADING "Total|Waits|(in 1000s)"
COL time_waited FORMAT 999,999,990.00 HEADING "Time|Waited|(in Hours)"
COL pct_significant FORMAT 90.00 HEADING "% of|Concern"
COL average_wait FORMAT 9999999990.00 HEADING "Avg|Wait|(Secs)"
COL seconds_in_wait FOR 9999999 HEADING "Wait|Time(s)"
COL wait_class FOR A15
COL final_blocking_session_status HEADING "Final|Blocking|Session_Sts"
COL final_blocking_instance HEADING "Final|Blocking|Instance"
COL final_blocking_session HEADING "Final|Blocking|Session"
WITH total_wait_time AS (
SELECT /*+ RULE */ SUM(time_waited) total_time_waited
FROM sys.v$system_event
WHERE event NOT IN ('SQL*Net message from client', 'rdbms ipc message',
'slave wait', 'pmon timer', 'smon timer',
'rdbms ipc reply', 'SQL*Net message to client',
'SQL*Net break/reset to client', 'inactive session',
'Null event')
)
SELECT * FROM (
SELECT event,
(total_waits / 1000) total_waits,
(time_waited / 360000) time_waited,
DECODE(event, 'SQL*Net message from client', 0,
'rdbms ipc message', 0, 'slave wait', 0,
'pmon timer', 0, 'smon timer', 0,
'rdbms ipc reply', 0, 'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0, 'Null event', 0,
(time_waited / b.total_time_waited) * 100) pct_significant,
(average_wait / 100) average_wait
FROM sys.v$system_event a, total_wait_time b
WHERE (time_waited / 360000) >= 0.01
ORDER BY pct_significant DESC, time_waited DESC
) WHERE ROWNUM < 11;
SELECT /*+ RULE */ s.sid || ',' || s.serial# SESS, s.machine, s.sql_id,
w.wait_class, w.event, w.p1, w.p2, w.p3, w.wait_time,
w.seconds_in_wait, w.state,
final_blocking_session_status, final_blocking_instance,
final_blocking_session
FROM v$session_wait w, v$session s
WHERE w.sid = s.sid AND w.wait_class != 'Idle'
ORDER BY w.wait_class;
九、性能诊断优化类
9.1 查询 SQL 资源消耗 TOP10
功能：统计 SQL 的 CPU、等待、IO 资源消耗 Top10
适用：定位耗资源 SQL
COL type FOR A10
SELECT * FROM (
SELECT ash.SQL_ID, ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
SUM(DECODE(ash.session_state, 'ON CPU', 1, 0)) AS "CPU",
SUM(DECODE(ash.session_state, 'WAITING', 1, 0)) -
SUM(DECODE(ash.session_state, 'WAITING', DECODE(wait_class, 'User I/O', 1, 0), 0)) AS "WAIT",
SUM(DECODE(ash.session_state, 'WAITING', DECODE(wait_class, 'User I/O', 1, 0), 0)) AS "IO",
SUM(DECODE(ash.session_state, 'ON CPU', 1, 1)) AS "TOTAL"
FROM v$active_session_history ash, audit_actions aud
WHERE SQL_ID IS NOT NULL
AND ash.sql_opcode = aud.action
AND ash.sample_time > SYSDATE - &minutes / (60 * 24)
GROUP BY sql_id, SQL_PLAN_HASH_VALUE, aud.name
ORDER BY SUM(DECODE(session_state, 'ON CPU', 1, 1)) DESC
) WHERE ROWNUM < 10;
提示：输入
&minutes指定时间范围（分钟）
9.2 查询活跃会话
功能：查看当前运行的用户会话、SID、等待事件
适用：活跃会话监控
SET LINESIZE 200
COL sid FORMAT 999999
COL s# FORMAT 9999999
COL username FORMAT A15
COL event FORMAT A40
COL machine FORMAT A20
COL p123 FORMAT A18
COL wt FORMAT 999
COL spid FORMAT A15
COL SQL_ID FOR A18
SELECT /*+ XJ LEADING(S) FIRST_ROWS */
S.SID, S.SERIAL# S#, P.SPID,
NVL(S.USERNAME, SUBSTR(P.PROGRAM, LENGTH(P.PROGRAM) - 6)) USERNAME,
S.MACHINE, S.EVENT,
S.P1 || '/' || S.P2 || '/' || S.P3 P123,
S.WAIT_TIME WT,
NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.STATUS = 'ACTIVE'
AND P.BACKGROUND IS NULL;
9.3 查询 RAC 通道等待
功能：RAC 环境分析组件通信通道等待
适用：Oracle RAC
SELECT CHANNEL, SUM(wait_count) sum_wait_count
FROM GV$CHANNEL_WAITS
GROUP BY CHANNEL
ORDER BY SUM(wait_count) DESC;
SELECT INST_ID, EVENT, COUNT(*)
FROM gv$session_wait
GROUP BY INST_ID, EVENT
HAVING COUNT(*) > 10
ORDER BY INST_ID ASC, COUNT(*) DESC;
9.4 查询 SQL 执行计划
功能：分析指定 SQL 的执行计划
适用：SQL 性能分析
SET LINESIZE 200
SET PAGESIZE 600
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(TO_CHAR('&SQL_ID'), &child_NULL, 'advanced'));
9.5 查询 AWR 执行计划
功能：从 AWR 中提取 SQL 历史执行计划
适用：历史计划查看
SET LINESIZE 200
SET PAGESIZE 600
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(TO_CHAR('&SQL_ID'), &plan_hash_NULL));
9.6 查询 SQL 计划详情
功能：分析 SQL 不同快照时段的执行计划及性能退化
适用：计划变化追踪
COL PLAN_HASH_VALUE FOR 9999999999
COL instance_number FOR 9
COL snap_id HEADING 'SnapId' FORMAT 999999
COL executions_delta HEADING "No. of exec" FORMAT 999999
COL date_time HEADING 'Date time' FOR A20
COL avg_lio HEADING 'LIO/exec' FORMAT 999999999999
COL avg_cputime_s HEADING 'CPUTIM/exec' FORMAT 99999
COL avg_etime_s HEADING 'ETIME/exec' FORMAT 999999
COL avg_pio HEADING 'PIO/exec' FORMAT 999999
COL avg_row HEADING 'ROWs/exec' FORMAT 9999999999
COL sql_profile FORMAT A35
SELECT DISTINCT s.snap_id, s.instance_number, PLAN_HASH_VALUE,
TO_CHAR(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi') || '_' ||
TO_CHAR(s.END_INTERVAL_TIME,'hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta / DECODE(NVL(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_lio,
(SQL.cpu_time_delta / 1000000) / DECODE(NVL(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_cputime_s,
(SQL.elapsed_time_delta / 1000000) / DECODE(NVL(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_etime_s,
SQL.DISK_READS_DELTA / DECODE(NVL(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_pio,
SQL.rows_processed_total / DECODE(NVL(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_row,
SQL.sql_profile
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql.instance_number = s.instance_number
AND sql_id IN ('&sql_id')
ORDER BY s.snap_id;
9.7 查询临时表空间排序
功能：监控临时表空间排序段使用情况
适用：排序瓶颈定位
SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT RPAD(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON
PROMPT +========================================================================+
PROMPT | Report : Temporary Sort Segments |
PROMPT | Instance : ¤t_instance |
PROMPT +========================================================================+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
COLUMN instance_name FORMAT A8 HEADING 'Instance'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace|Name' JUST RIGHT
COLUMN temp_segment_name FORMAT A8 HEADING 'Segment|Name' JUST RIGHT
COLUMN current_users FORMAT 9,999 HEADING 'Current|Users' JUST RIGHT
COLUMN total_temp_segment_size FORMAT 999,999,999,999 HEADING 'Total Temp|Segment Size' JUST RIGHT
COLUMN pct_used FORMAT 999 HEADING 'Pct.|Used' JUST RIGHT
COLUMN free_requests FORMAT 999 HEADING 'Free|Requests' JUST RIGHT
SELECT i.instance_name instance_name,
t.tablespace_name tablespace_name,
'SYS.' || t.segment_file || '.' || t.segment_block temp_segment_name,
t.current_users current_users,
(t.total_blocks * b.value) total_temp_segment_size,
(t.used_blocks * b.value) currently_used_bytes,
TRUNC(ROUND((t.used_blocks / t.total_blocks) * 100)) pct_used,
t.extent_hits extent_hits,
(t.max_blocks * b.value) max_size,
(t.max_used_blocks * b.value) max_used_size,
(t.max_sort_blocks * b.value) max_sort_size,
t.free_requests free_requests
FROM gv$instance i, gv$sort_segment t,
(SELECT value FROM v$parameter WHERE name = 'db_block_size') b
WHERE t.inst_id = i.inst_id
ORDER BY i.instance_name, t.tablespace_name;
9.8 查询消耗临时空间的 SQL
功能：定位消耗临时空间的 SQL
适用：临时空间分析
SELECT SQL_ID, sample_time, TEMP_SPACE_ALLOCATED
FROM v$active_session_history
WHERE SQL_ID IS NOT NULL
AND sample_time > TO_TIMESTAMP('&start_time','yyyy-mm-dd hh24:mi:ss.ff')
AND sample_time < TO_TIMESTAMP('&end_time','yyyy-mm-dd hh24:mi:ss.ff')
ORDER BY TEMP_SPACE_ALLOCATED;
9.9 查询 RAC 消耗临时空间的 SQL
功能：RAC 环境定位消耗超指定 GB 的 SQL
适用：Oracle RAC
SET LINESIZE 300
COL inst_id FOR 99
COL sql_id FOR A16
COL session_id FOR 999999
COL session_serial# FOR 999999
COL sql_exec_id FOR 999999999
COL sql_exec_start FOR A20
COL sql_plan_hash_value FOR 99999999999
COL sql_plan_operation FOR A12
COL min_sample_time FOR A25
COL max_sample_time FOR A25
COL temp_mb FOR 999999999
SELECT inst_id, session_id, session_serial#, sql_exec_id, sql_exec_start,
sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_line_id,
MIN(sample_time), MAX(sample_time),
MAX(temp_space_allocated) / POWER(1024, 2) temp_mb
FROM gv$active_session_history
WHERE temp_space_allocated >= &GB * 1024 * 1024 * 1024
GROUP BY inst_id, session_id, session_serial#, sql_exec_id, sql_exec_start,
sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_line_id
ORDER BY temp_mb DESC;
9.10 按时间筛选 RAC 消耗临时空间 SQL
功能：按时间范围筛选消耗临时空间的 SQL
适用：Oracle RAC
SET LINESIZE 300
COL inst_id FOR 99
COL sql_id FOR A16
COL temp_mb FOR 999999999
SELECT inst_id, session_id, session_serial#, sql_exec_id, sql_exec_start,
sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_line_id,
MIN(sample_time), MAX(sample_time),
MAX(temp_space_allocated) / POWER(1024, 2) temp_mb
FROM gv$active_session_history
WHERE temp_space_allocated >= &GB * 1024 * 1024 * 1024
AND sql_exec_start >= SYSDATE - &hour_ago / 24
GROUP BY inst_id, session_id, session_serial#, sql_exec_id, sql_exec_start,
sql_id, sql_plan_hash_value, sql_plan_operation, sql_plan_line_id
ORDER BY temp_mb DESC;
9.11 查询历史阻塞链
功能：分析指定时间范围内的阻塞链
适用：历史阻塞分析
SET LINES 300
COL LVL2_EVENT FOR A40
COL FINAL_PROGRAM FOR A30
COL FINAL_EVT FOR A30
WITH ash AS (
SELECT instance_number, SESSION_ID, event, BLOCKING_SESSION, program,
TO_CHAR(SAMPLE_TIME,'YYYY/MM/DD HH24:MI:SS') SAMPLE_TIME,
sample_id, blocking_inst_id
FROM dba_hist_active_sess_history
WHERE SAMPLE_TIME BETWEEN TO_DATE('&start_time','YYYY/MM/DD HH24:MI')
AND TO_DATE('&end_time','YYYY/MM/DD HH24:MI')
)
SELECT SAMPLE_TIME, FINAL_BLK, FINAL_PROGRAM, NVL(FINAL_EVT, 'ON CPU') AS FINAL_EVT,
MAX(LVL2_EVENT) AS LVL2_EVENT, COUNT(*) AS cnt
FROM (
SELECT SESSION_ID, SAMPLE_TIME,
SYS_CONNECT_BY_PATH(SESSION_ID, ',') CHAIN,
CONNECT_BY_ROOT(PROGRAM) AS FINAL_PROGRAM,
CONNECT_BY_ROOT(SESSION_ID) FINAL_BLK,
CONNECT_BY_ROOT(event) FINAL_EVT, event,
CASE WHEN LEVEL = 2 THEN event END AS LVL2_EVENT
FROM ash
START WITH BLOCKING_SESSION IS NULL
CONNECT BY PRIOR SESSION_ID = BLOCKING_SESSION
AND PRIOR INSTANCE_NUMBER = BLOCKING_INST_ID
AND sample_id = PRIOR sample_id
)
GROUP BY FINAL_BLK, FINAL_EVT, SAMPLE_TIME, FINAL_PROGRAM
HAVING COUNT(*) > 2
ORDER BY SAMPLE_TIME;
9.12 查询 SQL 缓存（多节点）
功能：查看 SQL 在 RAC 各节点的执行情况
适用：Oracle RAC
SET LINESIZE 200
COL inst_id FOR 99
COL sql_id FOR A16
COL plan_hash_value FOR 9999999999
COL executions FOR 999999999
SELECT inst_id, sql_id, plan_hash_value, executions, loads, invalidations
FROM gv$sql
WHERE sql_id = '&sql_id'
ORDER BY inst_id;
9.13 生成 SQL 优化建议
功能：通过 SQL Tuning Advisor 生成优化建议
适用：SQL 自动优化
DECLARE
my_task_id VARCHAR2(30);
BEGIN
my_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '&sql_id',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'TUNE_' || '&sql_id',
description => 'Tuning task for SQL ID &sql_id'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_' || '&sql_id');
DBMS_OUTPUT.PUT_LINE('Task ID: ' || my_task_id);
END;
/
-- 查看优化建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_' || '&sql_id') FROM DUAL;
9.14 查询 TOP SQL 等待事件
功能：找出等待事件最多的 Top SQL
适用：瓶颈定位
COL sql_id FOR A16
COL event FOR A40
COL total_waits FOR 999999999
COL time_waited FOR 9999999999
SELECT * FROM (
SELECT sql_id, event, SUM(total_waits) total_waits, SUM(time_waited) time_waited
FROM v$active_session_history ash
JOIN v$event_name en ON ash.event_id = en.event_id
WHERE sample_time > SYSDATE - 1 / 24
AND sql_id IS NOT NULL
GROUP BY sql_id, event
ORDER BY time_waited DESC
) WHERE ROWNUM <= 10;
9.15 查询表访问频率
功能：查看表的访问频率，判断热点程度
适用：热点表分析
SET LINESIZE 200
COL table_name FOR A30
COL owner FOR A20
COL executions FOR 9999999
SELECT o.owner, o.object_name table_name, COUNT(*) executions
FROM v$sql s
JOIN dba_objects o ON INSTR(s.sql_text, o.object_name) > 0
WHERE o.object_type = 'TABLE'
AND o.object_name = UPPER('&table_name')
AND o.owner = UPPER('&owner')
GROUP BY o.owner, o.object_name
ORDER BY executions DESC;
9.16 查询索引使用情况
功能：检查索引是否被使用，识别无用索引
适用：索引优化
SET LINESIZE 200
COL index_name FOR A30
COL table_name FOR A30
COL owner FOR A20
COL used FOR A10
SELECT i.owner, i.index_name, i.table_name,
CASE WHEN s.index_name IS NULL THEN 'UNUSED' ELSE 'USED' END used
FROM dba_indexes i
LEFT JOIN v$object_usage s ON i.index_name = s.index_name AND i.owner = s.table_owner
WHERE i.table_name = UPPER('&table_name')
AND i.owner = UPPER('&owner');
9.17 查询 CPU 高消耗会话
功能：定位当前 CPU 消耗最高的会话
适用：CPU 瓶颈分析
SET LINESIZE 200
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL machine FOR A30
COL sql_id FOR A16
COL cpu_usage FOR 999999999.99
SELECT s.sid, s.serial#, s.username, s.machine, s.sql_id,
SUM(value / 100) cpu_usage
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
JOIN v$statname sn ON ss.statistic# = sn.statistic#
WHERE sn.name = 'CPU used by this session'
AND s.status = 'ACTIVE'
GROUP BY s.sid, s.serial#, s.username, s.machine, s.sql_id
ORDER BY cpu_usage DESC
FETCH FIRST 10 ROWS ONLY;
9.18 查询 IO 高消耗文件
功能：找出 IO 消耗最高的数据文件
适用：IO 瓶颈分析
SET LINESIZE 200
COL file_name FOR A50
COL phyrds FOR 999999999
COL phywrts FOR 999999999
COL phyblkrd FOR 999999999
COL phyblkwr FOR 999999999
SELECT df.file_name, f.phyrds, f.phywrts, f.phyblkrd, f.phyblkwr
FROM v$filestat f
JOIN dba_data_files df ON f.file# = df.file_id
ORDER BY (f.phyrds + f.phywrts) DESC
FETCH FIRST 10 ROWS ONLY;
9.19 查询数据库等待事件 TOP10
功能：统计数据库整体等待事件 Top10
适用：系统瓶颈识别
SET LINESIZE 200
COL event FOR A40
COL total_waits FOR 999999999
COL time_waited FOR 9999999999
COL avg_wait FOR 999999999.99
SELECT * FROM (
SELECT event, total_waits, time_waited,
ROUND(time_waited / total_waits, 2) avg_wait
FROM v$system_event
WHERE wait_class NOT IN ('Idle', 'System')
ORDER BY time_waited DESC
) WHERE ROWNUM <= 10;
9.20 查询 RAC 节点性能差异
功能：对比 RAC 各节点性能指标
适用：Oracle RAC
SET LINESIZE 300
COL inst_id FOR 99
COL name FOR A30
COL value FOR 9999999999
SELECT inst_id, name, value
FROM gv$sysstat
WHERE name IN ('CPU used by this session', 'physical reads',
'physical writes', 'user commits', 'user rollbacks')
ORDER BY name, inst_id;
9.21 查询长事务
功能：定位长时间未提交的事务
适用：事务分析
SET LINESIZE 200
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL start_time FOR A30
COL status FOR A10
SELECT s.sid, s.serial#, s.username, t.start_time, t.status
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE t.start_time < SYSDATE - 1 / 24 -- 1小时以上的事务
ORDER BY t.start_time;
9.22 查询共享池碎片
功能：检查共享池碎片情况
适用：内存调优
SET LINESIZE 200
COL pool FOR A20
COL free_memory FOR 999999999
COL avg_free_size FOR 999999
COL free_chunks FOR 99999
SELECT pool, SUM(free_memory) free_memory,
AVG(free_memory) avg_free_size, COUNT(*) free_chunks
FROM v$sgastat
WHERE pool IN ('shared pool', 'large pool')
AND name = 'free memory'
GROUP BY pool;
9.23 查询重做日志切换频率
功能：统计日志切换频率，判断日志大小是否合理
适用：日志分析
SET LINESIZE 200
COL switch_time FOR A30
COL switch_count FOR 999
SELECT TRUNC(first_time, 'hh24') switch_time, COUNT(*) switch_count
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TRUNC(first_time, 'hh24')
ORDER BY switch_time;
9.24 查询 SQL 执行耗时分布
功能：分析 SQL 执行耗时分布
适用：SQL 性能分析
SET LINESIZE 200
COL sql_id FOR A16
COL elapsed_time FOR 999999999
COL executions FOR 999999
COL avg_elapsed_time FOR 9999999
SELECT sql_id, elapsed_time, executions,
elapsed_time / executions avg_elapsed_time
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY avg_elapsed_time DESC;
十、ADG 备库运维类
10.1 查询 ADG 同步状态
功能：检查物理备库的同步状态及延迟
适用：Oracle ADG
SET LINESIZE 200
COL name FOR A30
COL value FOR A50
SELECT name, value FROM v$dataguard_stats;
10.2 查询备库应用进程
功能：查看 MRP 进程状态，确认日志应用是否正常
适用：Oracle ADG
SET LINESIZE 200
COL process FOR A10
COL status FOR A20
COL client_process FOR A15
SELECT process, status, client_process, sequence#
FROM v$managed_standby;
10.3 启动备库日志应用
功能：启动物理备库的日志应用进程
适用：Oracle ADG
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;
10.4 停止备库日志应用
功能：停止物理备库的日志应用进程
适用：Oracle ADG
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
10.5 查询备库日志差距
功能：查询主备库之间归档日志差距
适用：Oracle ADG
SET LINESIZE 200
COL thread# FOR 99
COL max_seq_primary FOR 999999
COL max_seq_standby FOR 999999
COL gap FOR 999999
SELECT a.thread#, a.max_seq_primary, b.max_seq_standby,
a.max_seq_primary - b.max_seq_standby gap
FROM (SELECT thread#, MAX(sequence#) max_seq_primary
FROM v$archived_log WHERE archived = 'YES' GROUP BY thread#) a,
(SELECT thread#, MAX(sequence#) max_seq_standby
FROM v$archived_log WHERE applied = 'YES' GROUP BY thread#) b
WHERE a.thread# = b.thread#;
10.6 备库角色切换检查
功能：检查备库是否满足角色切换条件
适用：Oracle ADG
SET LINESIZE 200
COL switchover_status FOR A30
SELECT switchover_status FROM v$database;
10.7 查询备库文件同步
功能：检查主备库数据文件是否同步
适用：Oracle ADG
SET LINESIZE 200
COL name FOR A50
COL status FOR A10
COL bytes FOR 9999999999
SELECT name, status, bytes FROM v$datafile
UNION ALL
SELECT name, status, bytes FROM v$tempfile;
十一、RMAN 备份归档类
11.1 全库备份
功能：RMAN 全库备份，包含控制文件和归档
适用：Oracle RMAN
RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/backup/orcl/full_%U.bkp';
BACKUP FULL DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/orcl/ctl_%U.bkp';
RELEASE CHANNEL c1;
}
11.2 增量备份
功能：执行差异增量备份（级别 1）
适用：Oracle RMAN
RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/backup/orcl/inc1_%U.bkp';
BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
RELEASE CHANNEL c1;
}
11.3 查看备份集
功能：查询 RMAN 备份集详细信息
适用：Oracle RMAN
LIST BACKUP SUMMARY;
LIST BACKUP SET DETAILS;
11.4 删除过期备份
功能：删除 RMAN 中过期的备份集
适用：Oracle RMAN
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
十二、事务与参数查询类
12.1 查询数据库参数
功能：查询指定的数据库初始化参数
适用：参数检查
SET LINESIZE 200
COL name FOR A30
COL value FOR A50
COL description FOR A80
SELECT name, value, description
FROM v$parameter
WHERE name LIKE UPPER('%¶m_name%');
12.2 查询当前事务
功能：查询数据库当前活跃事务信息
适用：事务分析
SET LINESIZE 200
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL start_time FOR A30
COL xid FOR A20
SELECT s.sid, s.serial#, s.username, t.start_time, t.xid,
t.status, t.used_ublk, t.used_urec
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.start_time;
十三、元数据查询类
13.1 查询用户表
功能：查询指定用户下的所有表及表空间信息
适用：元数据查询
SET LINESIZE 200
COL table_name FOR A30
COL tablespace_name FOR A30
COL num_rows FOR 99999999
SELECT table_name, tablespace_name, num_rows, blocks
FROM dba_tables
WHERE owner = UPPER('&owner')
ORDER BY table_name;
13.2 查询表结构
功能：查询指定表的列结构、数据类型和约束
适用：表结构分析
SET LINESIZE 200
COL column_name FOR A30
COL data_type FOR A20
COL data_length FOR 99999
COL nullable FOR A10
SELECT column_name, data_type, data_length, data_precision,
data_scale, nullable, default_value
FROM dba_tab_columns
WHERE owner = UPPER('&owner')
AND table_name = UPPER('&table_name')
ORDER BY column_id;
13.3 查询数据库对象
功能：查询指定用户下的所有数据库对象及类型
适用：对象统计
SET LINESIZE 200
COL object_name FOR A30
COL object_type FOR A20
COL status FOR A10
SELECT object_name, object_type, status, created
FROM dba_objects
WHERE owner = UPPER('&owner')
ORDER BY object_type, object_name;
13.4 查询表空间数据文件
功能：查询表空间对应的数据文件信息
适用：存储管理
SET LINESIZE 200
COL file_name FOR A50
COL tablespace_name FOR A30
COL bytes FOR 9999999999
COL autoextensible FOR A10
SELECT file_name, tablespace_name,
bytes / 1024 / 1024 size_mb,
autoextensible, maxbytes / 1024 / 1024 max_size_mb
FROM dba_data_files
WHERE tablespace_name = UPPER('&tablespace_name');
📊 脚本速查表
| 类别 | 脚本数 | 快速入口 |
|---|---|---|
| 基础环境配置 | 5 | sqlprompt、nls_date_format、NLS_LANG |
| 表空间管理 | 5 | dba_tablespaces、扩容SQL、收缩SQL |
| ASM 磁盘 | 3 | v$asm_diskgroup、v$asm_disk |
| 归档日志 | 5 | delete archivelog、归档增量 |
| 索引管理 | 5 | dba_indexes、重建SQL |
| 约束查询 | 1 | dba_constraints |
| 分区表 | 3 | dba_tab_partitions、分区键 |
| 会话锁 | 6 | gv$session、v$lock、锁等待链 |
| 性能诊断 | 24 | Top SQL、执行计划、AWR、等待事件 |
| ADG 备库 | 7 | 同步状态、MRP进程、切换检查 |
| RMAN 备份 | 4 | 全库备份、增量备份 |
| 事务参数 | 2 | v$parameter、v$transaction |
| 元数据 | 4 | dba_tables、dba_tab_columns |
收藏本文，Oracle DBA 巡检脚本一站式搞定！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
