准确查询表空间使用情况

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

准确查询表空间使用情况

发表评论

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
<think>我们有两个需求:1. 查询普通表空间使用率;2. 查询临时表空间使用率。根据引用[2]和引用[3]提供的方法,我们可以分别编写SQL。 注意:引用[2]中提供了普通表空间使用率的查询,引用[3]提供了临时表空间使用率的查询。 普通表空间使用率查询(参考引用[2]并稍作调整): - 使用dba_data_files获取表空间总大小 - 使用dba_free_space获取表空间空闲大小 - 计算已用空间和使用率 临时表空间使用率查询(参考引用[3]): - 使用dba_temp_files获取临时表空间总大小 - 使用v$sort_usage获取临时表空间已用空间 另外,根据用户上一次的问题,我们曾提供了一个查询,这次可以结合引用[2]的写法进行优化(因为引用[2]的写法是常见的连接方式)。 我们将分别提供两个查询,并解释。 同时,注意权限:需要DBA权限。 步骤: 1. 普通表空间使用率查询 2. 临时表空间使用率查询 普通表空间使用率查询SQL(优化写法): ```sql SELECT total.tablespace_name AS "表空间名称", ROUND(total.MB, 2) AS "总容量(MB)", ROUND(total.MB - free.MB, 2) AS "已用空间(MB)", ROUND(free.MB, 2) AS "剩余空间(MB)", ROUND((1 - free.MB / total.MB) * 100, 2) AS "使用率(%)" FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total WHERE free.tablespace_name = total.tablespace_name AND total.tablespace_name NOT IN ('SYSTEM','SYSAUX') -- 排除系统表空间 ORDER BY "使用率(%)" DESC; ``` 但是,上述写法可能会遗漏没有空闲空间表空间(即free中没有记录),所以使用左外连接更安全。另外,引用[2]的写法是内连接,如果某个表空间没有空闲记录(即完全用完),则不会显示剩余空间为0的情况。因此,我们可以使用NVL来避免。 改进:使用左外连接,并处理NULL值。 由于引用[2]的写法可能遗漏,我们采用以下写法(更安全): ```sql SELECT df.tablespace_name AS "表空间名称", ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS "总容量(MB)", ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024, 2) AS "已用空间(MB)", ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024, 2) AS "剩余空间(MB)", ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) * 100 / SUM(df.bytes), 2) AS "使用率(%)" FROM dba_data_files df LEFT JOIN (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) fs ON df.tablespace_name = fs.tablespace_name WHERE df.tablespace_name NOT IN ('SYSTEM','SYSAUX') -- 排除系统表空间 GROUP BY df.tablespace_name ORDER BY "使用率(%)" DESC; ``` 临时表空间使用率查询(参考引用[3]): ```sql SELECT D.tablespace_name AS "表空间名称", ROUND(D.space, 2) AS "总容量(MB)", ROUND(F.used_space, 2) AS "已用空间(MB)", ROUND(D.space - F.used_space, 2) AS "剩余空间(MB)", ROUND(Nvl(F.used_space, 0) / D.space * 100, 2) AS "使用率(%)" FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS space FROM dba_temp_files GROUP BY tablespace_name) D LEFT JOIN (SELECT tablespace, SUM(blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024) AS used_space FROM v$sort_usage GROUP BY tablespace) F ON D.tablespace_name = F.tablespace WHERE D.tablespace_name IN ('TEMP', 'TEMP1') -- 这里填写需要查询的临时表空间名称 ORDER BY "使用率(%)" DESC; ``` 注意:在临时表空间查询中,v$sort_usage中的blocks乘以的是每个块的大小(字节数),而块大小可以通过查询v$parameter获取(db_block_size)。但是,引用[3]中直接使用了8192(即8KB),这假设块大小为8K。为了通用性,我们可以动态获取块大小。 但是,由于v$parameter可能没有直接给出每个块的大小,我们可以使用以下方式之一: 1. 使用`SHOW PARAMETER db_block_size;` 获取,但这是在SQL*Plus中,不能在SQL语句中使用。 2. 在SQL中查询v$parameter,如:`(SELECT value FROM v$parameter WHERE name = 'db_block_size')`,但注意,如果多个实例,可能需要考虑。 然而,在临时表空间使用情况查询中,更常见的做法是直接使用8192(因为大部分数据库块大小是8K),但为了准确,我们使用动态获取块大小的方法。 另外,注意:临时表空间可能有多个,所以通过IN条件指定。 但是,如果不知道临时表空间名称,可以先查询dba_temp_files获取所有临时表空间。 我们可以先不指定,然后查询所有临时表空间: ```sql SELECT D.tablespace_name AS "表空间名称", ROUND(D.space, 2) AS "总容量(MB)", ROUND(Nvl(F.used_space, 0), 2) AS "已用空间(MB)", ROUND(D.space - Nvl(F.used_space, 0), 2) AS "剩余空间(MB)", ROUND(Nvl(F.used_space, 0) * 100 / D.space, 2) AS "使用率(%)" FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS space FROM dba_temp_files GROUP BY tablespace_name) D LEFT JOIN (SELECT tablespace, SUM(blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024) AS used_space FROM v$sort_usage GROUP BY tablespace) F ON D.tablespace_name = F.tablespace ORDER BY "使用率(%)" DESC; ``` 但是,注意:v$sort_usage只记录当前正在使用的临时空间,当临时操作结束后,空间会被释放,所以这个使用率是当前使用情况。 根据以上,我们提供两个查询:普通表空间和临时表空间。 另外,引用[1]中提到,当表空间使用率超过90%时,需要采取措施。 下面,我将分别给出两个查询的完整SQL,并附上说明。 注意:如果用户没有DBA权限,可能无法访问这些视图。普通用户可以使用user_或all_开头的视图,但只能查看自己有权限的表空间。 但是,问题要求查看整个数据库的表空间使用率,所以需要DBA权限。 最终回答: 我们将提供两个查询: 1. 普通表空间使用率查询 2. 临时表空间使用率查询 并生成相关问题。</think>### Oracle 表空间使用率查询方法 以下是查询 Oracle 表空间使用率的完整解决方案,包含普通表空间和临时表空间的查询方法: #### 1. 普通表空间使用率查询 ```sql SELECT total.tablespace_name AS "表空间名称", ROUND(total.MB, 2) AS "总容量(MB)", ROUND(total.MB - free.MB, 2) AS "已用空间(MB)", ROUND(free.MB, 2) AS "剩余空间(MB)", ROUND((1 - free.MB / total.MB) * 100, 2) || '%' AS "使用率" FROM ( SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name ) free, ( SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name ) total WHERE free.tablespace_name = total.tablespace_name AND total.tablespace_name NOT IN ('SYSTEM','SYSAUX') -- 排除系统表空间 ORDER BY "使用率" DESC; ``` **查询结果示例**: ``` 表空间名称 | 总容量(MB) | 已用空间(MB) | 剩余空间(MB) | 使用率 ----------------------------------------------- USER_DATA | 10240.00 | 9216.00 | 1024.00 | 90.00% INDEX_TS | 5120.00 | 3584.00 | 1536.00 | 70.00% ``` #### 2. 临时表空间使用率查询 ```sql SELECT D.tablespace_name AS "表空间名称", ROUND(D.space, 2) AS "总容量(MB)", ROUND(NVL(F.used_space, 0), 2) AS "已用空间(MB)", ROUND(D.space - NVL(F.used_space, 0), 2) AS "剩余空间(MB)", ROUND(NVL(F.used_space, 0) / D.space * 100, 2) AS "使用率(%)" FROM ( SELECT tablespace_name, SUM(bytes) / (1024 * 1024) AS space FROM dba_temp_files GROUP BY tablespace_name ) D LEFT JOIN ( SELECT tablespace, SUM(blocks * 8192) / (1024 * 1024) AS used_space FROM v$sort_usage GROUP BY tablespace ) F ON D.tablespace_name = F.tablespace; ``` #### 关键视图说明: 1. **`dba_data_files`** 存储所有数据文件信息,包含表空间物理文件大小[^1] 2. **`dba_free_space`** 记录表空间中的空闲空间信息[^2] 3. **`dba_temp_files`** 存储临时表空间文件信息 4. **`v$sort_usage`** 监控临时段使用情况[^3] #### 注意事项: 1. 需要 **DBA 权限** 才能访问系统视图 2. 使用率超过 90% 的表空间需及时处理[^1] 3. 临时表空间使用率反映当前会话的临时段使用情况 4. 系统表空间(SYSTEM/SYSAUX)通常不参与常规监控 5. 块大小默认为 8192 字节(8KB),可根据实际参数调整 > 当表空间使用率接近 100% 时,会导致 "ORA-01653: unable to extend table" 错误,需立即处理[^1]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值