oracleの表領域が少なくなった場合の調査用SQL

テーブルスペースの表領域使用量

select
   tablespace_name,
   to_char(nvl(total_bytes / 1024,0), '999,999,999' ) as "size(KB)" ,
   to_char(nvl((total_bytes - free_total_bytes) / 1024,0), '999,999,999' ) as "used(KB)" ,
   to_char(nvl(free_total_bytes/1024,0), '999,999,999' ) as "free(KB)" ,
   round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
   ( select
       tablespace_name,
       sum (bytes) total_bytes
     from
       dba_data_files
     group by
       tablespace_name
   ),
   ( select
       tablespace_name free_tablespace_name,
       sum (bytes) free_total_bytes
     from
       dba_free_space
     group by tablespace_name
   )
where
   tablespace_name = free_tablespace_name(+)

スキーマ別 ディスク使用量

SELECT
     sg.tablespace_name,
     sg.owner,
     sg.segment_type,
     count (segment_name) segment_cnt,
     sum (blocks) blocks,
     round( sum (bytes)/1024/1024) mb
FROM
     dba_segments sg,
     dba_tablespaces ts
WHERE
     sg.tablespace_name = ts.tablespace_name
GROUP BY
     sg.tablespace_name,
     sg.owner,
     sg.segment_type
ORDER BY
     sg.tablespace_name,
     sg.owner,
     sg.segment_type

ユーザーの表領域を浪費しているテーブルを特定する。

select
  tablespace_name,segment_name,segment_type,sum(bytes) / 1000 / 1000
from
  user_extents
group by
  tablespace_name,segment_name,segment_type
order by
  sum(bytes) desc