运维管理18 分钟阅读
Oracle DBA 高效运维指南：高频实用 SQL 大全
大家好，这里是公众号 DBA学习之路，分享一些学习数据库路上的知识和经验。 @TOC目录 作为一名 Oracle DBA，在日常数据库运维工作中，我们经常需要快速获取数据库关键信息、排查性能问题或执行维护操作。经过多年实
2025年3月28日阅读—点赞—收藏—
oraclerac
内容阅读
在知识库中专注阅读，并随时返回相关工具与课程
大家好，这里是公众号 DBA学习之路，分享一些学习数据库路上的知识和经验。
前言
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
作为一名 Oracle DBA，在日常数据库运维工作中，我们经常需要快速获取数据库关键信息、排查性能问题或执行维护操作。经过多年实战积累，我整理了一套高频使用的 SQL 查询集合，涵盖了表空间监控、备份恢复、DataGuard 管理、性能诊断等核心场景。
这些 SQL 经过生产环境验证，能显著提升 DBA 的工作效率。现在将这些实用脚本分享给大家，希望能帮助各位同行更高效地完成数据库运维工作。
Top SQL
表空间使用率
一键查询表空间使用率 SQL：
1set linesize 2222 pagesize 1000 heading on wrap on2COLUMN tbsname HEADING 'Name'3COLUMN total_gb FORMAT 99,990.99 HEADING 'Total(GB)'4COLUMN used_gb FORMAT 99,990.99 HEADING 'Used(GB)'5COLUMN left_gb FORMAT 99,990.99 HEADING 'Left(GB)'6COLUMN used_percent FORMAT 990.99 HEADING 'Used(%)'7COLUMN count_file FORMAT 9999 HEADING 'File Count'89-- 11G10SELECT d.tablespace_name tbsname,11 round(d.tablespace_size * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,12 2) total_gb,13 round(d.used_space * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,14 2) used_gb,15 round((d.tablespace_size - d.used_space) * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,16 2) left_gb,17 round(d.used_percent,2) used_percent,18 (select COUNT(file_name) from dba_data_files where tablespace_name = d.tablespace_name) count_file19 FROM dba_tablespace_usage_metrics d20 ORDER BY 2 DESC;2122-- 12C 以上23SELECT d.tablespace_name tbsname,24 round(d.tablespace_size * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,25 2) total_gb,26 round(d.used_space * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,27 2) used_gb,28 round((d.tablespace_size - d.used_space) * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,29 2) left_gb,30 round(d.used_percent,2) used_percent,31 (select COUNT(file_name) from cdb_data_files where con_id = d.con_id and tablespace_name = d.tablespace_name) count_file32 FROM cdb_tablespace_usage_metrics d33WHERE d.con_id not in (1,2)34 ORDER BY d.con_id,2 DESC;RMAN 备份
一键查询 RMAN 备份情况：
1set linesize 2222 pagesize 1000 heading on wrap on2col status for a303col input_type for a204col start_time for a255col end_time for a256col input_bytes_display for a107col output_bytes_display for a10 8col time_taken_display for a10910SELECT input_type, status,11 to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as start_time,12 to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') as end_time,13 input_bytes_display, output_bytes_display, time_taken_display, compression_ratio14FROM v$rman_backup_job_details15ORDER BY start_time DESC;DataGuard
DG 日常运维高频 SQL：
1-- 主库查询 DataGuard 同步状态2set linesize 2222 heading on wrap on3column dest_name format a204column status format a85column database_mode format a156column recovery_mode format a357column protection_mode format a258column destination format a159column archived_seq# format 99999999910column applied_seq# format 99999999911column error format a1012column srl format a513column db_unique_name format a1514column gap_status format a101516SELECT inst_id, dest_name, status, database_mode, protection_mode,17 recovery_mode, gap_status, archived_seq#, applied_seq#, srl,18 db_unique_name, destination, error19FROM gv$archive_dest_status20WHERE status <> 'INACTIVE'21AND type = 'PHYSICAL';2223-- 主库查看当前归档日志24set line2222 pages100025SELECT thread#,26 MAX(sequence#) "Last Primary Seq Generated"27 FROM v$archived_log val,28 v$database vdb29 WHERE val.resetlogs_change# = vdb.resetlogs_change#30 GROUP BY thread#31 ORDER BY 1;3233-- 备库查看当前归档日志34set line2222 pages100035SELECT thread#,36 MAX(sequence#) "Last Standby Seq Received"37 FROM v$archived_log val,38 v$database vdb39 WHERE val.resetlogs_change# = vdb.resetlogs_change#40 GROUP BY thread#41 ORDER BY 1;4243set line2222 pages100044SELECT thread#,45 MAX(sequence#) "Last Standby Seq Applied"46 FROM v$archived_log val,47 v$database vdb48 WHERE val.resetlogs_change# = vdb.resetlogs_change#49 AND val.applied IN ('YES',50 'IN-MEMORY')51 GROUP BY thread#52 ORDER BY 1;5354-- 备库查询 DataGuard 进程同步状态55set line2222 pages100056select inst_id,pid,process,thread#,sequence#,status,delay_mins from gv$managed_standby;5758-- 备库查询未应用的归档日志59select inst_id,count(*) from gv$archived_log where applied='NO' group by inst_id order by inst_id;6061-- 检查是否存在 GAP62select * from gv$archive_gap;6364-- 备库查看闪回情况65set linesize 2222 heading on wrap on66column check_date format a1967column flashback_to format a1968column interval_day format 99999.9969SELECT to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS check_date,70 to_char(oldest_flashback_time, 'yyyy-mm-dd hh24:mi:ss') AS flashback_to,71 round(SYSDATE - oldest_flashback_time, 2) AS interval_day,72 retention_target,73 round(flashback_size / 1024 / 1024, 2) AS flashback_mb,74 round(estimated_flashback_size / 1024 / 1024, 2) AS estimated_flashback_mb75FROM v$flashback_database_log;等待事件
一键获取当前数据库 Top 等待事件：
1-- 查看当前 top event 的源头2set line2222 pages10003column serial# format 9999994column object_name format a305column sql_text format a506col session_detail format a3078SELECT rpad('+',9 LEVEL,10 '-') || sid || ' ' || sess.module session_detail,11 sid,12 serial#,13 'alter system kill session ''' || sid || ',' || serial# || ',@' || sess.inst_id || ''' immediate;' AS kill_sql,14 blocker_sid,15 sess.inst_id,16 wait_event_text,17 object_name,18 rpad(' ',19 LEVEL) || sql_text sql_text20 FROM v$wait_chains c21 LEFT OUTER JOIN dba_objects o22 ON (row_wait_obj# = object_id)23 JOIN gv$session sess24 USING (sid)25 LEFT OUTER JOIN v$sql SQL26 ON (sql.sql_id = sess.sql_id AND sql.child_number = sess.sql_child_number)27CONNECT BY PRIOR sid = blocker_sid28 AND PRIOR sess_serial# = blocker_sess_serial#29 AND PRIOR instance = blocker_instance30 START WITH blocker_is_valid = 'FALSE';行级锁
一键获取当前数据库行级锁 SQL：
1-- 查看当前数据库节点是否存在锁2set line2222 pages10003col OS_USER_NAME for a104col owner for a205col object_name for a406col object_type for a307select a.OS_USER_NAME, c.owner, c.object_name,c.object_type, b.sid, b.serial#, logon_time 8 from v$locked_object a, v$session b, dba_objects c 9 where a.session_id = b.sid 10 and a.object_id = c.object_id 11 order by b.logon_time;1213-- 查看锁的 sql14select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));15select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in ( select session_id from v$locked_object));1617-- 系统层面 kill 会话18set line2222 pages1000019select 'kill -9 ' || a.spid20 from v$process a, v$session b21 where a.addr = b.paddr22 and a.background is null23 and b.sid = 272124 and b.serial# = 4317;2526## 关闭连接当前数据库的监听进程27ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9在线日志切换
一键查询在线日志切换频率：
1SELECT2 SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY3 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H004 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H015 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H026 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H037 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H048 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H059 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H0610 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H0711 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H0812 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H0913 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H1014 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H1115 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H1216 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H1317 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H1418 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H1519 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H1620 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H1721 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H1822 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H1923 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H2024 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H2125 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H2226 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H2327 , COUNT(*) TOTAL28FROM29 gv$log_history a where SYSDATE - first_time < 730GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by 1;用户信息
一键查询非系统用户信息：
1-- 11G2set lines222 pages1000 3col username for a25 4col account_status for a20 5col default_tablespace for a20 6col temporary_tablespace for a20 7col profile for a208SELECT username,9 account_status,10 default_tablespace,11 temporary_tablespace,12 created,13 profile14 FROM dba_users15 WHERE account_status = 'OPEN'16 ORDER BY created;1718-- 12C 以上19set lines222 pages1000 20col username for a25 21col account_status for a20 22col default_tablespace for a20 23col temporary_tablespace for a20 24col profile for a2025SELECT a.username,26 b.account_status,27 b.default_tablespace,28 b.temporary_tablespace,29 b.created,30 b.profile31 FROM all_users a,32 dba_users b33 WHERE a.user_id = b.user_id34 AND a.oracle_maintained = 'N'35 AND b.account_status = 'OPEN';ASM 磁盘组
一键查询 ASM 磁盘组相关信息：
1-- asm 磁盘组信息2set lines2222 pages10000 3select name,round(usable_file_mb/1024,2) usable_file_gb,round((total_mb - free_mb)/total_mb * 100,2) used4 from v$asm_diskgroup;56-- asm 磁盘信息7set line2222 pages1000 8col path for a60 9col FAILGROUP for a12 10col header_status for a1611select path,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB from v$asm_disk order by 2,3,4,5;1213-- asm 磁盘组明细信息14set lines2222 pages10000 15col path for a40 16col FAILGROUP for a12 17col diskgroup_name for a3018col disk_name for a3019col HEADER_STATUS for a1020col state for a1021Col MOUNT_STATUS for a722col MODE_STATUS for a1023select b.name diskgroup_name,a.path,a.FAILGROUP,a.name as disk_name,a.STATE,a.MODE_STATUS,a.HEADER_STATUS,a.MOUNT_STATUS,a.REPAIR_TIMER24from v$asm_disk a,v$asm_diskgroup b where a.GROUP_NUMBER = b.GROUP_NUMBER25order by a.FAILGROUp;2627-- 查看 ASM 磁盘重平衡28set line2222 pages1000029col error_code for a2030select * from gv$asm_operation;DBLink
一键获取 DBLink 创建 SQL：
1set line2222 pages10002col text for a1503SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '|| DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST ||';' TEXT4FROM SYS.LINK$ L, SYS.USER$ U5WHERE L.OWNER# = U.USER#;数据文件收缩
一键收缩数据文件 SQL：
1set line222 pagesize10002col tablespace_name for a303col sql for a1004SELECT a.tablespace_name,5'alter database datafile ''' || file_name || ''' resize ' ||6 (ceil((nvl(hwm,7 1) * c.value) / 1024 / 1024) + 50) || 'M;' sql8 FROM dba_data_files a,9 (SELECT file_id,10 MAX(block_id + blocks - 1) hwm11 FROM dba_extents12 GROUP BY file_id) b,13 (SELECT VALUE14 FROM v$parameter15 WHERE NAME = 'db_block_size') c16 WHERE a.file_id = b.file_id(+)17 AND a.status != 'INVALID'18 AND a.online_status != 'OFFLINE'19 AND a.tablespace_name NOT IN ('UNDOTBS1',20 'UNDOTBS2',21 'SYSTEM',22 'SYSAUX',23 'USERS')24 ORDER BY 1 DESC;AWR
一键生成 AWR 性能报告：
1-- 生成单实例 AWR 报告：2@$ORACLE_HOME/rdbms/admin/awrrpt.sql34--生成 Oracle RAC AWR 报告5@$ORACLE_HOME/rdbms/admin/awrgrpt.sql67-- 生成 RAC 环境中特定数据库实例的 AWR 报告8@$ORACLE_HOME/rdbms/admin/awrrpti.sql910-- 生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法11@$ORACLE_HOME/rdbms/admin/awrgrpti.sql1213-- 生成 SQL 语句的 AWR 报告14@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql1516-- 生成特定数据库实例上某个 SQL 语句的 AWR 报告17@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql写在最后
数据库运维是一门需要不断积累和实践的艺术。本文分享的 SQL 脚本只是 DBA 工具箱中的一部分基础工具，真正的专业能力在于理解这些查询背后的原理，并能够根据实际场景灵活运用。
建议各位 DBA 同行在日常工作中持续积累自己的 SQL 脚本库，并定期整理优化。如果您有更好的 SQL 脚本或使用技巧，欢迎在评论区分享交流。让我们共同提升 Oracle 数据库的运维效率，打造更稳定高效的数据库环境！
温馨提示：在生产环境执行任何管理操作前，请务必先在测试环境验证，并确保有完整的备份方案。