梦想有多远

CREATE OR REPLACE PROCEDURE do_work (p_what IN VARCHAR2)
AS
   l_redo_size   NUMBER;
   l_cnt         NUMBER := 200;

   PROCEDURE report (l_op IN VARCHAR2)
   IS
   BEGIN
      SELECT v$mystat.VALUE - l_redo_size
        INTO l_redo_size
        FROM v$mystat, v$statname
       WHERE v$mystat.statistic# = v$statname.statistic#
         AND v$statname.NAME = ‘redo size’;

      DBMS_OUTPUT.put_line (   l_op
                            || ‘ redo size = ‘
                            || l_redo_size
                            || ‘ rows = ‘
                            || l_cnt
                            || ‘ ‘
                            || TO_CHAR (l_redo_size / l_cnt, ‘99,999.9′)
                            || ‘ bytes/row’
                           );

      INSERT INTO LOG
         SELECT p_what, l_op, data_length, l_redo_size, l_cnt
           FROM user_tab_columns
          WHERE table_name = ‘T’ AND column_name = ‘Y’;
   END;

   PROCEDURE set_redo_size
   AS
   BEGIN
      SELECT v$mystat.VALUE
        INTO l_redo_size
        FROM v$mystat, v$statname
       WHERE v$mystat.statistic# = v$statname.statistic#
         AND v$statname.NAME = ‘redo size’;
   END;
BEGIN
   set_redo_size;

   INSERT INTO t
      SELECT object_id, object_name, created
        FROM all_objects
       WHERE ROWNUM <= l_cnt;

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’insert’);
   set_redo_size;

   UPDATE t
      SET y = LOWER (y);

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’update’);
   set_redo_size;

   DELETE FROM t;

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’delete’);
END;

This entry was posted on 星期四, 十一月 15th, 2007 at 18:32 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.