一个消除链接行脚本

declare
  v_owner      varchar2(30):=upper('&owner');
  v_table_name varchar2(30):=upper('&table_name');
  v_pctfree    number:=&pct_free;
  v_pctused    number:=&pct_used;
  v_chain_cnt  number:=0;
  v_count      number:=0;
begin
  execute immediate 'analyze table '||v_owner||'.'||v_table_name||' estimate statistics sample 10 percent';
  select chain_cnt into v_chain_cnt from dba_tables where wner=v_owner and table_name=v_table_name;
  if v_chain_cnt>0 then
    select count(*) into v_count from user_tables where table_name='CHOCHO_CHAINED_ROWS';
     if v_count>0 then
       execute immediate 'drop table chocho_chained_rows ';
     end if;
    execute immediate 'create table chocho_chained_rows ('||'owner_name varchar2(30),'||
                                                            'table_name varchar2(30),'||
                                                            'cluster_name varchar2(30),'||
                                                            'partition_name varchar2(30),'||
                                                            'subpartition_name varchar2(30),'||
                                                            'head_rowid ROWID,'||
                                                            'analyze_timestamp DATE ) ';
    dbms_output.put_line('Number of chained rows for < '||v_owner||'.'||v_table_name||'> = '||to_char(v_chain_cnt));                                                       
    execute immediate ' analyze table '||v_owner||'.'||v_table_name||' list chained rows into chocho_chained_rows';
    execute immediate ' create table chocho_chained_temp as select * from  '||v_owner||'.'||v_table_name||' where rowid in'||'
                      (select head_rowid from chocho_chained_rows)';
    execute immediate ' delete from  '||v_owner||'.'||v_table_name||' where rowid in '||'(select head_rowid from chocho_chained_rows)';
    execute immediate ' alter table  '||v_owner||'.'||v_table_name||' pctfree '||v_pctfree|| 'pctused '||v_pctused;
    execute immediate ' insert into  '||v_owner||'.'||v_table_name||' select * from chocho_chained_temp';
    execute immediate ' drop table chocho_chained_rows';
    execute immediate ' drop table chocho_chained_temp';
    dbms_output.put_line('chained rows eliminated');
   else
    dbms_output.put_line('there are no chained rows for ');
   end if ;
  exception
   when others then
    dbms_output.put_line('ERROR:'||SQLERRM);
end;
/ 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10756358/viewspace-231067/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10756358/viewspace-231067/