有关表空间查询的sql脚本

select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
  round((f.free/a.total)*100) "% Free"
  from
  (select tablespace_name, sum(bytes/(1024*1024)) total
       from dba_data_files group by tablespace_name) a,
  (select tablespace_name, round(sum(bytes/(1024*1024))) used
       from dba_extents group by tablespace_name) u,
  (select tablespace_name, round(sum(bytes/(1024*1024))) free
       from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;

--查看当前用户表空间段的分配情况
select tablespace_name,sum(bytes)/(1024*1024),sum(blocks),sum(extents) from user_segments
group by tablespace_name  ---当前用户为nihl 查询结果就是用户nihl的表空间分配情况

--检查各用户表空间段情况
select owner,segment_type,tablespace_name,sum(bytes)/(1024*1024),sum(blocks),sum(extents) from dba_segments
where wner='NIHL'
group by owner,segment_type,tablespace_name
--检查当前用户表空间区的使用情况
select tablespace_name,sum(extent_id),sum(bytes)/(1024*1024),sum(blocks) from user_extents
group by tablespace_name

--检查所有用户表空间区的使用情况
select owner,segment_type,tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_extents
where wner='NIHL'
group by owner,segment_type,tablespace_name

select owner,tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_extents
where wner='NIHL'
group by owner,tablespace_name

---查看当前用户空闲表空间的情况
select tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from user_free_space
group by tablespace_name

select tablespace_name,sum(bytes)/(1024*1024),sum(blocks) from dba_free_space
group by tablespace_name

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14766526/viewspace-557771/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14766526/viewspace-557771/