Programming/Oracle

DB FREE SPACE CHECK하기

초록깨비 2009. 2. 13. 14:21
728x90

system계정으로 로그인 후 아래 쿼리고 확인한다.

 



select df.tablespace_name "Tablespace",
       round(df.TBS_byte / 1048576, 2) "Total(MB)",
       round((df.TBS_byte - fs.Free_byte) / 1048576, 2) "Used(MB)",
       round(fs.Free_byte / 1048576, 2) "Free(MB)",
       round((fs.Free_byte / df.TBS_byte) * 100, 0) "Free(%%)",
       fs.pieces "Pieces",
       round(fs.Max_free / 1048576, 2) "MaxFree(MB)"
  from (select tablespace_name,
               sum(bytes) TBS_byte
          from dba_data_files
         group by tablespace_name) df,
       (select tablespace_name,
               max(bytes) Max_free,
               sum(bytes) Free_byte,
               count(*) pieces
          from dba_free_space
         group by tablespace_name) fs,
       (select tablespace_name,
               initial_extent,
               next_extent
          from dba_tablespaces) db
 where df.tablespace_name = db.tablespace_name
   and df.tablespace_name = fs.tablespace_name(+)
 order by 5

 

 

 

728x90