当前位置: 代码迷 >> Oracle管理 >> 怎样查看当前用户下的表空间总容量以及剩余容量,该怎么解决
  详细解决方案

怎样查看当前用户下的表空间总容量以及剩余容量,该怎么解决

热度:106   发布时间:2016-04-24 05:34:31.0
怎样查看当前用户下的表空间总容量以及剩余容量
本人想要查看当前用户下的表空间容量信息,不知道该怎么写SQL。
只是查看当前用户下的。不适用需要sysdba权限的。

------解决方案--------------------
SQL code
select a.tablespace_name,       a.tablespace_size * b.block_size / 1024 / 1024 "totalmsize(m)",       a.used_space * b.block_size / 1024 / 1024 "usedmsize(m)",       round(a.used_percent, 2) "usedrate(%)"  from dba_tablespace_usage_metrics a, dba_tablespaces b where a.tablespace_name = b.tablespace_name;
------解决方案--------------------
SQL code
-- *01). 查看普通表空间的使用情况:SELECT a.tablespace_name as tablespace_name,    to_char(b.total/1024/1024,999999.99) as Total,    to_char((b.total-a.free)/1024/1024,'9999990D99') as Used,    to_char(a.free/1024/1024,'9999990D99') as Free,    to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as Used_Rate   FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,    (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b  WHERE a.tablespace_name=b.tablespace_name  ORDER BY round((total-free)/total,4) DESC;-- *02). 查看临时表空间的使用情况:su - oraclesqlplus /nologconn / as sysdbaSELECT temp_used.tablespace_name,        total - used as "Free",        total as "Total",        round(nvl(total - used, 0) * 100 / total, 3) "Free percent"  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,       (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_totalWHERE temp_used.tablespace_name = temp_total.tablespace_name;
------解决方案--------------------
探讨

SQL code
-- *01). 查看普通表空间的使用情况:
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,'9999990D99') as Used,
to_c……
  相关解决方案