테이블 스페이스 용량확인

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : 테이블 스페이스 용량확인

용량확인

SQL> 
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
    select a.tablespace_name col0,
      b.bytes/1024/1024 col1,
      a.bytes/1024/1024 col3,
      b.max/1024/1024 col5,
      (b.bytes - a.bytes)/1024/1024 col2,
      (a.bytes/ b.bytes) * 100 col4
    from (
        select tablespace_name,
          sum(bytes) bytes
        from dba_free_space
        group by tablespace_name) a,
      (
        select tablespace_name,
          sum(bytes) bytes,
          sum(maxbytes) max
        from dba_data_files
        group by tablespace_name) b
    where b.tablespace_name=a.tablespace_name)
order by col4 asc;
 
TS Name         Size(MB)      Free(MB)        Used(MB)       Max(MB)      % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX            2,048.00      1,011.94        1,036.06           .00       49.41
SYSTEM              700.00    400.81          299.19           .00       57.26
USERS            2,048.00      1,468.13          579.88           .00       71.69
UNDOTBS1        1,000.00    966.13           33.88      1,000.00       96.61

 

 

단순히 할당 용량만 확인

SQL> 
col tsname for a20
col file_name for a60
select tablespace_name tsname, file_name, bytes/1024/1024 mb 
from dba_data_files 
order by tablespace_name;
 
TSNAME             FILE_NAME                                  MB
-------------------- ------------------------------------------------------------ ----------
SYSAUX             /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf        2048
SYSTEM             /oracle/app/oracle/oradata/ORCL11/system01.dbf         700
UNDOTBS1         /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf        1000
USERS             /oracle/app/oracle/oradata/ORCL11/users01.dbf        2048

 

 

참조 :