关于Oracle数据库中行迁移/行链接的问题[12]

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