728x90

출처 - 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%)
SYSAUX1024514.4509.650.23
SYSTEM102439063438.09
TS_SEND_DATA7096059890.611069.484.4
TS_SEND_TEMP10240.11023.90.01
UNDOTBS1451855.74462.31.23
USERS1000.199.90.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_NAMEFILE_NAMEMBYTESUSE_MBYTES
SYSTEMC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF14643201458816
SYSAUXC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF471040450048
USERSC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF51203328
TESTC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST2073395218843264
EXAMPLEC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF10240079552
UNDOTBS1C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF691712017856
ORCLC:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ORCL10485761600

 

 

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.dbf16148332544532650393621474836480  24.80%
TS_TEST_DATA/oradata/TEST/ts_test_data03.dbf16073621504540121497621474836480  25.15%
TS_TEST_DATA/oradata/TEST/ts_test_data.dbf3057785241687942758431457280000   2.80%
SYSAUX/oradata/TEST/sysaux01.dbf5393612805343805441073741824  49.77%
USERS/oradata/TEST/users01.dbf65536104792064104857600  99.94%
SYSTEM/oradata/TEST/system01.dbf4089446406647971841073741824  61.91%
UNDOTBS1/oradata/TEST/undotbs01.dbf5314969646843166724737466368  98.88%
TS_TEST_TEMP/oradata/TEST/ts_test_temp.dbf6553610736762881073741824  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


반응형

+ Recent posts