oracle高水平位,【通用脚本分享】批量释放DELETE的表的高水平位

代码

create or replace package pkg_shrink

Authid Current_User

as

/*

created by ljb at 2010-10-18

功能：将delete后的表降低高水平

*/

procedure p_move_tab (p_tab varchar2);

procedure  p_cal_bytes (p_status varchar2 ,p_tab varchar2) ;

procedure p_rebuid_idx(p_tab varchar2);

procedure p_main(p_table_name varchar2);

end  pkg_shrink ;

/

create or replace package body pkg_shrink

as

v_sql  varchar2(4000);

procedure  p_cal_bytes (p_status varchar2 ,p_tab varchar2)

as

v_tab_bytes number;

v_idx_bytes number;

v_str_tab       varchar2(4000);

v_str_idx       varchar2(4000);

begin

select sum(bytes)/1024/1024 into v_tab_bytes from user_segments where segment_name=upper(p_tab);

select sum(bytes)/1024/1024 into v_idx_bytes from user_segments where segment_name IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME=upper(p_tab));

v_str_tab:=p_status||'表'||p_tab||'的大小为'||v_tab_bytes||'M';

if v_idx_bytes is null then

v_str_idx:=p_status||'无索引';

else

v_str_idx:=p_status||'索引的大小为'||v_idx_bytes||'M';

end if;

dbms_output.put_line(v_str_tab ||';'||v_str_idx);

end p_cal_bytes;

procedure p_move_tab (p_tab varchar2)

as

V_IF_PART_TAB NUMBER;

begin

SELECT COUNT(*) INTO V_IF_PART_TAB FROM user_part_tables WHERE TABLE_NAME=upper(P_TAB);

IF V_IF_PART_TAB=0 THEN   ----非分区表

v_sql:='alter table '||p_tab ||' move';--完成表的MOVE动作，从而做到降低高水平位，不过也带来了索引的失效！

DBMS_OUTPUT.put_line(v_sql);

execute immediate v_sql;

ELSE                    ---分区表

for i in (SELECT *　from USER_TAB_PARTITIONS WHERE TABLE_NAME=upper(p_tab)) loop

v_sql:='alter table '|| p_tab ||' move  partition ' ||i.partition_name;  --完成分区表的MOVE动作，同样带来了索引失效！

DBMS_OUTPUT.put_line(v_sql);

execute immediate v_Sql;

end loop;

END IF;

end p_move_tab;

procedure p_rebuid_idx(p_tab varchar2)

as

V_NORMAL_IDX NUMBER;

V_PART_IDX   NUMBER;

begin

SELECT  COUNT(*) INTO V_NORMAL_IDX FROM user_indexes  where table_name='PART_TAB' AND INDEX_NAME

NOT IN (SELECT INDEX_NAME FROM user_part_indexes);

IF V_NORMAL_IDX>=1 THEN  ---普通索引

for i in (select * from user_indexes where table_name=upper(p_tab) AND INDEX_NAME

NOT IN (SELECT INDEX_NAME FROM user_part_indexes)) loop

v_sql:= 'alter index '||i.index_name ||' rebuild'; --将失效的普通索引重建

DBMS_OUTPUT.put_line(v_sql);

execute immediate v_sql;

end loop;

END IF;

SELECT COUNT(*) INTO V_PART_IDX FROM user_part_indexes WHERE TABLE_NAME='PART_TAB';

IF V_PART_IDX>=1    THEN              ---分区索引

for i in (SELECT *　from User_Ind_Partitions WHERE  index_name in (select index_name from user_part_indexes where table_name =upper(p_tab))) loop

v_sql:='alter index '||i.index_name ||'  rebuild partition  ' ||i.partition_name;  ---将失效的分区索引重建

DBMS_OUTPUT.put_line(v_sql);

execute immediate v_Sql;

end loop;

END IF;

end p_rebuid_idx;

procedure p_main(p_table_name varchar2)

as

begin

for i in (select * from (

SELECT SUBSTR(s,INSTR(s,',',1,ROWNUM)+1, INSTR(s,',',1,ROWNUM+1) - INSTR(s,',',1,ROWNUM)-1)  AS TYPE_ID

FROM (SELECT ','||p_table_name||',' AS s FROM DUAL)

CONNECT BY ROWNUM<=100

)

WHERE type_id IS NOT NULL

) loop  ---在外面SELECT 再套一层是必须的 ，否则只会循环一次。另外type_id IS NOT NULL是必须的，否则会多循环

DBMS_OUTPUT.put_line('当前处理的表为'||I.TYPE_ID);

p_cal_bytes('未降低高水平位前',i.type_id);

p_move_tab(i.type_id);

p_rebuid_idx(I.TYPE_ID);

p_cal_bytes('降低高水平位后',i.type_id);

end loop;

end p_main;

end pkg_shrink;

/