Oracle DBA Lesson 57B — Find TEMP Consumers

A tempfile supplies temporary storage for many successive operations. Its catalog size can remain after a sort finishes, while the allocated space becomes reusable inside the temporary tablespace. To investigate pressure, capture allocations during activity, identify the SQL and sessions involved, and choose a plan, workarea, concurrency, or capacity check from that evidence.

Oracle DBA Lesson 57B — Find TEMP Consumers

This lesson uses read-only Oracle Database 19c examples in a single-instance LABPDB. The queries and sample values below are teaching examples. No database was connected, and no workload was executed to prepare these notes. The exercise is available only in the instructor-designated lab, with the access and bounded workload described below.

File size, allocated segments, and reusable space

Sorts, hash joins, and some other operations use memory workareas in the Program Global Area (PGA). If their allocated memory is insufficient, they may spill intermediate work into temporary storage. Temporary tables, their indexes and LOBs, and temporary undo when enabled can also consume TEMP. An allocation's SEGTYPE helps interpret the kind of temporary segment observed. See logical storage structures for temporary query, table, index, and LOB segments.

Three useful measurements answer different questions:

Measurement	Source	Interpretation
Catalog tempfile size	DBA_TEMP_FILES.BYTES	Logical size of each provisioned tempfile. Files can retain this size after an operation ends.
Consumer segment allocation	V$TEMPSEG_USAGE.BLOCKS and the tablespace's BLOCK_SIZE	Estimated allocated segment bytes visible during the capture. This includes allocated extents rather than a measurement of live row payload.
Reusable and unallocated tablespace space	DBA_TEMP_FREE_SPACE	Tablespace accounting that includes space available for later operations.

In DBA_TEMP_FREE_SPACE, ALLOCATED_SPACE includes both used allocation and allocation available for reuse. FREE_SPACE includes reusable allocation and unallocated space. These categories overlap. Adding them does not produce total capacity, and subtracting them is not a substitute for a timed consumer capture. See DBA_TEMP_FREE_SPACE for allocated and reusable space accounting.

When a query completes, its temporary query segments are released. Later work can reuse storage in the tablespace. Reducing a physical tempfile is a separate maintenance operation. On filesystems that create sparse tempfiles, catalog BYTES describes logical size and can exceed the blocks currently occupied on disk. Check physical storage with the appropriate filesystem or ASM evidence when evaluating capacity. See DBA_TEMP_FILES for catalog size and growth columns, managing tablespaces for sort-extent reuse and separate shrinking, and physical storage structures for sparse tempfile allocation.

Establish the observation scope

Use SQL*Plus with the instructor-provided local COURSE_DBA diagnostic account and approved service for LABPDB. The lab is Oracle 19c, single instance, and uses a normal shared temporary tablespace. RAC, instance-local tempfiles, and cross-container reporting need a different scope and are outside this exercise.

Before running the examples, the instructor must verify:

• The actual database, service, container, release update, account, and metadata visibility. Match the registered lab database identity and confirm LABPDB.
• Read access to SYS.V_$TEMPSEG_USAGE, SYS.V_$SESSION, SYS.DBA_TABLESPACES, SYS.DBA_TEMP_FILES, and SYS.DBA_TEMP_FREE_SPACE for the queries used here. These are selected metadata privileges. The account's name does not imply a DBA role or authorization to alter sessions or storage.
• The selected 19c view exposes SQL_ID_TEMPSEG. Check its definition before the identity example. If it is unavailable, stop that example and have the instructor provide the release-appropriate diagnostic. Do not guess the creator from the current SQL ID.
• The lab's existing temporary assignment and block size, the approved observer and workload sessions, and a finite row, time, and storage bound for any workload. Use an existing approved sort or instructor-captured snapshots. For an optional repeat of Lesson 57A's isolated practice, its owner and reset plan must already be approved.
• Metadata containing unrelated SQL or client identities remains inside the lab review. Only retrieve additional SQL text or plans for the designated workload with separately provisioned read access. No AWR, ASH, SQL Monitor, or tuning pack reports are required by this exercise.

Check the session before each capture:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV','CON_ID') AS container_id,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
       SYSTIMESTAMP AS captured_at
FROM dual;

SHOW is a SQL*Plus command. The SELECT captures database session context and time. Record the intended database and service identity from the lab inventory as well. A database name alone may occur in more than one environment. Continue only in the verified LABPDB session. A root query joining temporary allocations to DBA_TABLESPACES by name can mix same-named tablespaces from different containers. This lesson's join relies on the verified local scope.

Dynamic performance views change as work runs and are not guaranteed to provide a transaction-consistent set of rows across several queries. Capture time and scope with each query, repeat when needed, and treat missing, changed, or unmatched rows as a reason to check visibility and timing.

Oracle's general dynamic-view guidance supports simple extraction and recommends capturing rows into ordinary tables before joins, grouping, or sorting. The course's combined statements below are unexecuted instantaneous inspection patterns, with that support and consistency limitation. See dynamic performance views for simple extraction and consistency limits. Practice aggregation uses instructor-prepared fixed snapshot records by default. The instructor supplies the approved records or pre-existing snapshot tables and their recorded capture context. Provisioning and collection are separate work. Do not create collection tables or grant privileges as part of this read-only lesson. Fixed records describe their capture times and do not establish current live usage.

For a simple extraction, the approved observer can use this pattern in the verified container, preserving the timestamp recorded immediately before it:

SELECT username, sql_id, sql_id_tempseg,
       session_addr, session_num, segtype, tablespace,
       blocks, con_id
FROM v$tempseg_usage
WHERE con_id = TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID'));

For a repeatable supported comparison, aggregate the instructor's fixed records outside the dynamic view with their captured tablespace block sizes. If the instructor has not validated visibility and the instantaneous diagnostic for the lab release, use those records throughout.

Find the largest allocation groups

The primary query preserves the username, SQL identifier, segment type, and tablespace dimensions. It uses the actual block size from the matching local tablespace instead of assuming every tablespace uses 8 KiB blocks.

SELECT u.username, u.sql_id, u.segtype, u.tablespace,
       ROUND(SUM(u.blocks*t.block_size)/1024/1024,2) AS mb
FROM v$tempseg_usage u
JOIN dba_tablespaces t ON t.tablespace_name=u.tablespace
GROUP BY u.username, u.sql_id, u.segtype, u.tablespace
ORDER BY mb DESC;

Column	What to do with it
USERNAME	Identify the account associated with the allocation, and confirm the workload owner.
SQL_ID	Use this reported SQL identifier as a lead for correlation. Also inspect the creator and current identifiers below.
SEGTYPE	Interpret the allocation type, such as SORT, HASH, DATA, INDEX, LOB_DATA, or LOB_INDEX.
TABLESPACE	Check the temporary tablespace being consumed and its local metadata.
MB	The alias is retained from the course query. Division by 1,024 twice gives MiB, estimated from allocated blocks.

The result is grouped allocation, and several sessions or operators can contribute to one group. It is not a unique session or execution identifier. A large group is an investigation lead. The identity query provides more specific context. An empty result can mean the allocation has finished, a short peak was missed, or the observer cannot see the expected rows. Compare the capture with the known workload's timing and verified access. See V$TEMPSEG_USAGE for allocations, types, creator SQL, and session identity.

Worked block calculation

Suppose the captured row represents 2,048 allocated blocks and the matching DBA_TABLESPACES.BLOCK_SIZE is 8,192 bytes:

2,048 blocks × 8,192 bytes/block = 16,777,216 bytes
16,777,216 ÷ 1,024 ÷ 1,024 = 16 MiB

The block size is an input in this hypothetical example. Read the actual value in your lab:

SELECT tablespace_name, contents, block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY'
ORDER BY tablespace_name;

The estimate measures extent allocation. It can include blocks reserved for an operation and does not report a query's exact live payload, total I/O, or memory requirement. The view's SEGFILE# and SEGRFNO# describe the initial extent. One such file number does not establish every tempfile touched by the segment. Use the full tempfile inventory and appropriate additional evidence for a file or I/O investigation. LOB segments are valid TEMP consumers. A sort-only interpretation would omit them. See DBA_TABLESPACES for the actual tablespace block size.

Correlate the session and SQL identities

Keep the reported allocation SQL, the SQL that created the temporary segment, and the session's current SQL separately. Oracle 19c documents SQL_ID_TEMPSEG as the segment creator's SQL identifier, while V$SESSION.SQL_ID describes the current statement.

SELECT SYSTIMESTAMP AS captured_at,
       u.con_id, u.username, u.tablespace, u.segtype,
       u.sql_id AS usage_sql_id,
       u.sql_id_tempseg AS creator_sql_id,
       s.sid, s.serial#, s.status, s.module, s.action,
       s.sql_id AS current_sql_id,
       s.sql_exec_id, s.sql_exec_start,
       ROUND(u.blocks*t.block_size/1024/1024,2) AS mib
FROM v$tempseg_usage u
JOIN dba_tablespaces t
  ON t.tablespace_name = u.tablespace
LEFT JOIN v$session s
  ON s.saddr = u.session_addr
 AND s.serial# = u.session_num
 AND s.con_id = u.con_id
WHERE u.con_id = TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID'))
ORDER BY mib DESC;

SESSION_ADDR matches SADDR. SESSION_NUM is the session serial number, so it matches SERIAL#. Joining only by SQL ID can combine different sessions executing the same statement. Keep CON_ID in the correlation for the selected container. On RAC, a cross-instance diagnosis needs GV$ views and correct INST_ID joins as well. The single-instance query above is not that procedure. See V$SESSION for current SQL, the serial number, and execution context.

The left join preserves an allocation row if its session cannot be matched during the changing capture. An unmatched row needs verification or another snapshot. SID and SERIAL# together help distinguish a session when a SID is reused. MODULE and ACTION, where populated, help connect the allocation to the approved application or exercise. They are descriptive evidence and can change during the session.

For example, a temporary table can retain a session allocation while the session is executing another statement. The creator and current SQL IDs may therefore differ. Identify the actual owner and statement, inspect its relevant execution context, and obtain operational authorization before any later intervention. This lesson does not cancel, disconnect, or kill a session.

Compare activity and capacity across two captures

Record tempfile inventory and tablespace accounting near the consumer capture:

SELECT SYSTIMESTAMP AS captured_at,
       tablespace_name, file_id, file_name, bytes, blocks,
       autoextensible, maxbytes, increment_by
FROM dba_temp_files
ORDER BY tablespace_name, file_id;

SELECT SYSTIMESTAMP AS captured_at,
       tablespace_name,
       ROUND(tablespace_size/1024/1024,2) AS total_mib,
       ROUND(allocated_space/1024/1024,2) AS allocated_mib,
       ROUND(free_space/1024/1024,2) AS free_mib
FROM dba_temp_free_space
ORDER BY tablespace_name;

The following is a hypothetical teaching comparison, not actual SQL output. Assume the designated workload is the only consumer being compared, its sort is captured while active, and the same logical tempfile size is observed at both times. Other allocations and tablespace accounting are deliberately not inferred from these two values.

Capture	Designated workload's grouped allocation	Tempfile catalog BYTES
A, during the bounded sort	COURSE_OWNER, SQL ID 5q1k4m8p2s7t9, SORT, COURSE_TEMP54, 16 MiB	67,108,864 bytes (64 MiB)
B, after that sort finishes	The earlier query allocation row is absent	67,108,864 bytes (64 MiB)

The earlier row gives evidence of allocation at A. Its absence at B fits query completion. The retained file size provides storage for later work. Use the accounting query to observe available and reusable space, and capture other consumers, before asserting that the whole tablespace is idle. These examples do not prove there are exactly 48 MiB free: metadata, other allocations, and timing must be measured. A later empty consumer result cannot reconstruct an earlier peak.

Choose the next investigation

Pattern in the evidence	Next check	Why it helps
One unusually large allocation	Identify the statement and execution, then inspect its plan and workarea behavior.	Excessive join input, a large sort, or repeated spill can account for demand.
Many overlapping consumers	Compare approved session and workload times and scheduling.	Concurrent operations can exceed a capacity that handles them individually.
Sustained legitimate demand	Review tempfile growth settings, peak requirement, and actual disk or ASM capacity.	A configured maximum and physical available space both constrain growth.
DATA, INDEX, or LOB allocation	Identify the temporary object and its transaction or session lifetime.	Temporary tables and their related segments consume TEMP beyond sort work.

PGA is process memory containing SQL workareas. A sort or hash join can run optimally in memory, or use one or more passes through TEMP when its workarea spills. Increasing memory is a separate capacity decision, and the automatic PGA target distributes memory among active workareas. Start with the observed statement and workload, then review memory and physical capacity together. See tuning the PGA for workareas, memory, and spill behavior.

If the instructor has separately provisioned read access to SYS.V_$SQL_WORKAREA_ACTIVE, this optional snapshot adds current workarea evidence without using licensed history reports:

SELECT SYSTIMESTAMP AS captured_at,
       sid, sql_id, sql_exec_id, sql_exec_start,
       operation_type, actual_mem_used, number_passes,
       tempseg_size, tablespace
FROM v$sql_workarea_active
WHERE con_id = TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID'))
ORDER BY tempseg_size DESC NULLS LAST;

ACTUAL_MEM_USED and TEMPSEG_SIZE are byte values. NUMBER_PASSES = 0 indicates optimal workarea operation. TEMPSEG_SIZE is null if the workarea has not yet spilled. Active workareas disappear as they are deallocated, so record time and correlate the approved SQL and execution identity. Small workareas may be omitted from this view. A temporary-table or LOB allocation can have a different lifetime from a current sort or hash workarea. See V$SQL_WORKAREA_ACTIVE for instantaneous workarea statistics.

When TEMP_UNDO_ENABLED is effective for the relevant session, undo generated for temporary objects can be stored in temporary tablespaces. Include this policy and temporary undo demand in the capacity investigation. The documented SEGTYPE values in V$TEMPSEG_USAGE do not provide a general UNDO category. Specialized temporary undo statistics need their own approved read scope. This exercise leaves the parameter and existing temporary-object policy unchanged. See managing temporary undo and TEMP_UNDO_ENABLED for the session and system policy and its restrictions.

Read-only practice and completion criteria

Use two instructor-prepared fixed snapshots for aggregation and interpretation. An optional simple extraction can observe one existing instructor-approved bounded sort in its designated workload session, subject to the support and visibility conditions above. The observer captures metadata while that work is active and again after completion. If the sort stays in memory or completes before capture, use the prepared snapshots. Do not enlarge the workload, force an uncontrolled Cartesian join, or attempt to exhaust TEMP.

1. Record the verified lab, service, account, and container, the timestamp, the existing temporary assignment, and the relevant tablespace block size.
2. Compare consumer groups during activity and after completion. Preserve the exact observed values and distinguish them from the hypothetical example.
3. Calculate one allocation in MiB from blocks and the recorded block size. Correlate the relevant row to its session address and serial, and compare usage, creator, and current SQL identities. Record timing or visibility limitations.
4. Compare tempfile catalog size and tablespace accounting across the captures. Explain reusable allocation and the overlapping accounting columns.
5. State one evidence-supported next check: SQL plan or workarea, overlapping workload, or physical capacity and growth limits. Describe additional evidence needed before any change.

Pass when you can explain the two measurements, perform the block calculation, identify the relevant consumer with the limits of a changing snapshot, and choose a justified next check. This is a read-only diagnosis. No storage, default, assignment, quota, parameter, or session change is part of its completion.

Exit the observer normally after saving approved observations. The SELECTs create no lab objects to remove and have no transaction to undo. If the exercise used Lesson 57A's previously approved scratch setup, its owner follows that lesson's captured assignment and session restoration and exact cleanup procedure. Leave normal TEMP and all unrelated files intact. Shrink, recreation, and physical reclamation require a separate maintenance plan with active use, assignments, dependencies, file inventory, and restoration checked. A live peak is outside that plan.

Recap

Capture TEMP allocations while the work is active. Catalog tempfile size can remain after a sort finishes, while allocated space becomes reusable. Group consumers by username, SQL ID, segment type, and tablespace, and convert blocks with the matching tablespace block size. Correlate SESSION_ADDR to SADDR and SESSION_NUM to SERIAL#, and keep the usage, creator, and current SQL IDs separate. Compare tempfile catalog size and free-space accounting across two captures, then choose a plan, workarea, concurrency, or capacity check from the evidence. This is a read-only diagnosis. Do not change storage, defaults, assignments, quotas, parameters, or sessions.

Quiz

1. Does a large tempfile after the sort finishes mean the space cannot be reused?

Yes; every completed sort permanently consumes its tempfile. No; allocated temporary storage may be available for reuse by later operations. Yes; the file must always be deleted before another query runs. No; every tempfile automatically shrinks to zero after each sort.

2. A captured allocation has 2,048 blocks and the observed tablespace block size is 8,192 bytes. How much allocated space does this represent?

2 MiB. 8 MiB. 16 MiB. 2,048 MiB.

3. Which single-instance session correlation matches the TEMP allocation's session identity?

SQL ID alone. Username alone. SESSION_ADDR = SADDR, SESSION_NUM = SERIAL#, with matching CON_ID. SESSION_NUM = SID, ignoring container scope.

4. Which identifier explicitly describes the SQL statement that created the temporary segment in the documented 19c view?

V$TEMPSEG_USAGE.SQL_ID_TEMPSEG. V$SESSION.SQL_ID, in every case. DBA_TEMP_FILES.FILE_ID. DBA_TABLESPACES.BLOCK_SIZE.

5. Many approved sessions each allocate modest TEMP space, and their executions overlap during the pressure. What is a useful next check?

Delete the largest normal tempfile immediately. Assume the last session captured caused every earlier peak. Force a larger Cartesian join to reproduce exhaustion. Compare concurrency and scheduling, alongside observed peak demand and capacity.

The queries and sample values in this lesson are teaching examples. No database was connected, and no workload was executed to prepare these notes. Run the read-only practice in the instructor-designated Oracle Database 19c lab, confirm the exact release update, and record what you actually observe.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro