出处:http://www.itpub.net/252974.html

create or replace procedure print_redo( v_sql varchar2, v_commit boolean default true)
as
v_redo1 number(17,2);
v_redo2 number(17,2);
v_redos number(17,2);
v_redok number(17,2);
begin
/*初始*/
select m.value into v_redo1
from v$mystat m,v$statname s
where m.statistic#=s.statistic# and s.name like '%redo size%';
/*exec sql*/
execute immediate v_sql;
/*结束*/
select m.value into v_redo2
from v$mystat m,v$statname s
where m.statistic#=s.statistic# and s.name like '%redo size%';
/*计算*/
v_redos := v_redo2 - v_redo1;
v_redok := v_redos/1024;
dbms_output.put_line(' trans redo calc ');
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line('my redo size: '||to_char(v_redos)||' (Bytes)');
dbms_output.put_line('my redo size: '||to_char(v_redok)||' (Kbytes)');
dbms_output.put_line('-------------------------------------------');
/*提交否*/
if v_commit = true then
commit;
else
rollback;
end if;
end;

/*demo*/
exec print_redo('delete from a_test',false);

ITPub博客

显示sql语句的redo信息

最新文章