What is the purpose of temp tablespace?
In the world of Oracle Database, the Temporary (TEMP) Tablespace is often called the "Scratchpad" of the database. While your permanent tablespaces (like USERS or SYSTEM) are built for long-term storage, the TEMP tablespace is built for speed and transience.
It is the workspace Oracle uses when the memory (PGA) isn't large enough to handle a complex operation.
When you run a SQL query that requires sorting or joining data, Oracle first tries to do that work in the PGA (Program Global Area)—the private RAM assigned to your session.
However, RAM is expensive and limited. If you try to sort 50GB of data in a 2GB PGA, Oracle can't just give up. Instead, it "spills" the operation to the disk. The TEMP Tablespace is the specific area on the disk reserved for this "spillover" work.
The TEMP tablespace is triggered by several resource-heavy operations:
Sorting: Any query with an ORDER BY or GROUP BY clause that is too large for RAM.
Large Joins: Specifically Hash Joins where the database needs to build a "hash table" in memory but runs out of space.
Index Creation: Building an index requires sorting the entire table's worth of keys.
Distinct Values: Running SELECT DISTINCT requires a sort to identify and remove duplicates.
Global Temporary Tables: If you explicitly create a table with CREATE GLOBAL TEMPORARY TABLE, the data you insert into it is stored in TEMP, not in your data files.
There is a major architectural difference between your normal tablespace and your TEMP tablespace:
| Feature | Permanent Tablespace | TEMP Tablespace |
| File Type | Datafiles (.dbf) | Tempfiles (.tmp) |
| Persistence | Data stays after a reboot. | Data is wiped (re-initialized) on reboot. |
| Redo Logging | Changes are recorded in Redo Logs. | No Redo Logging (saves massive overhead). |
| Backup | Must be backed up. | No need to back up (they are empty at startup). |
If your TEMP tablespace is too small, you will encounter the error:ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
This means a user's query was so large (or poorly written) that it consumed all available scratchpad space. When this happens, the query fails immediately.
To keep your database running smoothly, follow these three "Golden Rules" for TEMP:
Use Locally Managed Tablespaces: Always use EXTENT MANAGEMENT LOCAL with UNIFORM SIZE for TEMP to avoid fragmentation.
Monitor "Spillage": Use the V$TEMPSEG_USAGE view to see which user is hogging all the space.
One TEMP is not always enough: In high-concurrency environments, you can create a Tablespace Group (a cluster of multiple TEMP tablespaces) to prevent different sessions from fighting over the same file.
Because Oracle knows the data in TEMP is temporary, it doesn't generate Redo Log entries for it. This makes writing to TEMP significantly faster than writing to a permanent table. This is why using Global Temporary Tables for intermediate processing in an ETL job is a brilliant performance move.