表空间space使用查询

--表空间使用

select tablespace_name, sum(bytes / 1024 / 1024) || ' MB' as Used_MB
  from dba_segments
 where segment_name not like 'BIN$%'
 group by tablespace_name;   --直接查各表空间使用空间

 

select total.tablespace_name,
       total.filebytes / 1024 / 1024 || ' MB' as "File_Bytes_MB",
       total.bytes / 1024 / 1024 || ' MB' "Total_MB",
       (total.bytes - free.bytes) / 1024 / 1024 || ' MB' "Used_MB",
       free.bytes / 1024 / 1024 || ' MB' "Free_MB",
       round(((total.bytes - free.bytes) / total.bytes) * 100, 2) || '%' "Percent_Used"
  from (select tablespace_name, sum(bytes) filebytes, sum(user_bytes) bytes
          from dba_data_files
         group by tablespace_name) total,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) free
 where total.tablespace_name =  free.tablespace_name(+)
 order by ((total.bytes - free.bytes) / total.bytes) desc;    --查各表空间使用、剩余等。

 

--数据文件是否可收缩

select /*+ ordered use_hash(a,b,c) */
 a.file_id,
 a.file_name,
 a.filesize_Mb,
 nvl(b.freesize_Mb, 0) as freesize_Mb,
 (a.filesize_Mb - nvl(b.freesize_Mb, 0)) used_size_Mb,
 c.HWMsize_Mb,
 c.HWMsize_Mb - (a.filesize_Mb - nvl(b.freesize_Mb, 0)) unusedsize_belowhwm_Mb,
 a.filesize_Mb - c.HWMsize_Mb can_shrink_size_Mb
  from (select file_id, file_name, round(bytes / 1024 / 1024) filesize_Mb
          from dba_data_files) a,
       (select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize_Mb
          from dba_free_space dfs
         group by file_id) b,
       (select file_id, round(max(block_id) * 8 / 1024) HWMsize_Mb
          from dba_extents
         group by file_id) c
 where a.file_id = b.file_id(+)
   and a.file_id = c.file_id
 order by a.file_id;