查看Oracle数据库表空间脚本

最新推荐文章于 2022-06-21 18:13:15 发布

转载最新推荐文章于 2022-06-21 18:13:15 发布 · 215 阅读

文章标签：

本文提供了一段详细的SQL查询语句，用于查看Oracle数据库中各表空间的总大小、已用空间、剩余空间及使用率等信息。通过两个子查询分别获取永久表空间和临时表空间的数据，并进行联合查询，最终按表空间名称排序输出。

Select * From (Select a.Tablespace_Name, To_Char(a.Bytes / 1024 / 1024, '99,999.999') Total_Bytes, To_Char(b.Bytes / 1024 / 1024, '99,999.999') Free_Bytes, To_Char(a.Bytes / 1024 / 1024 - b.Bytes / 1024 / 1024, '99,999.999') Use_Bytes, To_Char((1 - b.Bytes / a.Bytes) * 100, '99.99') || '%' Use From (Select Tablespace_Name, Sum(Bytes) Bytes From Dba_Data_Files Group By Tablespace_Name) a, (Select Tablespace_Name, Sum(Bytes) Bytes From Dba_Free_Space Group By Tablespace_Name) b Where a.Tablespace_Name = b.Tablespace_Name Union All Select c.Tablespace_Name, To_Char(c.Bytes / 1024 / 1024, '99,999.999') Total_Bytes, To_Char((c.Bytes - d.Bytes_Used) / 1024 / 1024, '99,999.999') Free_Bytes, To_Char(d.Bytes_Used / 1024 / 1024, '99,999.999') Use_Bytes, To_Char(d.Bytes_Used * 100 / c.Bytes, '99.99') || '%' Use From (Select Tablespace_Name, Sum Bytes) Bytes From Dba_Temp_Files Group By Tablespace_Name) c, (Select Tablespace_Name, Sum(Bytes_Cached) Bytes_Used From V$temp_Extent_Pool Group By Tablespace_Name) d Where c.Tablespace_Name = d.Tablespace_Name) Order By Tablespace_Name;

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

转载于:http://blog.itpub.net/25693151/viewspace-710150/