DBA不常用但及时可用的SQL语句集锦（1）

本文（偏于管理）介绍一些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