Oracle 10g 如何查看表空间及剩余空间

最新推荐文章于 2024-07-02 16:49:05 发布

cheug

最新推荐文章于 2024-07-02 16:49:05 发布

阅读量2.7k

点赞数

分类专栏： Oracle 文章标签： oracle manager sql 工具

本文链接：https://blog.youkuaiyun.com/cheug/article/details/7910514

版权

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文使用SQL语句和Oracle数据库工具追踪提取了表空间的状态、名称、类型、扩展管理方式以及磁盘使用情况，包括大小、已用空间和使用百分比。同时，对于临时表空间进行了特别关注，展示了如何获取并分析其使用情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.用SQL语句：此语句是用Toad 的SQL Monitor 工具追踪提取的

SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,
               '99999999.999') || '/' ||
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
               '990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
          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.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
       TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' ||
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) bytes
          from v$temp_extent_pool
         group by tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management like 'LOCAL'
   AND d.contents like 'TEMPORARY'

2 用Enterprise Manager 主控台