테이블스페이스의 빈 공간 찾기

테이블스페이스의 빈 공간 찾기

"ORA-01536: space quota exceeded for tablespace"로 인해 어플리케이션이 여러 번 실패하였습니다.또한 테이블스페이스의 빈 공간을 정기적으로 체크하고 일정 수준 이하로 떨어지면 경보를 발령함으로써 이를 방지하고 싶습니다.

테이블스페이스에 빈 공간이 얼마나 남았는지 알 수 있는 방법이 있나요?

(나는 DBA가 아니다) 조사를 좀 한 후 다음을 시도했습니다.

select max_bytes-bytes from user_ts_quotas;

select sum(nvl(bytes,0)) from user_free_space;

그러나 이러한 쿼리는 전혀 다른 결과를 반환합니다.

이 쿼리를 사용합니다.

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

훨씬 더 정확한 SQL 문

SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name, 
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES, 
                  SUM (BYTES) BYTES, 
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name 
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

사이즈를 체크하는 방법은 여러 가지가 있습니다만, 개발자로서 메타 테이블을 쿼리할 수 있는 접근권이 별로 없기 때문에 이 솔루션은 매우 간단합니다(주의: ORA-01653 'The ORA-01653 error is cause a tablespace'라는 에러메시지가 뜨면 ORA-01653 error is cause to a tablespace).

--Size of All Table Space

--1. Used Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME
--2. Free Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME

--3. Both Free & Used
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

감사해요.

이것은 제가 접한 것과 같은 가장 단순한 쿼리 중 하나이며 모니터링에도 사용됩니다.

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

Oracle 테이블스페이스에 대한 전체 기사:테이블스페이스

이것도 꽤 좋다

clear breaks
clear computes

Prompt
Prompt Tablespace Usage
Prompt

SET lines 120 pages 500 
col percent_used format 999.99

SELECT a.TABLESPACE_NAME,
  NVL(ROUND((a.BYTES  /1024)/1024/1024,2),2) GB_TOTAL,
  NVL(ROUND((b.BYTES  /1024)/1024/1024,2),2) GB_FREE,
  NVL(ROUND((b.largest/1024),2),0) KB_Chunk,
  NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used
FROM
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),0) BYTES
  FROM dba_data_files
  GROUP BY TABLESPACE_NAME
  ) a,
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),1) BYTES ,
    NVL(MAX(BYTES),1) largest
  FROM dba_free_space
  GROUP BY TABLESPACE_NAME
  ) b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;

산출량

TABLESPACE_NAME                  GB_TOTAL    GB_FREE   KB_CHUNK PERCENT_USED
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                                .84        .02       9216        97.36
SYSAUX                                .57        .05      32768        91.10
UNDOTBS1                              .06        .05      36864        23.13
USERS                                   0          0       4096        20.00

다음 쿼리는 테이블스페이스의 빈 공간(MB)을 찾는 데 도움이 됩니다.

select tablespace_name , sum(bytes)/1024/1024 from dba_free_space group by tablespacE_name order by 1;

다음 쿼리를 실행하여 테이블스페이스 빈 공간을 확인할 수 있습니다.

SELECT dfq.tablespace_name AS "Tablespace Name",
       dfq.totalspace AS "Total Size MB",
       (dfq.totalspace - dsq.totalusedspace) "Free Space MB",
       ROUND(100 * ((dfq.totalspace - dsq.totalusedspace) / dfq.totalspace))||'%' "Free Space %"
  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
          FROM dba_data_files
         GROUP BY tablespace_name) dfq,
       (SELECT tablespace_name,
               ROUND(SUM(bytes) / (1024 * 1024)) AS totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) dsq
 WHERE dfq.tablespace_name = dsq.tablespace_name(+);

위의 쿼리에서는 각 테이블스페이스의 총 크기, MB 단위의 여유 공간 및 전체 크기에서 백분율 단위의 여유 공간을 선택했습니다.따라서 이 백분율 열을 사용하여 사용 가능한 공간 백분율이 20%에 도달한 경우 또는 원하는 백분율에 도달한 경우 경보를 추적하고 발생시킬 수 있습니다.

제가 틀리지 않는 한 위의 코드는 할당되지 않은 공간을 고려하지 않으므로 하드 제한에 도달하는 시기를 알고 싶다면 maxbytes를 사용해야 합니다.

아래 코드가 그런 것 같아요.빈 공간은 "freespace" + 할당되지 않은 공간으로 계산됩니다.

select 
     free.tablespace_name,
     free.bytes,
     reserv.maxbytes,
     reserv.bytes,
     reserv.maxbytes - reserv.bytes + free.bytes "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(maxbytes) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) free
where free.tablespace_name = reserv.tablespace_name;

column pct_free format 999.99
select
     used.tablespace_name,
     (reserv.maxbytes - used.bytes)*100/reserv.maxbytes pct_free,
     used.bytes/1024/1024/1024 used_gb,
     reserv.maxbytes/1024/1024/1024 maxgb,
     reserv.bytes/1024/1024/1024 gb,
     (reserv.maxbytes - used.bytes)/1024/1024/1024 "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(greatest(maxbytes,bytes)) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_segments group by tablespace_name) used
where used.tablespace_name = reserv.tablespace_name
order by 2
/

또한 디스크 상의 파일 크기를 보면 테이블 공간 사용량에 대한 대략적인 정보를 얻을 수 있습니다.

DB는 최대 익스텐트로 작성되며, 각 dbf 파일은 32기가까지만 확장할 수 있습니다.따라서 마지막 dbf 파일이 32기가에 도달하면 용량이 부족해지고 다른 파일을 추가해야 합니다.

다음은 Oracle SQL 개발자가 테이블 영역 보기에서 사용하는 쿼리입니다.

select a.tablespace_name as "Tablespace Name",
       round(a.bytes_alloc / 1024 / 1024) "Allocated (MB)",
       round(nvl(b.bytes_free, 0) / 1024 / 1024) "Free (MB)",
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) "Used (MB)",
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Free",
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Used",
       round(maxbytes/1024 / 1024) "Max. Bytes (MB)"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select 
       h.tablespace_name as tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 2;

테이블스페이스라고 하는 스크립트를 사용할 수 있습니다.sh 이 편리한 번들 내:http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

언급URL : https://stackoverflow.com/questions/7672126/find-out-free-space-on-tablespace