Temporary LOBs in Oracle 23ai

A temporary LOB is a session-private CLOB/BLOB locator whose data is stored in the database TEMP (temporary tablespace) instead of a permanent tablespace. In Oracle 23ai, temporary LOBs are a practical tool when you need a “scratchpad” for large text or binary content while a PL/SQL routine runs—without committing that content to a permanent table.

Why temporary LOBs matter (performance + correctness)

• Staging for large content: assemble large XML/JSON documents, generate reports, build outbound payloads, concatenate many fragments, or buffer file contents before writing to a permanent SecureFiles LOB.
• Reduce permanent writes: avoid creating rows “just for processing” when the content is intermediate.
• Controlled lifecycle: you choose when to allocate and when to free space, which is important for TEMP-heavy workloads.
• Better tuning visibility: large temporary LOB activity shows up as TEMP usage and can be managed as a capacity/perf concern.

Key idea: a LOB variable in PL/SQL is a locator (a pointer-like value), not the payload itself. Temporary LOBs give you a locator backed by TEMP, which you can write into and then free explicitly.

Procedure / Function	Purpose
DBMS_LOB.CREATETEMPORARY	Creates a temporary LOB locator and allocates space in TEMP as you write to it. You can create a locator that lasts for the session or only for the current call.
DBMS_LOB.FREETEMPORARY	Frees TEMP space used by the temporary LOB and invalidates the locator. Good practice: free explicitly as soon as you are done.
DBMS_LOB.ISTEMPORARY	Returns whether a locator points to a temporary LOB (commonly used as = 1 or = 0 checks).

1) Create a temporary LOB

CREATETEMPORARY gives you a locator backed by TEMP. You choose: cache (buffer cache usage) and duration (session vs call). For many large workloads, avoid caching enormous payloads unless you have a clear reason.

DECLARE
  l_tmp  CLOB;
BEGIN
  -- cache => FALSE reduces buffer cache pressure for very large temporary payloads
  -- dur   => DBMS_LOB.SESSION keeps it valid for the session (until freed or session ends)
  DBMS_LOB.CREATETEMPORARY(l_tmp, cache => FALSE, dur => DBMS_LOB.SESSION);

  -- Always free when done (shown later)
END;

2) Write in chunks (preferred for large content)

For big payloads, write in chunks instead of repeated || concatenation. The LOB APIs avoid repeated reallocations and keep the approach scalable.

DECLARE
  l_tmp   CLOB;
  l_piece VARCHAR2(32767);
BEGIN
  DBMS_LOB.CREATETEMPORARY(l_tmp, cache => FALSE, dur => DBMS_LOB.SESSION);

  l_piece := 'Header line' || CHR(10);
  DBMS_LOB.WRITEAPPEND(l_tmp, LENGTH(l_piece), l_piece);

  l_piece := 'Body line' || CHR(10);
  DBMS_LOB.WRITEAPPEND(l_tmp, LENGTH(l_piece), l_piece);

  DBMS_OUTPUT.PUT_LINE('Length = ' || DBMS_LOB.GETLENGTH(l_tmp));

  DBMS_LOB.FREETEMPORARY(l_tmp);
END;

This is a common tuning and correctness pitfall: selecting a LOB column into a LOB variable assigns the locator—it does not copy data. If your target variable was a temporary LOB, the assignment overwrites your temporary locator.

Correct approach: keep the temporary locator and copy the bytes/chars into it.

DECLARE
  l_tmp     CLOB;
  l_src     CLOB;
  l_amount  INTEGER;
BEGIN
  DBMS_LOB.CREATETEMPORARY(l_tmp, cache => FALSE, dur => DBMS_LOB.SESSION);

  SELECT some_clob
  INTO   l_src
  FROM   some_table
  WHERE  id = 42;

  l_amount := DBMS_LOB.GETLENGTH(l_src);

  -- Copy payload into the temporary LOB (preserves l_tmp as a temp locator)
  DBMS_LOB.COPY(
    dest_lob    => l_tmp,
    src_lob     => l_src,
    amount      => l_amount,
    dest_offset => 1,
    src_offset  => 1
  );

  -- ...process l_tmp...

  DBMS_LOB.FREETEMPORARY(l_tmp);
END;

4) Free the temporary LOB (always)

Oracle can clean up session-duration temporary LOBs when the session ends, but in real systems (connection pooling, long-running sessions, batch jobs) “end of session” might be hours or days away. Freeing early reduces TEMP pressure.

DECLARE
  l_tmp CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(l_tmp, cache => FALSE, dur => DBMS_LOB.SESSION);

  -- ...use l_tmp...

  IF DBMS_LOB.ISTEMPORARY(l_tmp) = 1 THEN
    DBMS_LOB.FREETEMPORARY(l_tmp);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Best practice: cleanup even on errors
    IF DBMS_LOB.ISTEMPORARY(l_tmp) = 1 THEN
      DBMS_LOB.FREETEMPORARY(l_tmp);
    END IF;
    RAISE;
END;

Diagrams: what the key calls do

---

Tuning notes for Oracle 23ai (practical guidance)

• TEMP is part of your design: temporary LOBs consume TEMP. If your workload builds large payloads concurrently, size and monitor TEMP accordingly (especially with connection pools and batch jobs).
• Prefer chunked writes: repeated string concatenation can create unnecessary copies. Use DBMS_LOB.WRITEAPPEND (or WRITE) for scalable assembly.
• Free early: explicitly free temporary LOBs in normal and error paths to avoid “quiet” TEMP growth.
• Permanent LOBs: when the final output must be stored, use a permanent LOB column (commonly SecureFiles LOB) and copy from the temporary LOB into the permanent LOB at the end of the workflow.