数据库如何查看表空间的使用率

客官°小女子只卖身不卖艺 2021-09-20 07:16 340阅读 0赞

进入oracle数据库中，输入以下代码即可  
select total.tablespace\_name,  
round(total.MB, 2) as Total\_MB,  
round(total.MB - free.MB, 2) as Used\_MB,  
round((1 - free.MB / total.MB)  *100, 2) || '%' as Used\_Pct  
from (select tablespace\_name, sum(bytes) / 1024 / 1024 as MB  
from dba\_free\_space  
group by tablespace\_name) free,  
(select tablespace\_name, sum(bytes) / 1024 / 1024 as MB  
from dba\_data\_files  
group by tablespace\_name) total  
where free.tablespace\_name = total.tablespace\_name;  
![数据库如何查看表空间的使用率][watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk]  
SELECT a.tablespace\_name, a.BYTES total, a.bytes - nvl(b.bytes, 0) free FROM (SELECT tablespace\_name, SUM (bytes) bytes FROM dba\_temp\_files GROUP BY tablespace\_name) a,(SELECT tablespace\_name, SUM (bytes\_cached) bytes FROM v$temp\_extent\_pool GROUP BY tablespace\_name) b WHERE a.tablespace\_name = b.tablespace\_name(+)  
![数据库如何查看表空间的使用率][watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk 1]  
SELECT 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\_total WHERE temp\_used.tablespace\_name = temp\_total.tablespace\_name;  
![数据库如何查看表空间的使用率][watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk 2]

那些语句待用临时表空间  
SELECT se.username,sid,serial\#,sql\_address,machine,program,tablespace,segtype,contents FROM v$session se, v$sort\_usage su WHERE se.saddr = su.session\_addr;

转载于:https://blog.51cto.com/795580/2065190

[watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk]: https://s1.51cto.com/images/blog/201801/25/7611049e756b4255e6c99a5477687cdf.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk 1]: https://s1.51cto.com/images/blog/201801/25/79ae44925286cd4ec8724aea09e8a08a.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[watermark_size_16_text_QDUxQ1RP5Y2a5a6i_color_FFFFFF_t_100_g_se_x_10_y_10_shadow_90_type_ZmFuZ3poZW5naGVpdGk 2]: https://s1.51cto.com/images/blog/201801/25/0d354b81f3a9aaa11dd710c3bc1dca72.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=