3월 2017 | (주)누리웨어

2 03 2017

Oracle 용량확인

테이블스페이스 정보 조회 SELECT * FROM DBA_TABLESPACES; Oracle 테이블 스페이별 용량

select substr(a.tablespace_name,1,30) tablespace,
round(sum(a.total1)/1024/1024,1) "TotalMB",
round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
round(sum(a.sum1)/1024/1024,1) "FreeMB",
round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name,sum(bytes) total1,0,0,0
from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

select substr(a.tablespace_name,1,30) tablespace,

round(sum(a.total1)/1024/1024,1) "TotalMB",

round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

round(sum(a.sum1)/1024/1024,1) "FreeMB",

round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

from

(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name,sum(bytes) total1,0,0,0

from dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by tablespace;

테이블스페이스별 현황 확인 쿼리문(MB 단위)

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
(
SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT
FROM DBA_DATA_FILES E, DBA_FREE_SPACE F
WHERE E.FILE_ID = F.FILE_ID
GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
) A;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM

(

SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT

FROM DBA_DATA_FILES E, DBA_FREE_SPACE F

WHERE E.FILE_ID = F.FILE_ID

GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES

) A;

테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)

SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       (
         SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
         AND A.FILE_ID = B.FILE_ID;

SELECT A.TABLESPACE_NAME "테이블스페이스명",

A.FILE_NAME "파일경로",

(A.BYTES - B.FREE) "사용공간",

B.FREE "여유 공간",

A.BYTES "총크기",

TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"

FROM

(

SELECT FILE_ID,

TABLESPACE_NAME,

FILE_NAME,

SUBSTR(FILE_NAME,1,200) FILE_NM,

SUM(BYTES) BYTES

FROM DBA_DATA_FILES

GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)

) A,

(

SELECT TABLESPACE_NAME,

FILE_ID,

SUM(NVL(BYTES,0)) FREE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME,FILE_ID

) B

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME

AND A.FILE_ID = B.FILE_ID;

테이블 용량 조회

<span class="kwd">SELECT</span><span class="pln">
   owner</span><span class="pun">,</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln"> TRUNC</span><span class="pun">(</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span><span class="pun">)</span><span class="pln"> GB
</span><span class="kwd">FROM</span>
<span class="pun">(</span><span class="kwd">SELECT</span><span class="pln"> segment_name table_name</span><span class="pun">,</span><span class="pln"> owner</span><span class="pun">,</span><span class="pln"> bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_segments
</span><span class="kwd">WHERE</span><span class="pln"> segment_type </span><span class="kwd">in</span>  <span class="pun">(</span><span class="str">'TABLE'</span><span class="pun">,</span><span class="str">'TABLE PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_indexes i</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">index_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> i</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="kwd">in</span> <span class="pun">(</span><span class="str">'INDEX'</span><span class="pun">,</span><span class="str">'INDEX PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_lobs l</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">segment_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="kwd">IN</span> <span class="pun">(</span><span class="str">'LOBSEGMENT'</span><span class="pun">,</span><span class="str">'LOB PARTITION'</span><span class="pun">)</span>
<span class="kwd">UNION</span> <span class="kwd">ALL</span>
<span class="kwd">SELECT</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner</span><span class="pun">,</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">bytes
</span><span class="kwd">FROM</span><span class="pln"> dba_lobs l</span><span class="pun">,</span><span class="pln"> dba_segments s
</span><span class="kwd">WHERE</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">segment_name </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">index_name
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">owner </span><span class="pun">=</span><span class="pln"> l</span><span class="pun">.</span><span class="pln">owner
</span><span class="kwd">AND</span><span class="pln">   s</span><span class="pun">.</span><span class="pln">segment_type </span><span class="pun">=</span> <span class="str">'LOBINDEX'</span><span class="pun">)</span>
<span class="com">---WHERE owner in UPPER('&amp;owner')</span>
<span class="kwd">GROUP</span> <span class="kwd">BY</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln"> owner
</span><span class="kwd">HAVING</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)/</span><span class="lit">1024</span><span class="pun">/</span><span class="lit">1024</span> <span class="pun">&gt;</span> <span class="lit">10</span>  <span class="com">/* Ignore really small tables */</span>
<span class="kwd">ORDER</span> <span class="kwd">BY</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">)</span> <span class="kwd">desc</span>

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type in ('TABLE','TABLE PARTITION')

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type in ('INDEX','INDEX PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')

UNION ALL

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX')

---WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

3월, 2017

Oracle 용량확인