Oracle DBA 高效运维指南：高频实用 SQL 大全

大家好，这里是 DBA学习之路，专注于提升数据库运维效率。

前言

作为一名 Oracle DBA，在日常数据库运维工作中，我们经常需要快速获取数据库关键信息、排查性能问题或执行维护操作。经过多年实战积累，我整理了一套高频使用的 SQL 查询集合，涵盖了表空间监控、备份恢复、DataGuard 管理、性能诊断等核心场景。

这些 SQL 经过生产环境验证，能显著提升 DBA 的工作效率。现在将这些实用脚本分享给大家，希望能帮助各位同行更高效地完成数据库运维工作。

Top SQL

表空间使用率

一键查询表空间使用率 SQL：

set linesize 2222 pagesize 1000 heading on wrap on
COLUMN  tbsname                             HEADING  'Name'
COLUMN  total_gb        FORMAT 99,990.99    HEADING  'Total(GB)'
COLUMN  used_gb         FORMAT 99,990.99    HEADING  'Used(GB)'
COLUMN  left_gb         FORMAT 99,990.99    HEADING  'Left(GB)'
COLUMN  used_percent    FORMAT 990.99       HEADING  'Used(%)'
COLUMN  count_file      FORMAT 9999         HEADING  'File Count'-- 11G
SELECT d.tablespace_name tbsname,round(d.tablespace_size * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,2) total_gb,round(d.used_space * (SELECT value FROM v$parameter WHERE name='db_block_size')  / 1024 / 1024 / 1024,2) used_gb,round((d.tablespace_size - d.used_space) * (SELECT value FROM v$parameter WHERE name='db_block_size')  / 1024 / 1024 / 1024,2) left_gb,round(d.used_percent,2) used_percent,(select COUNT(file_name) from dba_data_files where tablespace_name = d.tablespace_name) count_fileFROM dba_tablespace_usage_metrics dORDER BY 2 DESC;-- 12C 以上
SELECT d.tablespace_name tbsname,round(d.tablespace_size * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024 / 1024 / 1024,2) total_gb,round(d.used_space * (SELECT value FROM v$parameter WHERE name='db_block_size')  / 1024 / 1024 / 1024,2) used_gb,round((d.tablespace_size - d.used_space) * (SELECT value FROM v$parameter WHERE name='db_block_size')  / 1024 / 1024 / 1024,2) left_gb,round(d.used_percent,2) used_percent,(select COUNT(file_name) from cdb_data_files where con_id = d.con_id and tablespace_name = d.tablespace_name) count_fileFROM cdb_tablespace_usage_metrics d
WHERE d.con_id not in (1,2)ORDER BY d.con_id,2 DESC;

RMAN 备份

一键查询 RMAN 备份情况：

set linesize 2222 pagesize 1000 heading on wrap on
col status for a30
col input_type for a20
col start_time for a25
col end_time for a25
col input_bytes_display for a10
col output_bytes_display for a10 
col time_taken_display for a10SELECT input_type, status,to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as start_time,to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') as end_time,input_bytes_display, output_bytes_display, time_taken_display, compression_ratio
FROM v$rman_backup_job_details
ORDER BY start_time DESC;

DataGuard

DG 日常运维高频 SQL：

-- 主库查询 DataGuard 同步状态
set linesize 2222 heading on wrap on
column dest_name format a20
column status format a8
column database_mode format a15
column recovery_mode format a35
column protection_mode format a25
column destination format a15
column archived_seq# format 999999999
column applied_seq# format 999999999
column error format a10
column srl format a5
column db_unique_name format a15
column gap_status format a10SELECT inst_id, dest_name, status, database_mode, protection_mode,recovery_mode, gap_status, archived_seq#, applied_seq#, srl,db_unique_name, destination, error
FROM gv$archive_dest_status
WHERE status <> 'INACTIVE'
AND type = 'PHYSICAL';-- 主库查看当前归档日志
set line2222 pages1000
SELECT thread#,MAX(sequence#) "Last Primary Seq Generated"FROM v$archived_log val,v$database     vdbWHERE val.resetlogs_change# = vdb.resetlogs_change#GROUP BY thread#ORDER BY 1;-- 备库查看当前归档日志
set line2222 pages1000
SELECT thread#,MAX(sequence#) "Last Standby Seq Received"FROM v$archived_log val,v$database     vdbWHERE val.resetlogs_change# = vdb.resetlogs_change#GROUP BY thread#ORDER BY 1;set line2222 pages1000
SELECT thread#,MAX(sequence#) "Last Standby Seq Applied"FROM v$archived_log val,v$database     vdbWHERE val.resetlogs_change# = vdb.resetlogs_change#AND val.applied IN ('YES','IN-MEMORY')GROUP BY thread#ORDER BY 1;-- 备库查询 DataGuard 进程同步状态
set line2222 pages1000
select inst_id,pid,process,thread#,sequence#,status,delay_mins from gv$managed_standby;-- 备库查询未应用的归档日志
select inst_id,count(*) from gv$archived_log where applied='NO' group by inst_id order by inst_id;-- 检查是否存在 GAP
select * from gv$archive_gap;-- 备库查看闪回情况
set linesize 2222 heading on wrap on
column check_date format a19
column flashback_to format a19
column interval_day format 99999.99
SELECT to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS check_date,to_char(oldest_flashback_time, 'yyyy-mm-dd hh24:mi:ss') AS flashback_to,round(SYSDATE - oldest_flashback_time, 2) AS interval_day,retention_target,round(flashback_size / 1024 / 1024, 2) AS flashback_mb,round(estimated_flashback_size / 1024 / 1024, 2) AS estimated_flashback_mb
FROM v$flashback_database_log;

等待事件

一键获取当前数据库 Top 等待事件：

-- 查看当前 top event 的源头
set line2222 pages1000
column serial# format 999999
column object_name format a30
column sql_text format a50
col session_detail format a30SELECT rpad('+',LEVEL,'-') || sid || ' ' || sess.module session_detail,sid,serial#,'alter system kill session ''' || sid || ',' || serial# || ',@' || sess.inst_id || ''' immediate;' AS kill_sql,blocker_sid,sess.inst_id,wait_event_text,object_name,rpad(' ',LEVEL) || sql_text sql_textFROM v$wait_chains cLEFT OUTER JOIN dba_objects oON (row_wait_obj# = object_id)JOIN gv$session sessUSING (sid)LEFT OUTER JOIN v$sql SQLON (sql.sql_id = sess.sql_id AND sql.child_number = sess.sql_child_number)
CONNECT BY PRIOR sid = blocker_sidAND PRIOR sess_serial# = blocker_sess_serial#AND PRIOR instance = blocker_instanceSTART WITH blocker_is_valid = 'FALSE';

行级锁

一键获取当前数据库行级锁 SQL：

-- 查看当前数据库节点是否存在锁
set line2222 pages1000
col OS_USER_NAME for a10
col owner for a20
col object_name for a40
col object_type for a30
select a.OS_USER_NAME, c.owner, c.object_name,c.object_type, b.sid, b.serial#, logon_time from v$locked_object a, v$session b, dba_objects c where a.session_id = b.sid and a.object_id = c.object_id order by b.logon_time;-- 查看锁的 sql
select 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));
select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in ( select session_id from v$locked_object));-- 系统层面 kill 会话
set line2222 pages10000
select 'kill -9 ' || a.spidfrom v$process a, v$session bwhere a.addr = b.paddrand a.background is nulland b.sid = 2721and b.serial# = 4317;## 关闭连接当前数据库的监听进程
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

在线日志切换

一键查询在线日志切换频率：

SELECTSUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*)                                                                      TOTAL
FROMgv$log_history  a where SYSDATE - first_time < 7
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by 1;

用户信息

一键查询非系统用户信息：

-- 11G
set lines222 pages1000 
col username for a25 
col account_status for a20 
col default_tablespace for a20 
col temporary_tablespace for a20 
col profile for a20
SELECT username,account_status,default_tablespace,temporary_tablespace,created,profileFROM dba_usersWHERE account_status = 'OPEN'ORDER BY created;-- 12C 以上
set lines222 pages1000 
col username for a25 
col account_status for a20 
col default_tablespace for a20 
col temporary_tablespace for a20 
col profile for a20
SELECT a.username,b.account_status,b.default_tablespace,b.temporary_tablespace,b.created,b.profileFROM all_users a,dba_users bWHERE a.user_id = b.user_idAND a.oracle_maintained = 'N'AND b.account_status = 'OPEN';

ASM 磁盘组

一键查询 ASM 磁盘组相关信息：

-- asm 磁盘组信息
set lines2222 pages10000                                                                                                                                         
select name,round(usable_file_mb/1024,2) usable_file_gb,round((total_mb - free_mb)/total_mb * 100,2) usedfrom v$asm_diskgroup;-- asm 磁盘信息
set line2222 pages1000                                                                                                                                           
col path for a60                                                                                                                                               
col FAILGROUP for a12                                                                                                                                                                                                                                                                                    
col header_status for a16
select path,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB from v$asm_disk order by 2,3,4,5;-- asm 磁盘组明细信息
set lines2222 pages10000                                                                                                                                         
col path for a40                                                                                                                                              
col FAILGROUP for a12                                                                                                                                          
col diskgroup_name for a30
col disk_name for a30
col HEADER_STATUS for a10
col state for a10
Col MOUNT_STATUS for a7
col MODE_STATUS for a10
select 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_TIMER
from v$asm_disk a,v$asm_diskgroup b where a.GROUP_NUMBER = b.GROUP_NUMBER
order by a.FAILGROUp;-- 查看 ASM 磁盘重平衡
set line2222 pages10000
col error_code for a20
select * from gv$asm_operation;

DBLink

一键获取 DBLink 创建 SQL：

set line2222 pages1000
col text for a150
SELECT '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 ||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

数据文件收缩

一键收缩数据文件 SQL：

set line222 pagesize1000
col tablespace_name for a30
col sql for a100
SELECT a.tablespace_name,
'alter database datafile ''' || file_name || ''' resize ' ||(ceil((nvl(hwm,1) * c.value) / 1024 / 1024) + 50) || 'M;' sqlFROM dba_data_files a,(SELECT file_id,MAX(block_id + blocks - 1) hwmFROM dba_extentsGROUP BY file_id) b,(SELECT VALUEFROM v$parameterWHERE NAME = 'db_block_size') cWHERE a.file_id = b.file_id(+)AND a.status != 'INVALID'AND a.online_status != 'OFFLINE'AND a.tablespace_name NOT IN ('UNDOTBS1','UNDOTBS2','SYSTEM','SYSAUX','USERS')ORDER BY 1 DESC;

AWR

一键生成 AWR 性能报告：

-- 生成单实例 AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrrpt.sql--生成 Oracle RAC AWR 报告
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql-- 生成 RAC 环境中特定数据库实例的 AWR 报告
@$ORACLE_HOME/rdbms/admin/awrrpti.sql-- 生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql-- 生成 SQL 语句的 AWR 报告
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql-- 生成特定数据库实例上某个 SQL 语句的 AWR 报告
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

写在最后

数据库运维是一门需要不断积累和实践的艺术。本文分享的 SQL 脚本只是 DBA 工具箱中的一部分基础工具，真正的专业能力在于理解这些查询背后的原理，并能够根据实际场景灵活运用。

建议各位 DBA 同行在日常工作中持续积累自己的 SQL 脚本库，并定期整理优化。如果您有更好的 SQL 脚本或使用技巧，欢迎在评论区分享交流。让我们共同提升 Oracle 数据库的运维效率，打造更稳定高效的数据库环境！

温馨提示：在生产环境执行任何管理操作前，请务必先在测试环境验证，并确保有完整的备份方案。