Oracle表空间使用及单表空间占用查询

表空间物理文件的名称及大小

select ds.tablespace_name
		,ds.file_id
		,ds.file_name
		,round(ds.bytes/(1024*1024),0) total_space   
  from dba_data_files ds
order by ds.tablespace_name

表空间及所占空间大小

select ds.tablespace_name
      ,sum(ds.bytes)/1024/1024 
 from dba_data_files ds
group by ds.tablespace_name;

表空间使用情况

select ta.tablespace_name
,ta.total
,tf.free
,ta.total-tf.free as used
,substr(tf.free/ta.total * 100, 1, 5) as "FREE%"
,substr((ta.total - tf.free)/ta.total * 100, 1, 5) as "USED%" 
FROM(select tablespace_name
            ,sum(bytes)/1024/1024 as total 
        from dba_data_files
    group by tablespace_name) ta, 
    (select tablespace_name
            ,sum(bytes)/1024/1024 as free 
       from dba_free_space 
   group by tablespace_name) tf
where ta.tablespace_name = tf.tablespace_name
order by ta.tablespace_name;

单表空间占用大小

select ts.segment_name
		,ts.segment_type
		,sum(ts.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments ts
where ts.segment_type='TABLE'
  and ts.segment_name='TABLE_NAME'
group by ts.owner
		,ts.segment_name
		,ts.segment_type;