Oracle DBMS_OUTPUT: Multiple ENABLE() Calls Behavior

Tested on: Oracle Database 23.26 Free
Date: 2025-12-25

Overview

This report documents the behavior when DBMS_OUTPUT.ENABLE() is called multiple times within a session, based on empirical testing against a real Oracle database.

Oracle Documentation states: "If there are multiple calls to ENABLE, then buffer_size is the last of the values specified."

1. Basic Behavior

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.PUT_LINE('Line 1');
DBMS_OUTPUT.ENABLE(10000);  -- Buffer NOT cleared
DBMS_OUTPUT.PUT_LINE('Line 2');
-- Output: Both lines appear

2. Shrinking Buffer Size

When calling ENABLE(smaller_size) after ENABLE(larger_size):

2.1 Empty Buffer

New smaller limit is enforced strictly.

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.ENABLE(2500);  -- Shrink while empty
DBMS_OUTPUT.PUT_LINE(RPAD('X', 2600, 'X'));  -- FAILS: exceeds 2500

2.2 Buffer Content < New Limit

New limit is enforced for total (existing + new).

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.PUT_LINE(RPAD('X', 1000, 'X'));  -- 1000 bytes
DBMS_OUTPUT.ENABLE(2000);  -- Shrink to 2000
DBMS_OUTPUT.PUT_LINE(RPAD('Y', 1500, 'Y'));  -- FAILS: 1000+1500 > 2000

2.3 Buffer Content >= New Limit (Key Finding)

Additive capacity behavior: When existing content already exceeds the new limit, Oracle expands capacity to approximately first_size + second_size.

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(4000);
DBMS_OUTPUT.PUT_LINE(RPAD('X', 3500, 'X'));  -- 3500 > 3000
DBMS_OUTPUT.ENABLE(3000);  -- Shrink, but content exceeds new limit
-- Effective capacity becomes ~7000 (4000 + 3000)
DBMS_OUTPUT.PUT_LINE(RPAD('Y', 3000, 'Y'));  -- OK: total ~6500 < 7000

3. Effect of GET_LINES() After Shrinking

After GET_LINES() flushes the buffer, the additive capacity is lost. The limit reverts to the last ENABLE value.

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(4000);
DBMS_OUTPUT.PUT_LINE(RPAD('X', 3500, 'X'));
DBMS_OUTPUT.ENABLE(3000);  -- Additive: capacity ~7000
DBMS_OUTPUT.GET_LINES(lines, numlines);  -- Flush
-- Capacity now reverts to 3000
DBMS_OUTPUT.PUT_LINE(RPAD('Y', 2999, 'Y'));  -- OK
DBMS_OUTPUT.PUT_LINE('Z');  -- OK (total 3000)
DBMS_OUTPUT.PUT_LINE('A');  -- FAILS: exceeds 3000

4. Buffer Reuse After GET_LINES()

GET_LINES() frees buffer space. The limit applies to current contents, not cumulative lifetime writes.

DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(2000);
DBMS_OUTPUT.PUT_LINE(RPAD('X', 1500, 'X'));  -- 1500
DBMS_OUTPUT.GET_LINES(lines, numlines);      -- Flush
DBMS_OUTPUT.PUT_LINE(RPAD('Y', 1500, 'Y'));  -- OK: buffer was cleared
DBMS_OUTPUT.GET_LINES(lines, numlines);      -- Flush again
DBMS_OUTPUT.PUT_LINE(RPAD('Z', 1500, 'Z'));  -- OK: can reuse indefinitely

5. Summary Table

6. Practical Implications

1. Don't rely on shrinking: Calling ENABLE(smaller) doesn't reclaim memory or enforce the smaller limit if content already exceeds it.

2. Use DISABLE to reset: If you need a clean slate, call DISABLE() first.

3. GET_LINES resets capacity: After flushing, the additive capacity bonus is lost.

4. Application code should avoid ENABLE/DISABLE: Per Oracle documentation, these calls can interfere with tools like SQL*Plus that manage output display.

7. Buffer Size Constraints

References

• Oracle DBMS_OUTPUT.ENABLE Documentation