Edited at

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

More than 3 years have passed since last update.


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

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