Oracle表空间使用情况查询

---剩余空闲表空间

SELECT a.tablespace_name, sum(bytes) / 1024 / 1024 / 1024 空间G

  FROM dba_free_space a

 WHERE a.tablespace_name in upper('bgpm_data01')

 group by a.tablespace_name;

 

---表空间名使用情况

select a.tablespace_name as "表空间名",

       a.bytes / 1024 / 1024 as "表空间大小(M)",

       (a.bytes - b.bytes) / 1024 / 1024 as "已使用空间(M)",

       b.bytes / 1024 / 1024 "空闲空间(M)",

       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"

  from (select tablespace_name, sum(bytes) bytes

          from dba_data_files

         group by tablespace_name)  a,

       (select tablespace_name, sum(bytes) bytes, max(bytes) largest

          from dba_free_space

         group by tablespace_name) b

 where a.tablespace_name = b.tablespace_name

 order by ((a.bytes - b.bytes) / a.bytes) desc;

 

---表占表空间情况

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"

from dba_segments t

where /*t.segment_type='TABLE'

and   */t.segment_name like '%FTP%'  --表名

group by OWNER, t.segment_name, t.segment_type;