ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 有关表空间查询的sql脚本

有关表空间查询的sql脚本

原创 Linux操作系统 作者:freshairpeng 时间:2009-02-25 15:50:34 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: rman操作
请登录后发表评论 登录
全部评论

注册时间:2009-02-25

  • 博文量
    69
  • 访问量
    246230