Tablespace free space

最新推荐文章于 2025-12-01 09:45:03 发布

原创最新推荐文章于 2025-12-01 09:45:03 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #dba #数据库

DBA 专栏收录该内容

1069 篇文章

订阅专栏

本文介绍如何通过SQL脚本查看Oracle数据库中各表空间的使用情况，包括总空间、已用空间和剩余空间，以及空间使用百分比，帮助管理员监控和优化存储资源。

Tablespace free space can be viewed in V$TABLESPACE ✖
在这里插入图片描述

Tablespace free space can be viewed in DBA_TABLESPACES ✖
在这里插入图片描述

tbs.sql

--表空间使用率
set linesize 220 pagesize 10000
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
           to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
           to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
           to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
    FROM   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 = 'LOCAL' AND d.contents = 'TEMPORARY')
   ORDER  BY 4 DESC;

--temp
select  df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0)  "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM   dba_TEMP_files
        GROUP  BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace
        FROM   gV$temp_extent_pool
        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+);

/*
  A Simple scripts to show current space usage.
*/
set linesize 400
col tablespace_name format a20
col total_Mbytes format 99999999.99
col used_Mbytes format 99999999.99
col free_Mbytes format 99999999.99
col pct_free format 99999999.99
col allocation_type format a10 heading 'ALLOCATION|TYPE'
col segment_space_management format a15 heading 'SEGMENT_SPACE|MANAGEMENT'
col initial_extent format a10 heading 'INITIAL|EXTENT'
COLUMN DUMMY NOPRINT
COMPUTE SUM OF used_Mbytes ON DUMMY
COMPUTE SUM OF free_Mbytes ON DUMMY
COMPUTE SUM OF total_Mbytes ON DUMMY
BREAK ON DUMMY
select a.tablespace_name,c.allocation_type,c.segment_space_management,
        case mod(c.initial_extent,1024*1024) when 0 then c.initial_extent/1024/1024||'M'
                else c.initial_extent/1024||'K' end initial_extent,
        a.total_Mbytes,a.total_Mbytes - b.free_Mbytes used_Mbytes,b.free_Mbytes,
        trunc(b.free_Mbytes/a.total_Mbytes * 100,2) pct_free,null dummy
from (
        select tablespace_name,sum(bytes)/1024/1024 total_MBytes
        from dba_data_files
        group by tablespace_name
) a, (
        select tablespace_name,sum(bytes)/1024/1024 free_Mbytes
        from dba_free_space
        group by tablespace_name
) b, dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name(+)
/

/*
  A Simple scripts to show current space usage.
*/
col tablespace_name format a20
set pages 1000
col total_Mbytes format 99999999.99
col used_Mbytes format 99999999.99
col free_Mbytes format 99999999.99
col pct_free format 99999999.99
col allocation_type format a10 heading 'ALLOCATION|TYPE'
col segment_space_management format a15 heading 'SEGMENT_SPACE|MANAGEMENT'
col initial_extent format a10 heading 'INITIAL|EXTENT'
set linesize 400
set feedback off
SELECT *
  FROM (SELECT A.CON_ID,
               A.TABLESPACE_NAME,
               C.ALLOCATION_TYPE,
               C.SEGMENT_SPACE_MANAGEMENT,
               CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
                 WHEN 0 THEN
                  C.INITIAL_EXTENT / 1024 / 1024 || 'M'
                 ELSE
                  C.INITIAL_EXTENT / 1024 || 'K'
               END INITIAL_EXTENT,
               A.TOTAL_MBYTES,
               A.TOTAL_MBYTES - B.FREE_MBYTES USED_MBYTES,
               B.FREE_MBYTES,
               TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
               NULL DUMMY
          FROM (SELECT CON_ID,
                       TABLESPACE_NAME,
                       SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
                  FROM CDB_DATA_FILES
                 GROUP BY TABLESPACE_NAME, CON_ID) A,
               (SELECT CON_ID,
                       TABLESPACE_NAME,
                       SUM(BYTES) / 1024 / 1024 FREE_MBYTES
                  FROM CDB_FREE_SPACE
                 GROUP BY TABLESPACE_NAME, CON_ID) B,
               CDB_TABLESPACES C
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
           AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
           AND A.CON_ID = B.CON_ID
           AND A.CON_ID = C.CON_ID
        UNION ALL
        SELECT A.CON_ID,
               A.TABLESPACE_NAME,
               C.ALLOCATION_TYPE,
               C.SEGMENT_SPACE_MANAGEMENT,
               CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
                 WHEN 0 THEN
                  C.INITIAL_EXTENT / 1024 / 1024 || 'M'
                 ELSE
                  C.INITIAL_EXTENT / 1024 || 'K'
               END INITIAL_EXTENT,
               A.TOTAL_MBYTES,
               A.TOTAL_MBYTES - B.FREE_MBYTES USED_MBYTES,
               B.FREE_MBYTES,
               TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
               NULL DUMMY
          FROM (SELECT CON_ID,
                       TABLESPACE_NAME,
                       SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
                  FROM CDB_TEMP_FILES
                 GROUP BY TABLESPACE_NAME, CON_ID) A,
               (SELECT CON_ID,
                       TABLESPACE_NAME,
                       SUM(FREE_SPACE) / 1024 / 1024 FREE_MBYTES
                  FROM CDB_TEMP_FREE_SPACE
                 GROUP BY TABLESPACE_NAME, CON_ID) B,
               CDB_TABLESPACES C
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
           AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
           AND A.CON_ID = B.CON_ID
           AND A.CON_ID = C.CON_ID)
 ORDER BY CON_ID
/


WITH X AS
 (SELECT A.CON_ID,
         A.TABLESPACE_NAME,
         C.ALLOCATION_TYPE,
         C.SEGMENT_SPACE_MANAGEMENT,
         CASE MOD(C.INITIAL_EXTENT, 1024 * 1024)
           WHEN 0 THEN
            C.INITIAL_EXTENT / 1024 / 1024 || 'M'
           ELSE
            C.INITIAL_EXTENT / 1024 || 'K'
         END INITIAL_EXTENT,
         A.TOTAL_MBYTES AS TOTAL_MB,
         A.TOTAL_MBYTES - B.FREE_MBYTES USED_MB,
         B.FREE_MBYTES AS FREE_MB,
         TRUNC(B.FREE_MBYTES / A.TOTAL_MBYTES * 100, 2) PCT_FREE,
         NULL DUMMY
    FROM (SELECT CON_ID,
                 TABLESPACE_NAME,
                 SUM(BYTES) / 1024 / 1024 TOTAL_MBYTES
            FROM CDB_DATA_FILES
           GROUP BY TABLESPACE_NAME, CON_ID) A,
         (SELECT CON_ID,
                 TABLESPACE_NAME,
                 SUM(BYTES) / 1024 / 1024 FREE_MBYTES
            FROM CDB_FREE_SPACE
           GROUP BY TABLESPACE_NAME, CON_ID) B,
         CDB_TABLESPACES C
   WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
     AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
     AND A.CON_ID = B.CON_ID
     AND A.CON_ID = C.CON_ID
   ORDER BY A.CON_ID)
SELECT CON_ID,
       SUM(TOTAL_MB) AS TOTAL_MB,
       SUM(USED_MB) AS TOTAL_USED_MB,
       SUM(FREE_MB) AS TOTAL_FREE_MB
  FROM X
 GROUP BY CON_ID
/