출처 - http://kalipso.tistory.com/80 

출처 - http://mentor75.tistory.com/entry/ORACLE-TABLE-SPACE-%EC%82%AC%EC%9A%A9%EB%9F%89-%ED%99%95%EC%9D%B8%EC%BF%BC%EB%A6%AC

출처 - https://kldp.org/node/34801

출처 - http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle

 

1. 테이블스페이스 정보 조회

SELECT * FROM DBA_TABLESPACES;

 

 

2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)

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;

 

조회결과를 다음과 같이 살펴볼 수 있다.

TABLESPACE명 총용량(TotalMB) 사용용량(UsedMB) 여유용량(FreeMB) 사용율(Used%)
SYSAUX 1024 514.4 509.6 50.23
SYSTEM 1024 390 634 38.09
TS_SEND_DATA 70960 59890.6 11069.4 84.4
TS_SEND_TEMP 1024 0.1 1023.9 0.01
UNDOTBS1 4518 55.7 4462.3 1.23
USERS 100 0.1 99.9 0.1

 

 

3. 테이블스페이스별 현황 확인 쿼리문(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;

 

TABLESPACE_NAME FILE_NAME MBYTES USE_MBYTES
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF 1464320 1458816
SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF 471040 450048
USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF 5120 3328
TEST C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST 20733952 18843264
EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF 102400 79552
UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF 6917120 17856
ORCL C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ORCL 1048576 1600

 

 

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

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;

 

테이블스페이스명 파일경로 사용공간 여유공간 총크기 여유공간
TS_TEST_DATA /oradata/TEST/ts_test_data02.dbf 16148332544 5326503936 21474836480   24.80%
TS_TEST_DATA /oradata/TEST/ts_test_data03.dbf 16073621504 5401214976 21474836480   25.15%
TS_TEST_DATA /oradata/TEST/ts_test_data.dbf 30577852416 879427584 31457280000    2.80%
SYSAUX /oradata/TEST/sysaux01.dbf 539361280 534380544 1073741824   49.77%
USERS /oradata/TEST/users01.dbf 65536 104792064 104857600   99.94%
SYSTEM /oradata/TEST/system01.dbf 408944640 664797184 1073741824   61.91%
UNDOTBS1 /oradata/TEST/undotbs01.dbf 53149696 4684316672 4737466368   98.88%
TS_TEST_TEMP /oradata/TEST/ts_test_temp.dbf 65536 1073676288 1073741824   99.99%

 

 

5. 테이블 용량 조회

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
SELECT l.table_name, l.owner, s.bytes
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

 

블로그 이미지

연미남편 nineDeveloper

안녕하세요 현직 개발자 입니다 ~ 빠르게 변화하는 세상에 뒤쳐지지 않도록 우리모두 열심히 공부합시다 ~! 개발공부는 넘나 재미있는 것~!

댓글을 달아 주세요