准确查询表空间使用情况

11g以前，一般查询表空间都是通过DBA_DATA_FILE和DBA_FREE_SPACE两个视图来查询。但从11g开始，如果你习惯看EM的话，会发现表空间使用率跟我们以前的sql查出来的结果有出入。这是因为EM采用了新的视图dba_tablespace_usage_metrics，针对自动扩展的表空间，得出来的结果就会不一样。

dba_tablespace_usage_metrics的used_space是已经分配的空间，对应v$filespace_usage的allocated_space字段，而对于非自动扩展的表空间，使用DBA_TABLESPACE_USAGE_METRICS视图，与传统脚本使用的DBA_DATA_FILE和DBA_FREE_SPACE查询的结果是一致的。

SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS from dba_data_files;

   FILE_ID TABLESPACE_NAME        AUT  MAXBLOCKS
---------- ------------------------------ --- ----------
     4 USERS              YES    4194302
     3 UNDOTBS1           YES    4194302
     2 SYSAUX             YES    4194302
     1 SYSTEM             YES    4194302


select TABLESPACE_NAME, sum(blocks)*8192/1024/1024 "SIZE (MB)" from dba_data_files
  2  where TABLESPACE_NAME='SYSTEM' group by tablespace_name;

TABLESPACE_NAME         SIZE (MB)
------------------------------ ----------
SYSTEM                    750

select TABLESPACE_NAME, TABLESPACE_SIZE*8192/1024/1024 "SIZE (MB)" from
DBA_TABLESPACE_USAGE_METRICS
  3  where tablespace_name='SYSTEM';

TABLESPACE_NAME         SIZE (MB)
------------------------------ ----------
SYSTEM                 13633.9375

通过上面的结果可以看到，对于自动扩展的system表空间来说，查出来的结果差距接近13g，这是因为DBA_TABLESPACE_USAGE_METRICS视图中的tablespace_size是dba_data_files的最大的块数。也就是dba_tablespace_usage_metrics的tablespace_size是datafile能增长到的最大值。

先看下DBA_TABLESPACE_USAGE_METRICS视图的定义

SYS@xb> set long 99999
SYS@xb> select dbms_metadata.get_ddl('VIEW','DBA_TABLESPACE_USAGE_METRICS') from dual;

DBMS_METADATA.GET_DDL('VIEW','DBA_TABLESPACE_USAGE_METRICS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE"
, "USED_PERCENT") AS
  SELECT  t.name,
    tstat.kttetsused,
    tstat.kttetsmsize,
    (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat      《== 这里发现一个问题，跟官方文档有点区别，一般表空间的数据来源不是v$filespace_usage 而是x$kttets
  WHERE
    t.online$ != 3 and
    t.bitmapped >> 0 and
    t.contents$ = 0 and
    bitand(t.flags, 16) >> 16 and
    t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped >> 0 and
     t.contents$ >> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped >> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#

这里tablespace_size跟dba_data_files的maxblock 并不一致

SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS,MAXBLOCKS*8192/1024/1024 "size(MB)" from dba_data_files;

   FILE_ID TABLESPACE_NAME        AUT  MAXBLOCKS   size(MB)
---------- ------------------------------ --- ---------- ----------
     4 USERS              YES    4194302 32767.9844
     3 UNDOTBS1           YES    4194302 32767.9844
     2 SYSAUX             YES    4194302 32767.9844
     1 SYSTEM             YES    4194302 32767.9844

SYS@xb> select tablespace_name,tablespace_size,tablespace_size*8192/1024/1024 "size(MB)" from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME            TABLESPACE_SIZE   size(MB)
------------------------------ --------------- ----------
SYSAUX                     1716806 13412.5469
SYSTEM                     1743686 13622.5469
TEMP                       1651420 12901.7188
UNDOTBS1                   1661788 12982.7188
USERS                      1648326 12877.5469

差异有点大，暂时不清楚是什么原因。个人认为最大block值应该约为32g比较准确，因为都是small file tablespace

关于临时表空间的使用，也许会碰到v$temp_space_header的temp usage怎么大于v$tempseg_usage(或v$sort_usage)的值呢？

select tablespace_name, sum(bytes_used/1024/1024) "gb_used"
from v$temp_space_header
  3  group by tablespace_name;

TABLESPACE_NAME           gb_used
------------------------------ ----------
TEMP                       29

select tablespace, (sum(blocks)*8192)/1024/1024/1024 "gb_used"
from v$tempseg_usage
  3  group by tablespace;

未选定行

视图v$sort_usage或者v$tempseg_usage(和v$sort_segment)给出了sort segment分配的正确信息，我们应该通过使用这三个表来查询当前临时空间的确切使用情况的。

但是，v$temp_space_header则是当临时空间使用最高的时候每个临时文件的多少块数，事实上，它展示了每个临时文件初始块的个数并非实际分配的块。v$sort_usage/v$tempseg_usage确切的反映了初始块中对每个事务分配了多少实际的sort extent。

另外，v$temp_space_header的信息是持久化的，即便重启也不会改变；而V$sort_segment and v$sort_usage不是持久的。

正确查看表空间的语句

SELECT /*+ first_rows */
 d.tablespace_name "TS NAME",
 NVL(a.bytes / 1024 / 1024, 0) "size MB",
 NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used MB",
 NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
 a.autoext "Autoextend",
 NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
 d.status "STAT",
 a.count "# of datafiles",
 d.contents "TS type",
 d.extent_management "EXT MGMT",
 d.segment_space_management "Seg Space MGMT"
  FROM sys.dba_tablespaces d,
       (select tablespace_name,
               sum(bytes) bytes,
               count(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT d.contents = 'UNDO'
   AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
   AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(t.bytes, 0) / 1024 / 1024,
       NVL(t.bytes / a.bytes * 100, 0),
       a.autoext,
       (NVL(a.bytes, 0) / 1024 / 1024 - NVL(t.bytes, 0) / 1024 / 1024),
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM sys.dba_tablespaces d,
       (select tablespace_name,
               sum(bytes) bytes,
               count(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          from dba_temp_files
         group by tablespace_name) a,
       (select ss.tablespace_name,
               sum((ss.used_blocks * ts.blocksize)) bytes
          from gv$sort_segment ss, sys.ts$ ts
         where ss.tablespace_name = ts.name
         group by ss.tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY'
   and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(u.bytes, 0) / 1024 / 1024,
       NVL(u.bytes / a.bytes * 100, 0),
       a.autoext,
       NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name,
               SUM(bytes) bytes,
               COUNT(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM (SELECT tablespace_name, sum(bytes) bytes, status
                  from dba_undo_extents
                 WHERE status = 'ACTIVE'
                 group by tablespace_name, status
                UNION ALL
                SELECT tablespace_name, sum(bytes) bytes, status
                  from dba_undo_extents
                 WHERE status = 'UNEXPIRED'
                 group by tablespace_name, status)
         group by tablespace_name) u
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = u.tablespace_name(+)
   AND d.contents = 'UNDO'
   AND d.tablespace_name LIKE '%%'
 ORDER BY 1

Oracle

#scripts

准确查询表空间使用情况

https://www.xbdba.com/2018/10/18/correct-query-tablespace-usage/

作者

xbdba

发布于

2018年10月18日

许可协议

Oracle的Central Inventory和Local inventory 上一篇

Oracle Block Checksum 下一篇