Oct 30, 2007

Script que entrega cantidad de Filas en Oracle

Este script es útil a la hora de calcular las estadisticas para saber a que tablas es conveniente aplicar esto. Adjuntto script:

spool cantidad_de_filas.txt

SET SERVEROUTPUT ON
declare
v_ct number := 0 ;
v_sqlcode number := 0 ;
v_stmt varchar2(90) ;
cursor c_tbl is
select owner, table_name
from sys.dba_tables
where owner = 'SAMA'
and rownum < 11
;
r_tbl c_tbl%rowtype;
begin
open c_tbl;
loop
fetch c_tbl into r_tbl;
exit when c_tbl%notfound;
v_stmt := 'select count(*) from '||r_tbl.owner||'.'||r_tbl.table_name;
execute immediate v_stmt into v_ct;
v_sqlcode := SQLCODE;
if v_sqlcode = 0
then dbms_output.put_line('Table '||r_tbl.owner||'.'||
rpad(r_tbl.table_name,30)||
' count is '||to_char(v_ct,'999999999990')
);
else dbms_output.put_line('Bad return code'||v_sqlcode||
' on select of '||r_tbl.owner||
'.'||r_tbl.table_name
);
end if;
end loop;
close c_tbl;
end;
/
spool off

No comments:

Post a Comment