demo05.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table t as select * from all_objects;
create index t_idx on t(object_name);
exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
create undo tablespace undo_small
datafile size 2m
autoextend off
/
alter system set undo_tablespace = undo_small;
begin
    for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
                 from t
                where object_name > ' ' )
    loop
        update t
           set object_name = lower(x.object_name)
         where rowid = x.rid;
        if ( mod(x.r,100) = 0 ) then
           commit;
        end if;
   end loop;
   commit;
end;
/

pause

create table to_do
as
select distinct substr( object_name, 1,1 ) first_char
  from T
/
begin
        for x in ( select * from to_do )
        loop
            update t set last_ddl_time = last_ddl_time+1
             where object_name like x.first_char || '%';

            dbms_output.put_line( sql%rowcount || ' rows updated' );
            delete from to_do where first_char = x.first_char;

            commit;
        end loop;
end;
/