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;

Redo Byte Address (RBA)

Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of

• the log file sequence number (4 bytes)
• the log file block number (4 bytes)
• the byte offset into the block at which the redo record starts (2 bytes)
• (全文…)
  

今天没事看测试了下表在nologging和logging模式下,使用APPEND产生REDO多少?注数据库是归档模式,非归档模式有时间我在试下.

SQL> set autotrace traceonly;
SQL> set time on;
15:43:35 SQL> insert into t2 select * from dba_objects;  这里表T2是处于nologging模式的..

(全文…)