選出有行連線(row chain)或者是行遷移(row migeration)的表

自己編寫的一個小指令碼，該指令碼的主要功能是選出有行遷移或者行連線的表，並且按照行遷移/行連線降序輸出OWNER.TABLE_NAME,該指令碼沒有統計ORACLE系統內建的表，如果表的索引狀態為unusable,也不能統計，請在資料庫空閒的時候執行該指令碼。

 嚴重警告:請別在生產環境中亂用該指令碼，後果自負

set serveroutput on
set linesize 200
set pagesize 100
declare
TYPE tablename IS RECORD
( owner dba_tables.owner%TYPE,
  table_name dba_tables.table_name%TYPE,
  chain_cnt dba_tables.chain_cnt%TYPE
);
dbatables tablename;
cursor table_name is
select owner,table_name from dba_tables where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and owner not in (select owner from dba_indexes where status='UNUSABLE');
cursor table_name2 is
select owner,table_name,chain_cnt from dba_tables where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS')
and chain_cnt>0 order by chain_cnt desc;
cursor spetial is select owner,table_name from dba_indexes where status='UNUSABLE';
begin
  for tablename in table_name loop
execute immediate 'analyze table '|| tablename.owner ||'.'||tablename.table_name || ' compute statistics ';
end loop;
  for c_spetial in spetial loop
dbms_output.put_line('UNUSABLE index in ' || c_spetial.owner ||'.' ||c_spetial.table_name || ' , Can not analyze this table');
end loop;
open table_name2;
loop
fetch table_name2 into dbatables;
if(table_name2%ROWCOUNT=0) then
dbms_output.put_line('No Chained Rows Found !!!');
else
dbms_output.put_line(dbatables.chain_cnt || ' chained rows found in ' ||  dbatables.owner ||'.'|| dbatables.table_name);
end if;
exit when table_name2%NOTFOUND;
end loop;
end;
/

例子：

SQL> set serveroutput on
SQL> set linesize 200
SQL> set pagesize 100
SQL> declare
  2  TYPE tablename IS RECORD
  3  ( owner dba_tables.owner%TYPE,
  4    table_name dba_tables.table_name%TYPE,
  5    chain_cnt dba_tables.chain_cnt%TYPE
  6  );
  7  dbatables tablename;
  8  cursor table_name is
  9  select owner,table_name from dba_tables where owner not in
 10  ('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and owner not in (select owner from dba_indexes where status='UNUSA
BLE');
 11  cursor table_name2 is
 12  select owner,table_name,chain_cnt from dba_tables where owner not in
 13  ('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS')
 14  and chain_cnt>0 order by chain_cnt desc;
 15  cursor spetial is select owner,table_name from dba_indexes where status='UNUSABLE';
 16  begin
 17    for tablename in table_name loop
 18  execute immediate 'analyze table '|| tablename.owner ||'.'||tablename.table_name || ' compute statistics ';
 19  end loop;
 20    for c_spetial in spetial loop
 21  dbms_output.put_line('UNUSABLE index in ' || c_spetial.owner ||'.' ||c_spetial.table_name || ' , Can not analyze this table');
 22  end loop;
 23  open table_name2;
 24  loop
 25  fetch table_name2 into dbatables;
 26  if(table_name2%ROWCOUNT=0) then
 27  dbms_output.put_line('No Chained Rows Found !!!');
 28  else
 29  dbms_output.put_line(dbatables.chain_cnt || ' chained rows found in ' ||  dbatables.owner ||'.'|| dbatables.table_name);
 30  end if;
 31  exit when table_name2%NOTFOUND;
 32  end loop;
 33  end;
 34  /
UNUSABLE index in SCOTT.EMP , Can not analyze this table
No Chained Rows Found !!!

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16978544/viewspace-705703/，如需轉載，請註明出處，否則將追究法律責任。