오라클 TableSpace 별 사용용량 구하는 SQL

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : Oracle TableSpace 별 사용용량 구하는 SQL , 테이블스페이스 용량

SQL> 
col "TOTAL SIZE" format 999,999,999,999
col "USED SIZE" format 999,999,999,999
col "FREE SIZE" format 999,999,999,999
col "MAX FREE" format 999,999,999,999
col "TS Name" for a20
col "Used rate" for a10
set  trimspool on newpage 1 linesize 120 pages 1000 heading on underline on echo off
 
 
select t.tn  "TS Name", 
       round(t.sizes,2) "TOTAL SIZE", 
       (round(t.sizes,2) - round(f.sizes,2))"USED SIZE",
       round(f.sizes,2) "FREE SIZE", 
       round(f.msizes,2) "Max Free", 
       round(((t.sizes - f.sizes) /t.sizes) * 100,2)||'%' "Used rate"
 from ( select tablespace_name tn, SUM(bytes) Sizes 
        from dba_data_files 
        group by tablespace_name) t, 
      (select tablespace_name tn, SUM(bytes) sizes, 
              MAX(bytes) msizes 
       from dba_free_space 
       group by tablespace_name) f 
      WHERE t.tn=f.tn 
      order by t.tn;
 
TS Name            TOTAL SIZE         USED SIZE          FREE SIZE     Max Free Used rate
-------------------- ---------------- ---------------- ---------------- ---------------- ----------
MOVE_IMSI        1,073,741,824         1,048,576      1,072,693,248    1,072,693,248 .1%
SYSAUX              732,954,624       558,825,472        174,129,152        5,242,880 76.24%
SYSTEM              732,954,624       292,421,632        440,532,992      224,395,264 39.9%
UNDOTBS1          208,666,624       149,291,008         59,375,616       53,477,376 71.55%
UNDOTBS2          732,954,624       732,823,552        131,072       65,536 99.98%
USERS              732,954,624       158,728,192        574,226,432      509,607,936 21.66%

또는

SQL> 
set lines 200
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') , t.tn, t.sizes Tot, (t.sizes-f.sizes) Used, ROUND((t.sizes-f.sizes)/t.sizes*100,2) UsePct,
NVL(TO_NUMBER(f.sizes),0) Free, 100-ROUND((t.sizes-f.sizes)/t.sizes*100,2) FreePct
FROM (SELECT tablespace_name tn, SUM(bytes)/1024/1024 Sizes
FROM dba_data_files
GROUP BY tablespace_name) t, 
(SELECT tablespace_name tn, SUM(bytes)/1024/1024 Sizes
FROM SYS.dba_free_space 
GROUP BY tablespace_name) f
WHERE t.tn = f.tn(+)
ORDER BY t.tn;
 
TO_CHAR( TN                       TOT     USED      USEPCT       FREE    FREEPCT
-------- ------------------------------ ---------- ---------- ---------- ---------- ----------
20190226 JSHTS                      2048     3.4375         .17  2044.5625     99.83
20190226 SYSAUX                    500    480.125       96.03     19.875      3.97
20190226 SYSTEM                    810   800.9375       98.88     9.0625      1.12
20190226 UNDOTBS1                70    15.25       21.79      54.75     78.21
20190226 USERS                     5        1          20      4        80
 
5 rows selected.

참고로 Table의 사용량을 체크하려면 아래 같이 작성하면 된다.

SQL> 
set lines 200
col owner for a10
col segment_name for a20
select owner,segment_name,segment_type,sum(bytes)/1024/1024 as MB
from dba_segments
where owner=UPPER('&OWNER')
GROUP BY owner,segment_name,segment_type;
 
Enter value for owner: jsh
 
OWNER                   SEGMENT_NAME        SEGMENT_TYPE           MB
------------------------------ -------------------- ------------------ ----------
JSH                   TEST_BASIC        TABLE               21
JSH                   TEST_PK            INDEX PARTITION           15
JSH                   TEST            TABLE PARTITION           20
JSH                   EMP2            TABLE            .0625
JSH                   PK_EMP            INDEX            .0625
JSH                   DEPT            TABLE            .0625
JSH                   PK_DEPT            INDEX            .0625
JSH                   EMP3            TABLE            .0625
JSH                   EMP            TABLE            .0625
JSH                   TEST_OLTP        TABLE            6
JSH                   EMP1            TABLE            .0625
 
11 rows selected.

Owner에는 해당 User명을 입력하면 된다.

Table만 현재 사용량을 체크하려면 segment_type = 'TABLE'이라고 Where 절에 추가한다.

Index Size를 체크하려면 segment_type = 'INDEX'라고 Where 절에 추가하면 된다.

참조 : http://kr.blog.yahoo.com/cheon0906/folder/3348213.html

http://egloos.zum.com/nachnine/v/3397916