本文（偏于管理）介绍一些DBA日常运维中不常使用，但是遇到特殊情况可以可以及时使用的SQL语句；
注：本文主要针对Oracle 11g版本
11g，检查表空间使用情况： select df.tablespace_name "表空间名", totalspace - freespace "used_mb", totalspace "总空间M", freespace "剩余空间M", round((1 - freespace / totalspace) * 100, 2) "使用率%" from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name(+) order by round((1 - freespace / totalspace) * 100, 2) desc / 表空间FSFI（自由空间碎片索引）值查询，FSFI最大值为100即为一个理想的单文件表空间： select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by 1 / 检查数据库表空间碎片量（表空间管理模式分为LOCAL和DICTIONARY）： select a.tablespace_name ,count(1) 碎片量 from dba_free_space a,dba_tablespaces b where a.tablespace_name =b.tablespace_name and b.extent_management = 'LOCAL' group by a.tablespace_name having count(1) >20 order by 2 / 表空间碎片整理： alter tablespace users coalesce; 查询表空间的自由空间： select a.file_id "FileNo",a.tablespace_name "Tablespace_name", a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used", sum(nvl(b.bytes,0)) "Free",sum(nvl(b.bytes,0))/a.bytes*100 "%free" from dba_data_files a, dba_free_space b where a.file_id=b.file_id(+) group by a.tablespace_name ,a.file_id,a.bytes order by a.tablespace_name / 检查Oracle系统撤销表空间使用情况（可了解UNDO TABLESPACE使用情况）： select TO_CHAR(MIN(Begin_Time),'DD HH24:MI:SS') "开始时间", TO_CHAR(MAX(End_Time),'DD HH24:MI:SS') "结束时间", SUM(Undoblks) "用撤消块数", SUM(Txncount) "事务执行块数", MAX(Maxquerylen) "查询最长秒", MAX(Maxconcurrency) "最高事务数", SUM(Ssolderrcnt) "ORA-01555次数", SUM(Nospaceerrcnt) "无可用空间数" from V$UNDOSTAT / 检查回收站内的相关对象： select * from ( select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name order by MB desc) where rownum <11 / 检查Oracle系统数据文件物理读情况（可了解目前数据文件的平衡情况）： Select sum(Decode(Name,'physical reads',value,0)) Dsk_Rds, sum(Decode(Name,'db block gets',value,0)) Blk_Gts, sum(Decode(Name,'consistent gets',value,0)) Con_Gts, ((1-(sum(Decode(Name,'physical reads',value,0))/ (sum(Decode(Name,'db block gets',value,0))+ sum(Decode(Name,'consistent gets',value,0)))))*100) Hit_Rate From V$sysstat / 用SQL查出当前的trace文件名： SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' AS "trace_file_name" FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d / 或： select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace from v$process a,v$session b,v$parameter c,v$instance d where a.addr=b.paddr and b.audsid=userenv('sessionid') and c.name='user_dump_dest' / 查看block情况（所在文件、块、行；bbed用） select rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_row_number(rowid) rowno from d / 或（对于大表）： select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='SYS' and segment_name='D' / 后续继续补充；--------------------------------------------------------------------------------------------
版权所有，转载请注明作者及原文链接，否则追究法律责任!
QQ: 584307876
作者: Seven
原文链接: http://blog.csdn.net/sunjiapeng/article/details/8968222
邮箱: seven_ginna@foxmail.com转载于:https://www.cnblogs.com/seven-dba/archive/2013/05/24/4354913.html
相关资源：JAVA上百实例源码以及开源项目