Oracle查询单表占用空间实际大小

1、查询”帐户/库”数据占用存储空间大小

select sum(bytes)/1024/1024 as “size(M)” from dba_segments where owner=’帐户名/库名’

2、查询单表数据占用存储空间大小

select sum(bytes)/(1024*1024) as “size(M)” from user_segments

where segment_name=upper(‘表名’);

3、在表空间中，查询占用存储空间最大的表

SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = upper(‘表空间名称’) GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

4、在”帐户/库”中，查询占用存储空间最大的表

SELECT owner, segment_name, segment_type, sum(bytes) / 1024 / 1024 "Size (MB)" FROM dba_segments WHERE owner = '你的用户名' GROUP BY owner, segment_name, segment_type ORDER BY (sum(bytes) / 1024 / 1024) DESC;

5、查询某segment_name属于哪个table

select owner,table_name,column_name,segment_name,index_name from dba_lobs a where a.segment_name = '上一步查询到的segment_name名称';

6、如果遇到大表需要清理历史数据，直接批量删除容易锁表，采用大表单表按天清理数据的过程方法：

DECLARE
  begin_date date := to_date('2020-09-01','yyyy-mm-dd');
BEGIN 
  WHILE begin_date < to_date('2023-07-01','yyyy-mm-dd') loop 
    DELETE FROM tablespace_name.table_name WHERE CREATE_TIME >= begin_date and CREATE_TIME < begin_date+1; 
    commit; 
    begin_date := begin_date+1; 
  end loop; 
END; 
/