Oracle查看表空间使用情况

野性酷女 2024-04-01 18:34 112阅读 0赞

#### 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');