ORACLE之DBA常用数据库查询

1. 数据库信息
   1. 数据库概要

select a.name "DB Name",

       e.global_name "Global Name",

       c.host_name "Host Name",

       c.instance_name "Instance Name" ,

       DECODE(c.logins,'RESTRICTED','YES','NO') "Restricted Mode",

       a.log_mode  "Archive Log Mode"

FROM v$database a, v$version b, v$instance c,global_name e

WHERE b.banner LIKE '%Oracle%';

1. 1. 参数文件(是spfile还是pfile)

select nvl(value,'pfile') "Parameter_File"

from v$parameter where Name='spfile';

1. 1. 非默认的参数

select name, rtrim(value) "pvalue"

from v$parameter

where isdefault = 'FALSE'

order by name;

1. 1. 控制文件及其状态

select Name,Status from v$controlfile;

1. 1. 数据库版本信息

select * from v$version;

1. 1. 数据库组件（true：已安装，false：未安装）

SELECT PARAMETER, VALUE FROM V$OPTION;

1. 1. 实例信息

select instance_name,host_name,version,status,database_status from v$instance;

1. 1. NLS参数设置

SELECT * FROM NLS_Database_Parameters;

1. 1. 已装载的产品选项

select COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry;

1. 1. 数据库的并发数

select count(*) as "并发数" from v$session where status='ACTIVE';

1. 1. 数据库Session连接数

select count(*) as "连接数" from v$session;

1. 1. 数据库总大小（GB）

select round(sum(space)) "总容量/Gb"

  from (select sum(bytes) / 1024 / 1024 / 1024 space

          from dba_data_files

        union all

        select nvl(sum(bytes) / 1024 / 1024 / 1024, 0) space

          from dba_temp_files

        union all

        select sum(bytes) / 1024 / 1024 / 1024 space from v$log);

1. 1. 数据库服务器运行的操作系统

select PLATFORM_NAME from v$database;

1. 1. DBID

select dbid from v$database;

1. 1. Flashback是否启动

select decode(flashback_on,'NO','未启用','启用') as "闪回模式" from v$database;

1. 存储结构、表空间、数据文件
   1. 表空间及数据文件

select tablespace_name,file_name,

bytes/1024/1024 "Total Size(MB)",autoExtensible "Auto"

from dba_data_files

order by tablespace_name,file_id;

1. 1. 表空间状态及其大小使用情况

SELECT  d.tablespace_name "Name", d.status "Status", d.contents "Type",

        ROUND(NVL(a.bytes / 1024 / 1024, 0), 2) "Size (MB)",

        ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 /1024, 2) "Used (MB)",

        ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used%",

        ROUND(NVL(a.maxbytes / 1024 / 1024, 0), 2) "Max Size (MB)",

        DECODE(NVL(a.maxbytes,0), 0, 0, ROUND(NVL(a.maxbytes - a.bytes, 0) / 1024 / 1024, 2)) "Unused (MB)",

        DECODE(NVL(a.maxbytes,0), 0, 0, ROUND((1 - NVL(a.bytes / a.maxbytes, 0))*100, 2)) "Unused%"

  FROM sys.dba_tablespaces d,

      (SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes

         FROM dba_data_files GROUP BY tablespace_name

       UNION ALL

       SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes

         FROM dba_temp_files GROUP BY tablespace_name) a,

      (SELECT tablespace_name, SUM(bytes) bytes

         FROM dba_free_space GROUP BY tablespace_name

       UNION ALL

       SELECT tablespace_name, SUM(bytes_free) bytes

         FROM gv$temp_space_header GROUP BY tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

      AND d.tablespace_name = f.tablespace_name(+);

1. 1. 数据文件状态及其大小使用情况

SELECT  a.tablespace_name "TableSpace Name", a.File_Name "File Name",

        a.status "Status", a.AutoExtensible "Auto",

        round(NVL(a.bytes / 1024 / 1024, 0),1) "Size (MB)",

        round(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, 1) "Used (MB)",

        round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used %"

FROM dba_data_files a,

  (select file_id, sum(bytes) bytes

   from dba_free_space group by File_id) f

WHERE a.file_id=f.file_id(+)

order by a.tablespace_name,a.File_id;

1. 1. 不使用临时文件的临时表空间

select tablespace_name,contents from dba_tablespaces

where contents='TEMPORARY' and tablespace_name not in

  (select tablespace_name from dba_temp_files);

1. 1. 无效的数据文件(offline)

select f.tablespace_name,f.file_name,d.status

from dba_data_files f,v$datafile d

where d.status='OFFLINE' and f.file_id=File#(+);

1. 1. 处于恢复模式的文件

select f.tablespace_name,f.file_name

from dba_data_files f, v$recover_file r

where f.file_id=r.file#;

1. 1. 含有50个以上的Extent且30%以上碎片的表空间

select s.tablespace_name,

       round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented,

       s.seg_count segments, f.hole_count holes

from  (Select tablespace_name, count(*) seg_count

       from   dba_segments group by tablespace_name) s,

      (Select   tablespace_name, count(*) hole_count

       from     dba_free_space  group by tablespace_name) f

where s.tablespace_name = f.tablespace_name

      and s.tablespace_name in (Select tablespace_name

           from dba_tablespaces where contents = 'PERMANENT')

             And s.tablespace_name not in ('SYSTEM')

      and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30

      and s.seg_count > 50;

1. 1. 表空间上的I/O分布

SELECT  t.name   ts_name,

        f.name  file_name,

        s.phyrds phy_reads,

        s.phyblkrd phy_blockreads,

        s.phywrts phy_writes,

        s.phyblkwrt phy_blockwrites

FROM    gv$tablespace t,

        gv$datafile f,

        gv$filestat s

WHERE   t.ts# = f.ts#

        and

        f.file# = s.file#

ORDER BY s.phyrds desc, s.phywrts desc;

1. 1. 数据文件上的I/O分布

Select   ts.NAME "Table Space", D.NAME "File Name",

         FS.PHYRDS "Phys Rds",

         decode(fstot.sum_ph_rds,  0, 0,

                 round(100 * FS.PHYRDS    / fstot.sum_ph_rds,  2)) "% Phys Rds",

         FS.PHYWRTS "Phys Wrts",

         decode(fstot.sum_ph_wrts, 0, 0,

                 round(100 * FS.PHYWRTS   / fstot.sum_ph_wrts, 2)) "% Phys Wrts"

FROM   V$FILESTAT FS, V$DATAFILE d, V$tablespace ts,

      (select sum(phyrds)   sum_ph_rds, sum(phywrts)   sum_ph_wrts,

              sum(phyblkrd) sum_bl_rds, sum(phyblkwrt) sum_bl_wrts

       from   V$filestat) fstot

WHERE  D.FILE# = FS.FILE# AND D.TS#   = TS.TS#;

1. 1. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments

Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT,

       ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)"

FROM   DBA_SEGMENTS

WHERE  ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR

     (ROUND(100 * NEXT_EXTENT / BYTES) >= 200))

  AND    SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')

order by 2,3,1;