테이블 스페이스 계산

/* 테이블 스페이스 사용량 계산 */

with t1 as (

select distinct tablespace_name

from dba_tables

where owner in (

'TIBERO',

'TEST'

)

)

select --ddf.tablespace_name "Tablespace Name",

       ddf.tablespace_name ,

       ddf.bytes/1024/1024 "Bytes(MB)",

       ddf.maxbytes/1024/1024 "MaxBytes(MB)",

       (ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)",

       round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)",

       dfs.bytes/1024/1024 "Free(MB)",

       round((1 - ((ddf.bytes - dfs.bytes) / ddf.bytes)) * 100, 2) "Free(%)"

from   (select tablespace_name, sum(bytes) bytes , sum(MAXBYTES) maxbytes

from   dba_data_files

group by tablespace_name) ddf,

       (select tablespace_name, sum(bytes) bytes , 0 maxbytes

from   dba_free_space

group by tablespace_name) dfs

where   ddf.tablespace_name = dfs.tablespace_name

  and ddf.tablespace_name in (select tablespace_name from t1)

order by ((ddf.bytes-dfs.bytes)/ddf.bytes) desc

;

 

/* 테이블스페이스별 사용량 계산 */

select ddf.tablespace_name ,

       ddf.bytes/1024/1024 "Bytes(MB)",

       ddf.maxbytes/1024/1024 "MaxBytes(MB)",

       (ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)",

       round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)",

       dfs.bytes/1024/1024 "Free(MB)",

       round((1 - ((ddf.bytes - dfs.bytes) / ddf.bytes)) * 100, 2) "Free(%)"

from   (select tablespace_name, sum(bytes) bytes , sum(MAXBYTES) maxbytes

from   dba_data_files

group by tablespace_name) ddf,

       (select tablespace_name, sum(bytes) bytes , 0 maxbytes

from   dba_free_space

group by tablespace_name) dfs

where   ddf.tablespace_name = dfs.tablespace_name

order by ((ddf.bytes-dfs.bytes)/ddf.bytes) desc

 

;

 

/* 사용자별 사용량 계산 */

with t1 as (
select distinct tablespace_name
from dba_tables
where owner in (
'TIBERO',
'TIBERO1',
'TEST'
)
)
select 
       sum(ddf.bytes/1024/1024) "Bytes(MB)",
       sum((ddf.bytes - dfs.bytes)/1024/1024) "Used(MB)"
from   (select tablespace_name, sum(bytes) bytes , sum(MAXBYTES) maxbytes
from   dba_data_files 
group by tablespace_name) ddf,
       (select tablespace_name, sum(bytes) bytes , 0 maxbytes
from   dba_free_space 
group by tablespace_name) dfs
where   ddf.tablespace_name = dfs.tablespace_name
  and ddf.tablespace_name in (select tablespace_name from t1)
order by ((ddf.bytes-dfs.bytes)/ddf.bytes) desc
;