Tom,

Regarding this "circular log" concept, I understand that there's no way to specify a table to wrap its rows around when the number of rows hits some cap, but what would you say about using a procedure like the following to incorporate the logic?  Assume that the logging would be controlled, so the table would only be inserted into via this procedure, and obviously one row at a time.

This code was run on a 9i database.

CREATE TABLE circular_log (
    rotating_id     NUMBER
,   seq             NUMBER
,   text            VARCHAR2(100)
)
/
CREATE SEQUENCE circular_log_seq
INCREMENT BY 1
START WITH 1
CACHE 20
NOCYCLE
/
CREATE OR REPLACE PROCEDURE log_issue (p_log_text VARCHAR2)
IS
    PRAGMA      AUTONOMOUS_TRANSACTION;
    l_rotating_id          POSITIVE;
    l_max_rows  CONSTANT   PLS_INTEGER := 20;
BEGIN
    BEGIN
        -- 1. Look up the ID of the row having the maximum
        --    seq.
        -- 2. The ID of interest will be this maximum
        --    seq's ID + 1, unless we're at the limit,
        --    in which case the ID of interest becomes 1.
        SELECT CASE
               WHEN cl_max.rotating_id + 1 > l_max_rows
               THEN 1
               ELSE cl_max.rotating_id + 1
               END
        INTO   l_rotating_id
        FROM   circular_log      cl_max
        WHERE  cl_max.rotating_id =
              (SELECT a.rotating_id
               FROM  (SELECT   cl.rotating_id
                      FROM     circular_log   cl
                      ORDER BY cl.seq DESC) a
               WHERE  ROWNUM = 1)
        FOR UPDATE;
    EXCEPTION
        -- The very first time we insert into this table,
        -- the above SELECT will raise a NO_DATA_FOUND, so
        -- start counting at 1.
        WHEN NO_DATA_FOUND THEN
            l_rotating_id := 1;
    END;

    -- 3. If the ID of interest is already in the table,
    --    then just update the SEQ and TEXT columns of 
    --    that ID.
    --    If the ID of interest is not already in the table,
    --    then insert it, along with SEQ and TEXT.
    MERGE INTO circular_log         cl
    USING     (SELECT l_rotating_id rotating_id FROM DUAL) x
    ON        (cl.rotating_id = x.rotating_id)
    WHEN       MATCHED THEN
               UPDATE
               SET    cl.seq  = circular_log_seq.NEXTVAL
               ,      cl.text = p_log_text
    WHEN   NOT MATCHED THEN
               INSERT (rotating_id
               ,       seq
               ,       text
               ) VALUES (
                       x.rotating_id
               ,       circular_log_seq.NEXTVAL
               ,       p_log_text
               );
    COMMIT;
END log_issue;
/

SQL> BEGIN
  2  FOR i IN 1..24 LOOP log_issue(TO_CHAR(TO_DATE(i,'J'),'jsp')); END LOOP;
  3  END;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM circular_log ORDER BY seq DESC
  2  /
 
ROTATING_ID        SEQ TEXT
----------- ---------- -----------------------------------------------------
          4         24 twenty-four
          3         23 twenty-three
          2         22 twenty-two
          1         21 twenty-one
         20         20 twenty
         19         19 nineteen
         18         18 eighteen
         17         17 seventeen
         16         16 sixteen
         15         15 fifteen
         14         14 fourteen
         13         13 thirteen
         12         12 twelve
         11         11 eleven
         10         10 ten
          9          9 nine
          8          8 eight
          7          7 seven
          6          6 six
          5          5 five
 
20 rows selected.
 
SQL>