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_file
  FROM dba_tablespace_usage_metrics d
 ORDER 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_file
  FROM 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 a10

SELECT 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 a10

SELECT 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     vdb
 WHERE 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     vdb
 WHERE 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     vdb
 WHERE 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 a30

SELECT 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_text
  FROM v$wait_chains c
  LEFT OUTER JOIN dba_objects o
    ON (row_wait_obj# = object_id)
  JOIN gv$session sess
 USING (sid)
  LEFT OUTER JOIN v$sql SQL
    ON (sql.sql_id = sess.sql_id AND sql.child_number = sess.sql_child_number)
CONNECT BY PRIOR sid = blocker_sid
       AND PRIOR sess_serial# = blocker_sess_serial#
       AND PRIOR instance = blocker_instance
 START 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.spid
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.background is null
   and b.sid = 2721
   and b.serial# = 4317;

## 关闭连接当前数据库的监听进程
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

在线日志切换

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

SELECT
    SUBSTR(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
FROM
  gv$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,
       profile
  FROM dba_users
 WHERE 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.profile
  FROM all_users a,
       dba_users b
 WHERE a.user_id = b.user_id
   AND 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) used
  from 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;' sql
  FROM dba_data_files a,
       (SELECT file_id,
               MAX(block_id + blocks - 1) hwm
          FROM dba_extents
         GROUP BY file_id) b,
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'db_block_size') c
 WHERE 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 数据库的运维效率，打造更稳定高效的数据库环境！

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