年底又该大扫除了，看看哪些大表碎片多-凯发app官方网站

create or replace function get_space_usage1(
owner in varchar2,
object_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default null) return sys.dbms_debug_vc2coll pipelined
as
ufbl number;
ufby number;
fs1bl number;
fs1by number;
fs2bl number;
fs2by number;
fs3bl number;
fs3by number;
fs4bl number;
fs4by number;
fubl number;
fuby number;
begin
dbms_space.space_usage(owner,object_name,segment_type,
ufbl, ufby, fs1bl,fs1by, fs2bl,fs2by,
fs3bl,fs3by, fs4bl,fs4by, fubl,fuby, partition_name);
pipe row('full blocks /mb '||to_char(fubl, '999999999')||' '||to_char(fuby /1048576,'999999999'));
pipe row('unformatted blocks/mb '||to_char(ufbl, '999999999')||' '||to_char(ufby /1048576,'999999999'));
pipe row('free space 0-25% '||to_char(fs1bl, '999999999')||' '||to_char(fs1by /1048576,'999999999'));
pipe row('free space 25-50% '||to_char(fs2bl, '999999999')||' '||to_char(fs2by /1048576,'999999999'));
pipe row('free space 50-75% '||to_char(fs3bl, '999999999')||' '||to_char(fs3by /1048576,'999999999'));
pipe row('free space 75-100% '||to_char(fs4bl, '999999999')||' '||to_char(fs4by /1048576,'999999999'));
end get_space_usage1;
/

看效果

create table t1 (i int);
insert into t1 select rownum from dual connect by level <1000001;
delete from t1 where rownum<556677;
insert into t1 select rownum from dual connect by level <10000;
delete from t1 where rownum<3322;
col column_value for a50
select * from table(get_space_usage1('dba1','t1','table'));

这里是块个数，400个块空闲75%以上。

看看大小
占用空间13m，实际上就6m

看看t2人人都希望如此

因此，表t1还是能收缩一部分的，例如通过move 、online redefin、泵、ctas、shrink

对表执行全部删除后

还是有些未填充满的块，按说都该为0，再插入新数据

有些块填满，216 76-208-69=15。

阅读(47) | 评论(0) | 转发(0) |

下一篇：mksysb恢复后数据库启动注意事项

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们