Oracle RAC Temporary Tablespaces and Instance Management: What Actually Matters in Practice

There’s a pattern I’ve noticed with Oracle RAC documentation: it covers the architecture in depth, spends considerable time on the networking and storage stack, and then handles temporary tablespace management in about two paragraphs before moving on. For day-to-day DBA work, that’s backwards. Temporary tablespace issues — contention, exhaustion, poorly sized allocations — show up in production incidents far more often than the exotic cluster problems that get most of the documentation attention.

This is a practical guide to local temporary tablespaces in RAC environments and the instance management commands you actually need. Less theory, more of the operational reality.

---

Why Local Temporary Tablespaces Exist

Before local temporary tablespaces, RAC environments used shared temporary tablespaces sitting on shared storage — ASM or otherwise. Every node competing for temporary space was competing for the same I/O path. For sort-heavy or hash join-heavy workloads, that cross-node I/O pressure added up quickly. You’d see it in wait events, in degraded query performance during peak periods, and in the kind of contention that’s frustrating to diagnose because it doesn’t show up as a single obvious bottleneck.

Local temporary tablespaces address this by giving each RAC node its own temporary storage on node-local disks. The node handles its own sort operations, its own hash operations, its own spillover from memory-intensive queries — without touching shared storage at all. The performance improvement in I/O-sensitive workloads can be substantial, and it also reduces the metadata coordination overhead that comes with shared temporary space in a cluster.

The tradeoff is that local temporary tablespaces are strictly per-instance. A session on Node 1 uses Node 1’s local temp space. There’s no cross-node sharing, no automatic failover to another node’s temp space if yours fills up. You size it correctly and monitor it, or you deal with the consequences.

---

Creating Local Temporary Tablespaces

The FOR ALL clause is what makes this work cleanly in a RAC environment — it creates the tablespace across all instances in a single command rather than requiring you to connect to each node separately:

CREATE LOCAL TEMPORARY TABLESPACE temp_local_all
FOR ALL
TEMPFILE '/localdisk/temp01.dbf' SIZE 10G;

Each node creates its own copy of the tempfile at the path you specify. Since these are on local disks, the path needs to exist and have adequate space on every node. If you’re doing this for the first time, verifying the disk layout across all nodes before running the command is worth the two minutes it takes.

One thing worth knowing if you’re on Oracle 23ai or later: Bigfile Tablespace is now the default. For most temporary tablespace use cases that’s fine, but if you specifically need Smallfile, you have to say so explicitly:

CREATE SMALLFILE TEMPORARY TABLESPACE temp_local;

The change is easy to miss if you’re not expecting it, and the behavior difference matters if you have tooling or scripts that assume a particular format.

---

How Oracle Decides Which Temp Space to Use

This is where the behavior gets specific enough that it’s worth understanding clearly, because the failure mode when you get it wrong is abrupt.

For read/write instances, Oracle’s allocation order is:

1. The user’s assigned shared temporary tablespace
2. The user’s assigned local temporary tablespace
3. The default shared temporary tablespace

Here’s the critical part: once Oracle starts allocating from one tablespace, it does not switch to another if that tablespace runs out of space. If your local temporary tablespace fills up mid-query, Oracle throws an error. It doesn’t transparently spill over to shared temp. The query fails.

This is a different failure behavior than what people sometimes expect coming from other database systems, and it means your sizing and monitoring approach needs to account for it. Monitoring free space in local temp tablespaces isn’t optional housekeeping — it’s how you avoid unexpected query failures in production.

---

Assigning Temp Tablespaces to Users

For high-volume workloads where specific users or application accounts drive the majority of sort and hash operations, assigning a dedicated local temporary tablespace is a clean way to isolate resource consumption:

ALTER USER username LOCAL TEMPORARY TABLESPACE temp_local;

This is particularly useful for ETL accounts, reporting users, and batch processing accounts where you can predict the workload pattern and want to prevent one heavy user from starving out everyone else on the node. It’s also easier to monitor — if you know that etl_user is mapped to temp_local_etl, you can track that tablespace specifically rather than trying to attribute temp space usage from a shared pool.

---

The Administrative Commands You’ll Actually Use

Resize:

ALTER TABLESPACE temp_local RESIZE 20G;

Enable autoextend — useful as a safety net, but size it properly first rather than relying on autoextend as a substitute for planning:

ALTER DATABASE TEMPFILE '/localdisk/temp01.dbf'
AUTOEXTEND ON NEXT 1G;

Autoextend on local disks has a ceiling: whatever free space exists on that local disk. Unlike ASM diskgroups where you might have significant headroom, local disks can fill up faster than you’d expect during a runaway query or an unexpected batch spike. Enable autoextend, but also set a MAXSIZE and monitor the local disk utilization, not just the tablespace free space.

Shrink after heavy usage:

Temporary tablespace files don’t automatically release space after large operations complete. If you’ve had a significant batch run and want to reclaim the disk space, shrinking the tempfile is the right move. It also resets the high-water mark, which matters if you’re reporting on disk utilization.

---

Monitoring: The Views You Want

Three views do most of the work:

SELECT tablespace_name, shared, inst_id
FROM dba_temp_files;

The SHARED column tells you whether each tempfile is LOCAL or SHARED, and INST_ID tells you which node owns it. This is your quick sanity check that local temp tablespaces are configured the way you think they are across all nodes.

For space utilization:

SELECT tablespace_name, bytes_used, bytes_free
FROM dba_temp_free_space;

This gives you current utilization per tablespace. In a RAC environment you’ll want to check this per instance during peak periods to build a baseline for how much headroom you actually need. Don’t size temporary tablespaces based on what looks reasonable on paper — size them based on what your actual worst-case workloads consume, with margin.

DBA_TABLESPACES and DBA_TEMP_FILES round out the picture for configuration details. If you’re building monitoring scripts, combining current utilization from DBA_TEMP_FREE_SPACE with autoextend settings and disk-level free space gives you the complete picture.

---

Starting and Stopping RAC Instances

srvctl is the right tool for cluster-wide RAC operations. Managing instances through SQL*Plus works but bypasses the cluster resource management layer, which means Oracle Clusterware doesn’t know what you did and can work against you — restarting a node you intentionally stopped, for example.

Start and stop the entire database:

srvctl start database -d orcl
srvctl stop database -d orcl

Start or stop specific instances:

srvctl start instance -d orcl -i orcl1,orcl2
srvctl stop instance -d orcl -i orcl1 -o immediate

The -o option takes the same shutdown modes you’re used to from SQL*Plus: normal, transactional, immediate, and abort. For planned maintenance, immediate is generally the right choice. transactional is cleaner but can take longer if you have long-running transactions. abort is for emergencies.

Startup modes via srvctl follow the same pattern as SQL*Plus — open, mount, nomount — useful when you’re doing recovery or configuration work that requires the instance at a specific state.

The -eval flag:

srvctl stop database -d orcl -eval

This simulates the command and shows you what would happen without actually doing it. I use this before any cluster-wide operation I don’t run daily. It takes ten seconds and can surface dependency or resource issues before they become actual problems during a maintenance window.

---

When SQL*Plus Makes Sense for RAC Instance Management

The short answer: when you’re working on a specific node locally and don’t need cluster-aware behavior.

export ORACLE_SID=orcl1
sqlplus / as sysdba

Then STARTUP or SHUTDOWN IMMEDIATE as you normally would.

This is appropriate for local diagnostic work, recovery scenarios where you’re bringing up a specific instance deliberately outside of cluster control, and situations where srvctl itself isn’t available (which happens during certain failure scenarios). For routine administration in a healthy cluster, prefer srvctl.

---

A Few Things Worth Getting Right From the Start

Local temporary tablespaces add real performance value for the right workloads, but they require more deliberate management than shared temporary tablespaces because the failure modes are less forgiving. If shared temp runs out, the problem might be distributed. If local temp on Node 3 runs out, every session on Node 3 hitting temp-intensive operations gets errors, and Node 1 and Node 2 won’t help.

Size based on observed workload, not estimates. Monitor free space per instance, not just aggregate. Know which users are assigned to which temp tablespaces so you can quickly trace utilization spikes. Enable autoextend with a sensible MAXSIZE as a safety net, not as a primary capacity strategy.

And use srvctl for cluster operations — consistently, not just sometimes. The five extra seconds it takes compared to SQL*Plus is not the place to optimize.