Oracle DBA Lesson 57A — Provide Temporary Space for a Workload

Sorts and hash joins use work areas to hold intermediate results in memory. When an operation needs disk space, Oracle writes intermediate data into temporary segments in its assigned temporary tablespace. This lesson provisions bounded temporary storage and explains how a workload receives its assignment.

Oracle DBA Lesson 57A — Provide Temporary Space for a Workload

What TEMP supplies

A query can read a permanent table while its sort or hash operation uses temporary storage. The table's rows remain in permanent datafiles. The intermediate work uses tempfiles. Temporary tablespaces also support other transient objects, such as temporary tables, indexes, and LOBs. This exercise focuses on sort and hash work.

A work area is memory used by a SQL operator. A sort uses it to order rows. A hash join uses it to build and probe intermediate hash structures. In the dedicated-server baseline, these SQL work areas use PGA memory. A workload that exceeds its available work-area memory can spill to disk. Investigating the SQL plan, current consumers, and memory pressure belongs to Lesson 57B. Here the task is to provide suitable storage and assignment. See Tuning the Program Global Area for SQL work areas and spill behavior.

Temporary contents serve the operation or session. Treat the configured tempfile as storage infrastructure. It is not a permanent copy of the source table's data.

Bounded tempfile plan

The example creates COURSE_TEMP54 in the designated LABPDB using the verified effective Oracle Managed Files destination:

CREATE TEMPORARY TABLESPACE course_temp54
  TEMPFILE SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE 256M;

Clause	Meaning
CREATE TEMPORARY TABLESPACE	Provide a temporary tablespace for transient work
TEMPFILE	Use temporary-file storage
SIZE 64M	Set an initial logical tempfile size of 64 MiB
AUTOEXTEND ON NEXT 16M	Permit autoextension with a configured 16 MiB increment
MAXSIZE 256M	Set a 256 MiB automatic-growth ceiling

The lab uses binary units: one MiB is 1,048,576 bytes. The initial size is 67,108,864 bytes, the configured increment is 16,777,216 bytes, and the ceiling is 268,435,456 bytes. These small values are lab choices, not production sizing guidance. Growth arithmetic and shared-capacity planning were covered in Lesson 56A.

Logical size and physical capacity. On some operating systems, tempfile blocks receive physical storage only when accessed. DBA_TEMP_FILES.BYTES reports the file's logical size. A successful create or a 64 MiB dictionary value therefore requires a separate assessment of usable physical capacity, including later access, growth, other files sharing the destination, and operating margin. Have the instructor check the actual filesystem or ASM allocation behavior and usable capacity. MAXSIZE bounds autoextension. It does not reserve its ceiling's bytes. See CREATE TABLESPACE and Managing Tablespaces for temporary storage and platform allocation behavior.

With an unnamed TEMPFILE, Oracle generates a filename at the effective OMF destination. The filesystem directory must already exist and be accessible to the database process. The example does not set a destination or change a parameter. See DB_CREATE_FILE_DEST and Managing Data Files and Temp Files for the destination and file-management prerequisites.

Verify before assigning a user

Query the new tempfile in the same PDB:

SELECT f.tablespace_name, f.file_id, f.file_name, f.status,
       f.bytes, f.autoextensible, f.maxbytes,
       f.increment_by, t.block_size,
       f.increment_by * t.block_size AS configured_next_bytes
FROM dba_temp_files f
JOIN dba_tablespaces t
  ON t.tablespace_name = f.tablespace_name
WHERE f.tablespace_name = 'COURSE_TEMP54'
ORDER BY f.file_id;

SELECT tablespace_name, contents, block_size, extent_management
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_TEMP54';

Record each actual FILE_NAME and verify that it belongs to the instructor-approved destination. Initially expect one online tempfile with BYTES = 67108864, AUTOEXTENSIBLE = YES, MAXBYTES = 268435456, and configured_next_bytes = 16777216. CONTENTS should be TEMPORARY and extent management LOCAL. These are expected checks, not captured results. See DBA_TEMP_FILES for the file metadata.

INCREMENT_BY is a count of Oracle blocks. Multiply it by the tablespace's actual BLOCK_SIZE to convert it to bytes. For 8,192-byte blocks, 2,048 blocks correspond to 16 MiB. Read the real block size instead of assuming it. A mismatch in placement, status, or settings is a reason to stop before assignment and correct the approved plan.

Who receives this storage?

Assignment	Scope and consequence
Per-user temporary tablespace	Directs the designated user's temporary work to the specified tablespace
Container default temporary tablespace	Supplies the assignment for users without an explicit temporary choice in that container
Temporary tablespace group	Makes several member temporary tablespaces available to an assigned user. It can also be a default.

An explicitly authorized per-user test uses:

ALTER USER course_owner TEMPORARY TABLESPACE course_temp54;

This changes COURSE_OWNER's temporary assignment. It leaves the permanent tablespace and permanent quota in place. Permanent quotas govern permanent segment allocation. The QUOTA clause cannot be applied to a temporary tablespace. Plan and control temporary demand separately. See ALTER USER and CREATE USER for assignment syntax and the quota restriction.

The database-default command has wider scope because it can affect users without an explicit assignment. This exercise does not change the container default, create or change a tablespace group, or alter local-temporary policy. Existing group and default policies must be preserved. See Managing Tablespaces for groups and assignment defaults, and DBA_USERS and DBA_TABLESPACE_GROUPS for assignment metadata.

Recovery inventory

RMAN tracks tempfile names for recreation. It does not back up or restore tempfile contents as permanent data. Keep an inventory of the temporary tablespace, filenames, sizes, status, growth attributes, effective destination, and user, default, and group assignments so the recovery runbook can re-establish usable temporary storage.

Oracle documents recreation of missing temporary storage recorded in the control file after whole-database restore and recovery and opening. OMF recreation uses the current DB_CREATE_FILE_DEST. Explicit files use their recorded locations. Existing files with invalid headers are excluded from that automatic recreation. An I/O or recreation failure can be reported in the alert log while database opening continues. This is a documented scenario, not a guarantee for every PDB or recovery workflow. See RESTORE and Complete Database Recovery for tempfile recreation and scenario-specific postchecks.

After an instructor-led recovery drill, verify DBA_TEMP_FILES, destination availability, permissions, usable capacity, growth settings, and assignments. Resolve any missing or invalid tempfile using the rehearsed recovery runbook before resuming workload. Do not treat an open database alone as TEMP validation. This lesson's examples perform no recovery, manual re-add, or deletion.

Lab prerequisites and operating conditions

Use Oracle Database 19c on Linux, a single instance with dedicated test sessions, SQL*Plus, and the instructor-designated LABPDB service. The examples below have not been executed against a database. No actual release update, storage report, or query result is claimed.

The instructor must provide:

• A disposable lab, the exact database and PDB identity, the current release update, and a tested reset and recovery procedure before the scratch drop.
• Local COURSE_DBA authorization for CREATE TABLESPACE, DROP TABLESPACE, and the permitted ALTER USER operation. The account also needs reads on SYS.DBA_TABLESPACES, SYS.DBA_TEMP_FILES, SYS.DBA_USERS, SYS.DBA_TABLESPACE_GROUPS, SYS.DATABASE_PROPERTIES, and suitable SYS.V_$PARAMETER access for SHOW PARAMETER. Dependency checking needs SYS.V_$TEMPSEG_USAGE and instructor session monitoring.
• An approved, existing, writable effective OMF destination with finite usable capacity. Neither a filename prefix nor dictionary BYTES establishes physical headroom.
• COURSE_TEMP54 absent from tablespaces and group names. The exercise must own the new object and its exact generated tempfile inventory.
• A designated local COURSE_OWNER with an instructor-confirmed explicit, shared, single-tablespace original temporary assignment, plus the exact original restore statement. Confirm the policy using provisioning records, not the dictionary name alone. An inherited CDB$DEFAULT assignment, a group, or a local-temporary policy requires its own approved restoration procedure. Stop this simple test if one applies.
• For the optional workload, CREATE SESSION, approved input objects, a fixed row and data-size limit, a finite duration, one isolated test session, agreed resource bounds, and an instructor stop procedure. No cross joins, forced large sorts, parameter changes, parallel pressure, or workload escalation are supplied here.

DDL changes commit independently of a later ROLLBACK. Restore the assignment through its recorded inverse statement and remove only the owned scratch tablespace after dependency checks.

Independent practice

1. Confirm the intended session. Connect as COURSE_DBA through the instructor's service using a password prompt or an approved wallet. Run:

   SHOW USER
   SHOW CON_NAME
   
   SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS database_name,
          SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
   FROM dual;
   
   SHOW PARAMETER db_create_file_dest

   Proceed only for the expected database, LABPDB, account, and approved effective destination. Creation in root would provision root storage.

2. Capture the baseline before any mutation. Record the complete results and the instructor-confirmed assignment policy:

   SELECT tablespace_name
   FROM dba_tablespaces
   WHERE tablespace_name = 'COURSE_TEMP54';
   
   SELECT group_name, tablespace_name
   FROM dba_tablespace_groups
   ORDER BY group_name, tablespace_name;
   
   SELECT property_name, property_value
   FROM database_properties
   WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
   
   SELECT username, common, default_tablespace,
          temporary_tablespace, local_temp_tablespace
   FROM dba_users
   WHERE username = 'COURSE_OWNER';

   The scratch name must also be absent as a GROUP_NAME. Preserve the entire default and group baseline. For the simple explicit-single assignment confirmed by the instructor, generate and save the exact inverse statement:

   SELECT 'ALTER USER COURSE_OWNER TEMPORARY TABLESPACE "' ||
          REPLACE(temporary_tablespace, '"', '""') || '";'
          AS restore_assignment_sql
   FROM dba_users
   WHERE username = 'COURSE_OWNER';

   Expect exactly one designated local-user row. Have the instructor confirm that this statement matches the recorded original policy. Save it before altering the user. Never substitute an assumed TEMP name.

3. Create and verify the bounded scratch storage. Execute the stated CREATE TEMPORARY TABLESPACE only in the authorized lab. Run both verification queries, inventory the actual files, and compare the physical-capacity report. Also confirm that COURSE_TEMP54 has no group membership and that the recorded default remains unchanged. Stop on a mismatch before assignment.
4. Optional bounded assignment test. If the instructor authorizes the test and the exact restore statement is saved, assign only COURSE_OWNER as shown above. Query DBA_USERS to verify that one user's assignment. Reconnect the isolated owner session to LABPDB and confirm its identity before the approved workload. Do not rely on a session opened before the assignment.

   The instructor samples temporary usage during the bounded operation and confirms that it maps to COURSE_TEMP54. A small operation may complete entirely in memory. Do not increase work merely to force a spill. In that case, record assignment verification separately and leave the spill observation pending for an instructor-designed test. Lesson 57B teaches the diagnostic interpretation.

5. Finish and restore. Allow the approved operation to finish, then disconnect all of its test sessions so session-lifetime temporary objects cannot remain. In the verified COURSE_DBA session, run the exact saved inverse statement. Requery the owner's permanent and temporary fields and compare them with the baseline. Verify that the container default and the complete group listing are unchanged. Reconnect a controlled owner session only if needed to verify normal use under the restored assignment, and disconnect it before the scratch drop.

Pass: explain workarea spill and tempfile purpose; verify actual placement, logical size, and bounded growth; document separate physical-capacity evidence; explain per-user, default, and group scope and the permanent-quota distinction; and, if the optional test was performed, preserve the default and group baseline and restore the exact original assignment. Mark unavailable or unobserved workload evidence pending.

Owned scratch cleanup

Perform cleanup only in the instructor-approved disposable lab, after restoration and disconnection. Immediately before dropping, confirm the current session and run:

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace = 'COURSE_TEMP54'
   OR temporary_tablespace = 'COURSE_TEMP54'
   OR local_temp_tablespace = 'COURSE_TEMP54';

SELECT group_name, tablespace_name
FROM dba_tablespace_groups
WHERE tablespace_name = 'COURSE_TEMP54'
   OR group_name = 'COURSE_TEMP54';

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

SELECT username, tablespace, segtype, blocks
FROM v$tempseg_usage
WHERE tablespace = 'COURSE_TEMP54';

There must be no user assignment, local assignment, group membership, or current temporary-segment usage depending on the scratch tablespace. The default must match its baseline and must not name COURSE_TEMP54. Preserve the exact tempfile inventory, confirm that all test sessions are disconnected, and prevent a new assignment or workload during this controlled cleanup window. A no-row usage sample alone does not prevent a later session from acquiring space.

If any dependency remains, stop and restore the approved state. Dropping a temporary tablespace can wait for active temporary segments to be released. Do not issue the drop as a way to stop workload. This exercise does not offline or shrink tempfiles.

Only after all conditions pass:

DROP TABLESPACE course_temp54 INCLUDING CONTENTS AND DATAFILES;

This removes the owned scratch tablespace and its associated files. OMF files are normally removed with the tablespace. The explicit clause states the cleanup intent. Tablespace drop is outside the recycle bin and cannot be reversed with ROLLBACK or an undrop command. The instructor's reset and recovery procedure is the fallback for an unintended change. See DROP TABLESPACE for the owned cleanup consequences.

Requery DBA_TABLESPACES and DBA_TEMP_FILES for COURSE_TEMP54 and expect no rows. Have the instructor verify removal of only the inventoried scratch files. Reconfirm the user's exact original assignment, the original default, and the complete original group listing. Never shrink, drop, or delete the normal TEMP files as exercise cleanup.

Recap

Provide bounded temporary storage for sort and hash work that spills from PGA work areas. In LABPDB, create temporary tablespace COURSE_TEMP54 with an unnamed tempfile of 64 MiB, an autoextend increment of 16 MiB, and a ceiling of 256 MiB. Verify the generated file, logical size, block-based increment, and a separate physical-capacity check before assignment. ALTER USER changes only COURSE_OWNER's temporary tablespace. A permanent quota does not govern TEMP, and this exercise leaves the container default and any tablespace group unchanged. After recovery, recheck the inventoried tempfiles, destination capacity, growth, and assignments. Cleanup restores the saved assignment and drops only the owned scratch tablespace after the dependency checks are empty.

Quiz

1. Will adding a permanent datafile to USERS resolve lack of TEMP extents?

Yes, every datafile supplies temporary extents. No, the temporary workload needs its assigned TEMP capacity and workarea demand checked. Yes, if the user has a permanent quota. Yes, if USERS is the user's default permanent tablespace.

2. What does DBA_TEMP_FILES.BYTES report?

The logical size of the tempfile. Guaranteed physical reservation for every future block. The number of rows currently being sorted. The user's permanent quota.

3. What is the scope of ALTER USER COURSE_OWNER TEMPORARY TABLESPACE COURSE_TEMP54?

It changes the container default for every user. It changes COURSE_OWNER's temporary assignment. It moves COURSE_OWNER's permanent tables. It creates a tablespace group.

4. Which statement about quotas and TEMP is correct?

A USERS quota reserves the same number of bytes in TEMP. The QUOTA clause caps temporary-table-space consumption. Permanent quotas govern permanent segments; temporary demand requires separate planning and control. Increasing a permanent quota automatically adds a tempfile.

5. What belongs in a recovery check for temporary storage?

Assume database opening proves every tempfile is usable. Restore transient sort contents from a permanent datafile backup. Check the inventoried tempfiles, destination capacity, growth settings and assignments after the applicable recreation workflow. Drop normal TEMP before every recovery.

The statements in this lesson are unexecuted teaching examples. No release update, storage report, or query result is claimed here. Run the practice in the 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