Oracle水位收缩过程
原理
利用过程调用外层视图检查出对应的表，进行自动水位收缩
存储过程调用的视图
--grant select any dictionary to TEST;
--grant analyze any to TEST;
--grant create table to TEST;
--可以通过下面这个视图来指定表范围，下面的过程是调用这个视图里面最小的那张表进行碎片整理。
create or replace view test.v_shrinktable as
select t.*,
round((t.realkb / t.hwmkb) * 100) pct,
s.bytes
from (select aa.OWNER,
aa.TABLE_NAME,
aa.NUM_ROWS,
aa.AVG_ROW_LEN,
round(blocks *
(select value
from v$parameter
where name = 'db_block_size') / 1024 / 1024) hwmkb
--,round(blocks*(select value from v$parameter where name ='db_block_size')/1024*pct_free/100) pctkb,
,
ROUND((num_rows * avg_row_len / 1024)) realkb1,
ROUND((num_rows * avg_row_len / 1024 / 1024)) realkb,
aa.LAST_ANALYZED
from dba_tables aa
where aa.owner in ('H2','H2LOG')
and aa.NUM_ROWS >= 0) t,
dba_segments s
where t.hwmkb > 100
and t.owner = s.owner
and t.table_name = s.segment_name
and t.table_name not in ('T') --排除表
and round((t.realkb / t.hwmkb) * 100) < 70 --碎片情况
and bytes < 10000000000 --表大小限制
order by bytes;
具体存储过程内容
--对应过程
create procedure test.shrink_table(user IN VARCHAR2) is
v_sql long:='';
v_sqltemp long:='';
P_TOTALRECORDS number:=0;
p_date date;
BEGIN
--记录中总记录条数
V_SQL := 'SELECT TO_NUMBER(COUNT(*)) FROM dba_tables where owner=(''' || user ||''') and table_name=('''||'SHRINKTABLE_NEED_HIS'||''') ';
EXECUTE IMMEDIATE V_SQL INTO P_TOTALRECORDS;
IF P_TOTALRECORDS < 1 THEN
EXECUTE IMMEDIATE 'create table shrinktable_need_his(owner varchar2(80),table_name varchar2(80),bytes_start number,bytes_end number,pct number,shrink_time varchar2(80))';
END IF;
for i in (select owner||'.'||table_name as tablename,t.* from (select * from (select owner,table_name,bytes as bytes_start,0 as bytes_end,pct from v_shrinktable b order by bytes_start) a where rownum=1) t )
loop
EXECUTE IMMEDIATE ( 'insert into shrinktable_need_his values ('''||i.owner||''','''||i.table_name||''','||i.bytes_start||','||i.bytes_end||','||i.pct||','''||to_char(sysdate,'yyyymmdd')||''' )');
EXECUTE IMMEDIATE ' alter table ' || i.tablename || ' enable row movement';
EXECUTE IMMEDIATE ' alter table ' || i.tablename || ' shrink space cascade';
EXECUTE IMMEDIATE ' alter table ' || i.tablename || ' disable row movement';
dbms_stats.gather_table_stats(ownname => user,tabname =>i.table_name,degree =>10,cascade =>true,estimate_percent =>25);
v_sqltemp := 'update shrinktable_need_his a set a.bytes_end =(select b.bytes from dba_segments b where b.owner=' ||'('''||i.owner||''')'||' and b.segment_name='||'('''||i.table_name||''') )'||
'where a.owner=' ||'('''||i.owner||''')'||' and a.table_name='||'('''||i.table_name||''') ';
EXECUTE IMMEDIATE( v_sqltemp);
end loop;
commit;
end shrink_table;
