오라클 테이블스페이스 용량 조회, oracle tablespace 용량 조회

오라클 테이블스페이스 용량 조회, oracle tablespace 용량 조회

SQL/ORACLE 2014.02.03 11:13

출처 - 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