Oracle中行迁移和行链接的清除及检测

核心提示： SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';可以使用如下的脚本来直接查找存在有行链接和行迁移的表，自动完成所有的分析和统计，Oracle中行迁移和行链接的清除及检测(6

SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

可以使用如下的脚本来直接查找存在有行链接和行迁移的表，自动完成所有的分析和统计。

accept owner prompt " Enter the schema name
to check for Row Chaining (RETURN for All): "
prompt
prompt
accept table prompt " Enter the table name
to check (RETURN for All tables owned by &owner): "
prompt
prompt
set head off serverout on term on feed off veri off echo off
!clear
prompt
declare
v_owner varchar2(30);
v_table varchar2(30);
v_chains number;
v_rows number;
v_count number := 0;
sql_stmt varchar2(100);
dynamicCursor INTEGER;
dummy INTEGER;
cursor chains is
select count(*) from chained_rows;
cursor analyze is
select owner, table_name
from sys.dba_tables
where owner like upper('%&owner%')
and table_name like upper('%&table%')
order by table_name;
begin
dbms_output.enable(64000);
open analyze;
fetch analyze into v_owner, v_table;
while analyze%FOUND loop
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'analyze table '||v_owner
||'.'||v_table||' list chained rows into chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
open chains;
fetch chains into v_chains;
if (v_chains != 0) then
if (v_count = 0) then
dbms_output.put_line(CHR(9)||CHR(9)||CHR(9)
||'<<<<< Chained Rows Found >>>>>');
v_count := 1;
end if;
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'Select count(*) v_rows'||' From '||v_owner||'.'||v_table;
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);
dummy := dbms_sql.execute(dynamicCursor);
dummy := dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner||'.'||v_table);
dbms_output.put_line(CHR(9)||'---> Has '
||v_chains||' Chained Rows and '||v_rows||' Num_Rows in it!');
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'truncate table chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains := 0;
end if;
close chains;
fetch analyze into v_owner, v_table;
end loop;
if (v_count = 0) then
dbms_output.put_line('No Chained Rows found
in the '||v_owner||' owned Tables!');
end if;
close analyze;
end;
/
set feed on head on
prompt

三、行迁移和行链接的清除