Oracle查看表空间使用率及爆满解决方案_SQL语句

1.查看数据库表空间文件:

--查看数据库表空间文件
select * from dba_data_files;





2.查看所有表空间的总容量：

--查看所有表空间的总容量
select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB
from dba_data_files dba
group by dba.TABLESPACE_NAME;





--查看所有表空间的总容量
select dba.TABLESPACE_NAME, sum(bytes)/1024/1024/1024 as GB
from dba_data_files dba
group by dba.TABLESPACE_NAME;



3.查看数据库表空间使用率

--查看数据库表空间使用率
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
order by used_pct desc;





4.1.查看表空间总大小、使用率、剩余空间

--查看表空间总大小、使用率、剩余空间
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name

4.2.查看表空间使用率(包含temp临时表空间)

--查看表空间使用率(包含临时表空间)
select * from (
Select a.tablespace_name,
(a.bytes- b.bytes) "表空间使用大小(BYTE)",
a.bytes/(1024*1024*1024) "表空间大小(GB)",
b.bytes/(1024*1024*1024) "表空间剩余大小(GB)",
(a.bytes- b.bytes)/(1024*1024*1024) "表空间使用大小(GB)",
to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率"
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,
d.bytes_used "表空间使用大小(BYTE)",
c.bytes/(1024*1024*1024) "表空间大小(GB)",
(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",
d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",
to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"
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





5.查看具体表的占用空间大小

--查看具体表的占用空间大小
select * from (
select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc





二、扩展大小或增加表空间文件

1.更改表空间的dbf数据文件分配空间大小

alter database datafile ‘...system_01.dbf’ autoextend on;
alter database datafile ‘...system_01.dbf’ resize 1024M;





2.为表空间新增一个数据文件(表空间满32G不能扩展则增加表空间文件)

alter tablespace SYSTEM add datafile '/****' size 1000m autoextend on next 100m;

版权声明：本文内容来自CSDN:普通网友，遵循CC 4.0 BY-SA版权协议上原文接及本声明。
本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行可。
原文链接：https://blog.csdn.net/AUZKAY/article/details/137899684
如有涉及到侵权，请联系，将立即予以删除处理。
在此特别鸣谢原作者的创作。
此篇文章的所有版权归原作者所有，与本公众号无关，商业转载建议请联系原作者，非商业转载请注明出处。