오라클 테이블스페이스 용량확인 , 사용량확인, oracle tablespace volume size

오라클 테이블스페이스 용량확인 , 사용량확인, oracle tablespace volume size

오라클 테이블스페이스 용량확인을 위한 쿼리입니다.

 

select
    substr(t.tablespace_name,1,30) TABLESPACE_NAME,
    round(sum(t.totalbyte)/1024/1024,1) "Total MB",
    round((round(sum(t.totalbyte)/1024/1024,1)-round(sum(t.sumbyte)/1024/1024,1))/round(sum(t.totalbyte)/1024/1024,1)*100,2) "Used%",
    round(sum(t.totalbyte)/1024/1024,1)-round(sum(t.sumbyte)/1024/1024,1) "Used MB",
    round(sum(t.sumbyte)/1024/1024,1) "Free MB"
from (
    (
        select tablespace_name,sum(bytes) totalbyte,0 sumbyte
        from dba_data_files
        group by tablespace_name
    ) 
    union
    (
        select tablespace_name,0 totalbyte,sum(bytes) sumbyte
        from dba_free_space
        group by tablespace_name
    )
) t
group by t.tablespace_name
order by TABLESPACE_NAME;

실행시간은 좀(1분이상) 걸릴 수 있습니다.

 

실행결과는 아래와 같이 나오게 됩니다.

오라클 테이블스페이스 용량확인 , oracle tablespace volume size
#오라클,#테이블,#스페이스,#용량,#확인,#테이블스페이스,#용량확인,#oracle,#table,#space,#tablespace,#tbs,#volume,#check

select substr(t.tablespace_name,1,30) TABLESPACE_NAME, round(sum(t.totalbyte)/1024/1024,1) "Total MB", round((round(sum(t.totalbyte)/1024/1024,1)-round(sum(t.sumbyte)/1024/1024,1))/round(sum(t.totalbyte)/1024/1024,1)*100,2) "Used%", round(sum(t.totalbyte)/1024/1024,1)-round(sum(t.sumbyte)/1024/1024,1) "Used MB", round(sum(t.sumbyte)/1024/1024,1) "Free MB" from ( ( select tablespace_name,sum(bytes) totalbyte,0 sumbyte from dba_data_files group by tablespace_name ) union ( select tablespace_name,0 totalbyte,sum(bytes) sumbyte from dba_free_space group by tablespace_name ) ) t group by t.tablespace_name order by TABLESPACE_NAME;

https://devscb.com/post/103

Check Oracle tablespace capacity, check usage, oracle tablespace volume size