Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 56 Part 1 — Choose File Growth Limits Before Creating Storage

Plan a permanent tablespace around the application's expected growth and the physical destination that must supply its files. Choose the file model, initial allocation, extension increment and maximum; then budget other consumers of the same storage.

Oracle DBA Lesson 56 Part 1 — Choose File Growth Limits Before Creating Storage

Choose the file model

In Oracle Database 19c, a tablespace is a logical storage unit. Its permanent data files are physical files on disk. APP_DATA is the permanent tablespace in this plan, in the intended PDB.

ChoiceFile arrangementGrowth implication
SMALLFILESeveral data files are possibleExtend existing files or add another file within database and platform limits
BIGFILEOne data file onlyGrow that file; a second file cannot be added

Neither name determines the initial allocation. A bigfile is not automatically a huge allocation. Per-file limits depend on block size, database, and platform restrictions. Confirm them for the intended configuration. Oracle's documented approximate block ceilings are 2^22 blocks per smallfile and 2^32 blocks per bigfile. These are addressability limits, not usable physical capacity guarantees. Also consider database file-count limits and recovery and operational requirements before choosing the model.

This plan chooses explicit SMALLFILE. For ordinary permanent storage, retain locally managed extents and automatic segment space management:

  • Local extent bitmaps track extent allocation. An extent is a group of blocks assigned to an object's segment.
  • Automatic segment space management uses bitmaps to track available room within a segment. It is the default for new permanent, locally managed tablespaces. This does not apply uniformly to special cases such as SYSTEM, undo, and temporary storage.

These mechanisms organize space inside files. The shared destination still needs physical capacity planning.

See Managing Tablespaces and CREATE TABLESPACE.

Specify bounded file growth

64M, 16M, and 256M are binary megabytes: 1 MiB = 1,048,576 bytes. Compare reports in consistent units. Storage vendors may report decimal GB. File allocation includes file metadata and object allocation, so neither the file size nor the configured maximum equals table data size or available filesystem space. Thin provisioning, quotas, and backing storage can impose additional capacity limits.

This is a clause fragment for planning, not a standalone executable statement:

DATAFILE SIZE 64M
  AUTOEXTEND ON NEXT 16M MAXSIZE 256M
SettingMeaning in this plan
SIZE 64MInitial physical file allocation: 64 MiB
AUTOEXTEND ONAllows automatic file extension when needed and possible
NEXT 16MMinimum extension increment: 16 MiB
MAXSIZE 256MMaximum automatic size of this file: 256 MiB

One 16 MiB extension takes the file from 64 MiB to 80 MiB. Oracle may need larger growth to satisfy a request. NEXT is the minimum increment, not a promise that every request grows by exactly one increment. The video illustrates one such extension on a fixed 0–256 MiB file-size scale. It depicts the allocated boundary of the same file expanding, rather than data being copied to another file.

Possible additional file growth is 256 − 64 = 192 MiB. MAXSIZE does not reserve that future 192 MiB or the full 256 MiB in the shared destination. Further growth needs available storage, applicable limits, and sufficient capacity for the required increment. A user quota also does not reserve disk space.

See file specification and managing data files.

Budget the shared destination

Take a report that shows 160 MiB available after the initial 64 MiB allocation. Budget from that same point in time.

Future requirementMiB
This file's possible growth: 256 − 64192
Planned growth of other files and consumers on this destination64
Chosen operating headroom32
Total future capacity required288
Available after initial creation160
Shortfall: 288 − 160128

This plan cannot support every stated growth requirement with that available capacity. Add capacity or revise the growth commitment before creation. Choose headroom from growth history, peaks, maintenance operations, monitoring, and storage policy. Check every consumer that shares the destination. A different directory name does not establish an independent physical pool.

Two different boundaries can prevent extension when further file growth is needed:

  1. Configured limit: the file reaches 256 MiB. Further autoextension is stopped by MAXSIZE even if the destination has spare capacity.
  2. Physical limit: the file reaches only 80 MiB, while other consumers exhaust the destination. Growth can fail before MAXSIZE.

These cases concern requests that need file growth. Existing free blocks inside an allocated file may still serve allocations. Reaching MAXSIZE does not mean every insert fails.

Check dictionary settings

In the intended PDB, check an existing tablespace. These statements use REFERENCE_TS:

SHOW CON_NAME

SELECT tablespace_name, bigfile, block_size,
       extent_management AS extent_mgmt,
       segment_space_management AS space_mgmt
FROM dba_tablespaces
WHERE tablespace_name = 'REFERENCE_TS';
TABLESPACE_NAMEBIGFILEBLOCK_SIZEEXTENT_MGMTSPACE_MGMT
REFERENCE_TSNO8192LOCALAUTO

BIGFILE = NO indicates smallfile. BLOCK_SIZE is bytes per Oracle block for that tablespace. LOCAL and AUTO describe extent management and segment space management.

Check each physical file and translate its increment with the matching tablespace block size:

SELECT f.file_name,
       f.bytes, f.maxbytes, f.autoextensible,
       f.increment_by, t.block_size,
       f.bytes / 1048576 AS allocated_mib,
       f.maxbytes / 1048576 AS maximum_mib,
       f.increment_by * t.block_size / 1048576 AS increment_mib
FROM dba_data_files f
JOIN dba_tablespaces t
  ON t.tablespace_name = f.tablespace_name
WHERE f.tablespace_name = 'REFERENCE_TS'
ORDER BY f.file_name;

One REFERENCE_TS file focuses on these columns:

BYTESMAXBYTESAUTOEXTENSIBLEINCREMENT_BYBLOCK_SIZE
67108864268435456YES20488192

BYTES is the allocated file size. MAXBYTES is its maximum file size. INCREMENT_BY is measured in Oracle blocks, so 2048 × 8192 = 16,777,216 bytes = 16 MiB. Use the tablespace's block size, not an assumed database-wide value. AUTOEXTENSIBLE = YES enables automatic growth. A maximum alone does not enable it. For multiple files, read every row and budget their combined possible growth against the shared destination. Investigate missing or inaccessible file metadata and encrypted-keystore conditions rather than accepting an incomplete inventory.

See DBA_DATA_FILES and DBA_TABLESPACES.

Confirm where OMF will put the file

SHOW PARAMETER db_create_file_dest

For an unnamed data file, the effective DB_CREATE_FILE_DEST must be valid in the intended PDB. Oracle uses it to generate the physical filename and placement. A filesystem directory must already exist and permit Oracle to create files. A suitable ASM disk group can also be used. A parameter value by itself proves neither writable storage nor sufficient available capacity.

Obtain filesystem available capacity, or ASM usable file capacity with redundancy and required recovery space considered. In ASM, USABLE_FILE_MB and REQUIRED_MIRROR_FREE_MB provide relevant information for supported configurations. The raw FREE_MB figure alone may overstate room for new mirrored files. Oracle notes additional caveats for FLEX and EXTEND disk groups, so use the applicable storage procedure. Confirm backing capacity and concurrent consumers with the storage administrator. An explicit filename instead requires a separately verified, unique, writable path and a clear file-management choice.

See Oracle Managed Files, DB_CREATE_FILE_DEST, and V$ASM_DISKGROUP.

Recap

Choose the file model, initial allocation, extension increment, and maximum before creating permanent storage, and budget every other consumer of the same destination. SMALLFILE can add another data file within database and platform limits. BIGFILE has one file, so growth extends that file. APP_DATA uses explicit SMALLFILE, locally managed extents, and automatic segment space management. SIZE 64M, NEXT 16M, and MAXSIZE 256M are binary megabytes. NEXT is the minimum extension: one such step takes 64 MiB to 80 MiB. MAXSIZE limits automatic growth and does not reserve the remaining 192 MiB. After the initial allocation leaves 160 MiB free, 192 MiB of further growth, 64 MiB for other consumers, and 32 MiB of headroom total 288 MiB, a shortfall of 128 MiB. Add capacity or revise the commitment before creation. A file can also reach MAXSIZE while the destination still has space, or fail to extend at 80 MiB when the destination is full. Free blocks already inside the file can still be used. In the intended PDB, read DBA_TABLESPACES and DBA_DATA_FILES, convert INCREMENT_BY with that tablespace's block size, and confirm that DB_CREATE_FILE_DEST is a writable destination with enough usable capacity.

Quiz

1. Does MAXSIZE 256M reserve 256 MiB of disk at creation?

2. INCREMENT_BY is 2048 and BLOCK_SIZE is 8192. What is the increment?

3. Which tablespace model permits adding a second data file, within applicable limits?

4. An 80 MiB file has MAXSIZE 256M, but its shared destination is full. What can happen?

5. The post-initial-allocation report shows 160 MiB free. Future file growth is 192, other consumers need 64, and headroom is 32. What is the result?

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 ...