Oracle查看表空间使用情况_oracle表空间使用率查询

查看所有表空间

select 
b.file_name 物理文件名, 
b.tablespace_name 表空间, 
b.bytes /1024/1024 大小M, 
(b.bytes - sum(nvl(a.bytes,0))) /1024/1024 已使用M, 
substr( (b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100, 1, 5) 利用率 
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes 
order by b.tablespace_name
1
2
3
4
5
6
7
8
9
10

查询表空间的数据使用情况

select 
upper(f.tablespace_name) as "表空间名", 
d.tot_grootte_mb as "表空间大小(M)", 
d.tot_grootte_mb - f.total_bytes as "已使用空间(M)", 
to_char(round((d.tot_grootte_mb-f.total_bytes) / d.tot_grootte_mb * 100,2), '990.99')||'%' as "使用比", 
f.total_bytes as "空闲空间(M)", 
f.max_bytes as "最大块(M)" 
from (select tablespace_name, 
round(sum(bytes)/(1024*1024), 2) total_bytes, 
round(max(bytes)/(1024*1024), 2) max_bytes 
from sys.dba_free_space group by tablespace_name) f, 
(select dd.tablespace_name, 
round(sum(dd.bytes)/(1024*1024), 2) tot_grootte_mb 
from sys.dba_data_files dd group by dd.tablespace_name) d 
where d.tablespace_name = f.tablespace_name
order by d.tot_grootte_mb;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

常用的清理语句

1.删除表

drop table F_TEMP_H;
drop table F_TEMP_D;
1
2

2.删除小时表

drop table VOLTE_BIGXDR_PD_2022092302 CASCADE CONSTRAINTS PURGE;
drop table VOLTE_BIGXDR_PD_2022092303 CASCADE CONSTRAINTS PURGE;
1
2

3.清理分区表

alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37815;
alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37835;
1
2

4.清空表

truncate table F_TEMP_H;
truncate table F_TEMP_D;
1
2

5.删除表数据

delete from F_TEMP_H where sdate<=to_date('2022013123','yyyymmddhh24');
delete from F_TEMP_D where sdate<to_date('20220101','yyyymmdd');
1
2