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
查询表空间的数据使用情况
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.删除表
drop table F_TEMP_H;
drop table F_TEMP_D;
2.删除小时表
drop table VOLTE_BIGXDR_PD_2022092302 CASCADE CONSTRAINTS PURGE;
drop table VOLTE_BIGXDR_PD_2022092303 CASCADE CONSTRAINTS PURGE;
3.清理分区表
alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37815;
alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37835;
4.清空表
truncate table F_TEMP_H;
truncate table F_TEMP_D;
5.删除表数据
delete from F_TEMP_H where sdate<=to_date('2022013123','yyyymmddhh24');
delete from F_TEMP_D where sdate<to_date('20220101','yyyymmdd');