Permanent, Temporary, Undo Tablespaces and Bigfile vs Smallfile Tradeoffs

Learning outcomes

ServiceHub’s storage dashboard shows USERS, TEMP, and UNDO-related structures together and labels all of them “database files.” That hides three different jobs. Permanent storage keeps application and dictionary segments. Temporary storage holds transient work that does not belong in permanent segments. Undo stores before-images and transaction metadata used for rollback and read consistency. Choosing bigfile or smallfile changes file-management characteristics, not the semantic job of the tablespace.

1. Permanent tablespaces store persistent segments

A permanent tablespace stores durable segments such as tables, indexes, LOBs, materialized views, and dictionary structures. Its datafiles participate in database backup/recovery. ServiceHub’s owner objects live in a permanent tablespace such as USERS or the optional SERVICEHUB_DATA created in Chapter 01.

SELECT tablespace_name, contents, status, bigfile,       extent_management, segment_space_managementFROM   dba_tablespacesORDER  BY contents, tablespace_name;SELECT file_id, tablespace_name, file_name,       ROUND(bytes/1024/1024,1) AS size_mbFROM   dba_data_filesORDER  BY tablespace_name, file_id;

Permanent does not mean “never changes” and does not imply a recovery model by itself. Redo, archived redo, control files, RMAN backups, and recovery configuration are separate mechanisms covered later.

2. Temporary tablespaces are scratch space for database operations

A temporary tablespace is backed by tempfiles, not ordinary permanent datafiles. Oracle uses temporary segments for work that cannot remain entirely in memory, such as large sorts, hash operations, index builds, global temporary table activity, and other SQL execution work. The fact that a query spills to TEMP does not mean it created a permanent application table.

SELECT file_id, tablespace_name, file_name,       ROUND(bytes/1024/1024,1) AS size_mb,       autoextensible,       ROUND(maxbytes/1024/1024,1) AS max_mbFROM   dba_temp_filesORDER  BY tablespace_name, file_id;SELECT username, tablespace, segtype,       ROUND(blocks * p.value / 1024 / 1024,1) AS approx_mbFROM   v$tempseg_usage tCROSS JOIN (SELECT value FROM v$parameter WHERE name='db_block_size') pORDER  BY approx_mb DESC;

The second query is a snapshot. A sort may finish between samples. Also, temporary tablespaces can use different block sizes in specialized configurations, so the simple multiplication using DB_BLOCK_SIZE is a course-lab approximation rather than a universal accounting formula. For precise production monitoring use the relevant tempfile/tablespace metadata and current Oracle views for that configuration.

3. Undo is transactional history, not scratch space

An undo tablespace stores undo records generated by transactions. Undo lets Oracle roll back uncommitted changes, recover transactions, and reconstruct older block versions for consistent reads. This is why a reader can often see a statement-consistent image while another session is changing the same rows. Undo is not the same thing as redo: redo describes changes for durability/recovery; undo describes how to reverse or reconstruct changes.

SELECT name, valueFROM   v$parameterWHERE  name IN ('undo_management','undo_tablespace','undo_retention')ORDER  BY name;SELECT tablespace_name, contents, statusFROM   dba_tablespacesWHERE  contents = 'UNDO';SELECT begin_time, end_time, undoblks, txncount,       maxquerylen, ssolderrcntFROM   v$undostatORDER  BY begin_time DESCFETCH FIRST 12 ROWS ONLY;

V$UNDOSTAT summarizes recent undo activity. SSOLDERRCNT can help identify ORA-01555 history, but tuning undo from one sample is unsafe. Chapter 07 connects undo retention, long-running reads, transaction rates, and ORA-01555 mechanisms in depth.

4. Bigfile versus smallfile is about file topology

A bigfile tablespace contains one potentially very large datafile or tempfile. A traditional smallfile tablespace can contain multiple files. Bigfile tablespaces reduce file-count administration and can scale to very large capacities, but they assume an underlying storage design that can stripe and manage very large files appropriately. Oracle explicitly recommends using bigfile tablespaces with ASM or other capable logical volume/storage managers rather than treating a single huge file on an unstriped device as automatically superior.

5. Observe the actual default instead of trusting the version label

SELECT property_name, property_valueFROM   database_propertiesWHERE  property_name = 'DEFAULT_TBS_TYPE';SELECT tablespace_name, contents, bigfile,       block_size, extent_management, segment_space_managementFROM   dba_tablespacesORDER  BY tablespace_name;

A freshly created 26ai database using current DBCA templates is expected to show the new bigfile default. A migrated/upgraded database can legitimately show smallfile tablespaces. This distinction matters in runbooks: ALTER TABLESPACE ... ADD DATAFILE is not valid for a bigfile tablespace because there can be only one file.

6. Deliberately wrong approach: “bigfile is faster, so convert everything”

That recommendation ignores workload, backup/recovery workflow, storage striping, file-size/platform limits, migration risk, and existing topology. It also confuses manageability with throughput. A single file does not create more I/O bandwidth by itself.

A safer decision asks: how many files exist, how often file-count administration causes incidents, what storage layer provides striping/redundancy, how will RMAN parallelize backup/restore, what role transitions or open times matter, what is the recovery-time objective, and what migration method/rollback exists? Only then should file topology change.

7. Hands-on lab: build a three-purpose storage evidence card

Without changing the database, capture a repeatable report for permanent, temporary, and undo storage. Run it at low activity and again while a controlled query performs a larger sort if your Free lab has sufficient headroom.

SELECT tablespace_name, contents, bigfile,       extent_management, segment_space_managementFROM   dba_tablespacesORDER  BY contents, tablespace_name;SELECT tablespace_name, COUNT(*) AS datafiles,       ROUND(SUM(bytes)/1024/1024,1) AS datafile_mbFROM   dba_data_filesGROUP  BY tablespace_nameORDER  BY tablespace_name;SELECT tablespace_name, COUNT(*) AS tempfiles,       ROUND(SUM(bytes)/1024/1024,1) AS tempfile_mbFROM   dba_temp_filesGROUP  BY tablespace_nameORDER  BY tablespace_name;SELECT begin_time, undoblks, txncount, maxquerylen, ssolderrcntFROM   v$undostatORDER  BY begin_time DESCFETCH FIRST 6 ROWS ONLY;

Verification: identify one permanent tablespace that stores ServiceHub objects, the default TEMP tablespace and its tempfile(s), and the configured undo tablespace. Record whether each is bigfile. No resize or conversion is required.

8. Production judgment

Separate monitoring and capacity rules by purpose. Permanent space alarms should correlate segment growth, free extents, autoextend headroom, and lower-storage capacity. TEMP alarms should correlate active SQL work areas and peak concurrency rather than permanently “used” segments. Undo capacity must correlate transaction generation, retention needs, longest queries, and recovery/flashback requirements. Combining all three into one percentage hides the mechanism of failure.

Bigfile adoption should be evaluated with backup/restore throughput, file-count overhead, storage striping, platform limits, Data Guard role-transition/open behavior, and operational tooling. Current Oracle MAA guidance favors bigfile for large new designs, but that recommendation still assumes an appropriate lower storage layer and tested recovery workflow.

9. Summary and next step

Permanent, temporary, and undo tablespaces have different jobs and failure modes. Bigfile versus smallfile changes file topology, not those semantics and not performance by decree. Lesson 3 now asks a more subtle question: after objects grow and rows are deleted, why can space remain allocated, where is the high-water mark, and what operations actually reclaim space?

Authoritative references

• Oracle AI Database Administrator’s Guide — Managing Tablespaces — permanent, temporary, locally managed, bigfile, and smallfile administration
• Oracle AI Database Concepts — Undo Data — undo and consistent-read concepts
• Oracle AI Database Administrator’s Guide — Managing Undo — automatic undo management and monitoring
• Oracle AI Database Reference — V$UNDOSTAT — undo workload statistics
• Oracle MAA — Database Configuration Best Practices — current bigfile operational guidance