/****************1、建立臨時表********************/
create table temp_table_size
(
table_name varchar2(30) not null, --表名
main_size number(12,3) default 0 not null, --表主段空間（單位：MB）
lob_size number(12,3) default 0 not null, --LOB大字段空間
data_size number(12,3) default 0 not null, --數(shù)據(jù)空間（主空間 + LOB）
index_size number(12,3) default 0 not null, --索引空間
total_size number(12,3) default 0 not null, --總占用空間（加索引空間）
record_count number(15) default 0 not null --記錄數(shù)
);
alter table temp_table_size add constraint pk_temp_table_size PRimary key (table_name);
insert into temp_table_size (table_name) select table_name from user_tables;
commit;
/**************2、表各項統(tǒng)計*****************/
declare
v_size_1 number(12,3);
v_size_2 number(12,3);
v_size_3 number(12,3);
v_count number(15);
begin
--數(shù)據(jù)初始化
update temp_table_size
set main_size = 0,
lob_size = 0,
data_size = 0,
index_size = 0,
total_size = 0,
record_count = 0;
for v_rec in (select table_name from user_tables) loop
--主數(shù)據(jù)段空間
select sum(bytes) / 1024 / 1024
into v_size_1
from user_segments
where segment_name = v_rec.table_name;
--LOB空間
select nvl(sum(bytes),0) / 1024 / 1024
into v_size_2
from user_segments
where segment_name in
(
select segment_name
from user_lobs
where table_name= v_rec.table_name
);
--索引空間
select nvl(sum(bytes),0) / 1024 / 1024
into v_size_3
from user_segments
where segment_name in
(
select index_name
from user_indexes
where table_name= v_rec.table_name
);
--表記錄數(shù)統(tǒng)計
execute immediate 'select count(*) from ' v_rec.table_name into v_count;
--寫統(tǒng)計結(jié)果
update temp_table_size
set main_size = v_size_1,
lob_size = v_size_2,
index_size = v_size_3,
data_size = v_size_1 + v_size_2,
total_size = v_size_1 + v_size_2 + v_size_3,
record_count = v_count
where table_name = v_rec.table_name;
end loop;
--保存結(jié)果
commit;
end;
/
/**************3、查看統(tǒng)計結(jié)果***************/
select *
from temp_table_size
order by table_name;
