Enhance Skills Daily

October 04, 2026  ·   · 

Oracle DBA Lesson 57 Part 1 — Provide Temporary Space for a Workload

Temporary storage supports intermediate database work. In the dedicated-server example, a report sorts more data than its PGA work area can hold. Oracle writes intermediate sort data to temporary extents in a physical tempfile on disk. The sort continues using its work area and temporary storage. The report's persistent table remains in its permanent tablespace.

Oracle DBA Lesson 57 Part 1 — Provide Temporary Space for a Workload

Where intermediate work is stored

Sorts and hash joins can use temporary storage when their work exceeds their memory work areas. Temporary tables and temporary undo are other consumers. The private work area in this example belongs to the PGA, not the SGA. Configuration and the execution model matter. This lesson does not claim that every query uses TEMP, and it does not describe shared-server memory placement. See Tuning the Program Global Area.

Check the intended PDB and unused name

The statement creates an ordinary shared temporary tablespace, not a RAC local temporary tablespace. APP_TEMP stays nondefault and outside every group. Tablespaces and groups share a namespace, so the name has to be free both as a tablespace and as a group. Confirm that the current container is APP_PDB, that APP_TEMP is not already a tablespace or a group, and that this PDB has a suitable effective DB_CREATE_FILE_DEST:

SHOW CON_NAME

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'APP_TEMP';

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

SHOW PARAMETER db_create_file_dest

SHOW commands belong to SQL*Plus. The SELECT statements are SQL. Omitting a filename relies on Oracle Managed Files. A parameter value alone does not prove that the destination is writable or that it has capacity. Confirm the actual filesystem or ASM destination and the other consumers of that destination. Oracle may create generated subdirectories and identifiers, so do not guess the final path. See Using Oracle Managed Files.

Create bounded temporary storage

Create the temporary tablespace without an explicit filename:

CREATE TEMPORARY TABLESPACE app_temp
  TEMPFILE SIZE 64M
  AUTOEXTEND ON NEXT 16M MAXSIZE 256M;
ClauseMeaning
TEMPORARY TABLESPACEStorage for temporary work
TEMPFILE SIZE 64MCreates a tempfile with an initial logical size of 64 MiB
AUTOEXTEND ON NEXT 16MEnables extension with a minimum 16 MiB increment
MAXSIZE 256MCaps this file's automatic size at 256 MiB
No explicit filenameOracle generates the filename at the effective OMF destination

Here, 1 MiB is 1,048,576 bytes. Temporary tablespaces use locally managed uniform extents, and the statement leaves the extent size to Oracle. The maximum is a per-file bound. It does not reserve future physical storage, and it does not impose a per-user temporary quota. If more files are added later, assess their combined bounds and the shared destination capacity.

On some operating systems, physical space for tempfile blocks is allocated when those blocks are accessed. The displayed file size therefore does not establish physically reserved capacity. Keep enough usable disk space for workload use and growth.

CREATE TEMPORARY TABLESPACE performs an implicit commit. ROLLBACK cannot undo it. See CREATE TABLESPACE and Managing tablespaces.

Verify the file before assignment

Read the tempfile before you assign the tablespace:

SELECT file_id, file_name,
       bytes / 1048576 AS size_mib,
       autoextensible,
       maxbytes / 1048576 AS max_mib
FROM dba_temp_files
WHERE tablespace_name = 'APP_TEMP'
ORDER BY file_id;

The illustrated file row is:

FILE_NAMESIZE_MIBAUTOEXTENSIBLEMAX_MIB
…/tempfile/o1_mf_app_temp_….tmp64YES256

Ellipses abbreviate a display. They are not an executable filename. Record every complete FILE_NAME from the query result, and confirm its physical destination, initial size, and growth limit. BYTES and MAXBYTES describe the file. Neither measures remaining filesystem or ASM capacity, or active sort usage.

For the extension increment, use the matching tablespace block size:

SELECT f.file_id,
       f.increment_by * t.block_size / 1048576 AS next_mib
FROM dba_temp_files f
JOIN dba_tablespaces t
  ON t.tablespace_name = f.tablespace_name
WHERE f.tablespace_name = 'APP_TEMP';

INCREMENT_BY is in Oracle blocks. This example expects NEXT_MIB = 16. Investigate a missing or unexpected row before you assign the tablespace. See DBA_TEMP_FILES.

Inspect and change one user's temporary assignment

Read the current user assignment and the container temporary default. Creating APP_TEMP alone does not change either one:

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'APP_USER';

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

The illustrated row before the change is:

USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE
APP_USERUSERSTEMP

DEFAULT_TABLESPACE controls ordinary permanent object placement. TEMPORARY_TABLESPACE names a temporary tablespace or a group for that user's temporary work. See DBA_USERS.

After the file checks match, assign that one user:

ALTER USER app_user
  TEMPORARY TABLESPACE app_temp;

Read the user row again. The illustrated row after the change is:

USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE
APP_USERUSERSAPP_TEMP

The permanent default remains USERS. Reconnect the session before testing the new assignment. An operation that is already running does not migrate its temporary extents. ALTER USER performs an implicit commit, so ROLLBACK cannot undo the assignment.

The container default is a different change. For an ordinary shared temporary tablespace, Oracle documents that change as reassigning users who are currently assigned the old default, including users explicitly assigned to it. Inspect the affected users and groups before changing it. The ALTER USER statement does not change the container default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE app_temp;

See ALTER USER and ALTER DATABASE.

Groups, quotas and recovery

A temporary tablespace group provides several member tablespaces under one assignment. Oracle can use temporary space across its members. A group does not promise equal distribution, a specific selection order, or unlimited physical capacity. Each member's files still need capacity planning. Inspect membership with DBA_TABLESPACE_GROUPS. This lesson uses one bounded tablespace and creates no group. See DBA_TABLESPACE_GROUPS.

Permanent tablespace quotas govern permanent object allocation. Oracle does not assign user quotas to temporary tablespaces. Manage temporary space through file and destination capacity, suitable work areas, SQL plans, and workload and concurrency controls. More permanent datafile space does not supply missing temporary extents. See Creating user accounts.

Tempfiles are not backed up by RMAN, and their transient contents are not restored or recovered as persistent table data. Keep the tempfile inventory and verify temporary storage after recovery. Oracle documents missing recorded temporary storage being re-created when the database opens after whole-database restore and recovery, with the previous creation size and growth attributes. That re-creation is conditional. An existing file with an invalid header is not automatically re-created, and re-creation can fail while the database open continues. OMF re-creation uses the current DB_CREATE_FILE_DEST. Review the recovery procedure, the inventory, and the alert log. A successful open does not by itself prove usable temporary storage. See RESTORE and complete database recovery.

Recap

Temporary space holds intermediate work that does not fit in a memory work area. In the dedicated-server example, that work area is PGA memory, and spilled sort data goes to temporary extents in a tempfile. The persistent table stays in its permanent tablespace. Sorts, hash joins, temporary tables, and temporary undo are consumers. Not every query uses TEMP.

Create APP_TEMP in APP_PDB as an ordinary shared temporary tablespace: a 64 MiB tempfile, autoextend in 16 MiB increments, and a 256 MiB per-file maximum, with Oracle Managed Files naming the file. Confirm the container, the unused name, the generated file, and NEXT_MIB of 16 before the assignment. ALTER USER sets TEMPORARY_TABLESPACE to APP_TEMP and leaves DEFAULT_TABLESPACE and the container default unchanged. There is no per-user temporary quota. A tablespace group is a separate assignment. After recovery, check the tempfile inventory and usability. A successful open does not prove that temporary storage is usable.

Quiz

1. In the dedicated-server example, where is the sort work area, and where is spilled intermediate data?

2. Will adding a permanent datafile to USERS supply missing temporary extents?

3. What does MAXSIZE 256M establish for the example?

4. After ALTER USER APP_USER TEMPORARY TABLESPACE APP_TEMP, what should be checked?

5. What is true about tempfile recovery?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...