demo06.sql

来自「Expert Oracle Database Architecture 9i a」· SQL 代码 · 共 58 行

create table perm
( x char(2000) ,
  y char(2000) ,
  z char(2000)  )
/
create global temporary table temp
( x char(2000) ,
  y char(2000) ,
  z char(2000)  )
on commit preserve rows
/
create or replace procedure do_sql( p_sql in varchar2 )
as
    l_start_redo    number;
    l_redo            number;
begin
    select v$mystat.value
      into l_start_redo
      from v$mystat, v$statname
     where v$mystat.statistic# = v$statname.statistic#
       and v$statname.name = 'redo size';

    execute immediate p_sql;
    commit;

    select v$mystat.value-l_start_redo
      into l_redo
      from v$mystat, v$statname
     where v$mystat.statistic# = v$statname.statistic#
       and v$statname.name = 'redo size';

    dbms_output.put_line
    ( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' ||
      substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
end;
/
set serveroutput on format wrapped
begin
    do_sql( 'insert into perm
             select 1,1,1
               from all_objects
              where rownum <= 500' );

    do_sql( 'insert into temp
             select 1,1,1
               from all_objects
              where rownum <= 500' );
        dbms_output.new_line;

    do_sql( 'update perm set x = 2' );
    do_sql( 'update temp set x = 2' );
        dbms_output.new_line;

    do_sql( 'delete from perm' );
    do_sql( 'delete from temp' );
end;
/