Oracle DBA Lesson 58A — Choose How to Grow a Datafile

When a permanent segment needs its next extent, Oracle needs allocatable space in that tablespace. A DBA can increase an existing file's size, revise its automatic growth settings, or add a supported datafile. Choose the action from the file inventory and storage evidence. By the end of this lesson, you should be able to explain one bounded choice, predict its effect, and verify the resulting files.

Oracle DBA Lesson 58A — Choose How to Grow a Datafile

Establish the kind of space that is missing

Read the complete error and identify the affected permanent tablespace and segment. An extent needs contiguous Oracle blocks within one file. Total free space can consist of several smaller extents. A large total alone does not ensure that the next requested extent fits. Permanent quota errors need a quota check. TEMP extent errors use the workload's temporary storage and its own investigation.

The growth choices here concern an online permanent tablespace. The example uses a separately provisioned, disposable COURSE_GROW56 in LABPDB. Check its current file size, automatic-growth policy, actual storage capacity, and file model.

Evidence	What it answers
DBA_DATA_FILES.FILE_ID, FILE_NAME	Which exact registered file will change?
BYTES, USER_BYTES	Current file size. The usable user-data portion is smaller because of file metadata.
AUTOEXTENSIBLE, MAXBYTES	Is automatic growth enabled, and what is its configured ceiling?
INCREMENT_BY × DBA_TABLESPACES.BLOCK_SIZE	Configured extension increment in bytes.
DBA_FREE_SPACE	What free extents are available within the current files?
DBA_TABLESPACES.BIGFILE	NO means smallfile. YES means bigfile.
Storage measurement and existing growth commitments	Can the destination support the chosen allocation and future growth?

NEXT is the minimum automatic-extension increment. An actual extension can service a larger demand. MAXSIZE limits automatic growth. Changing that ceiling requires a capacity plan. File format and block size, platform, file-count limits, PDB storage policy, and storage capacity can impose further constraints. See managing data files and temp files for file-size changes, and the file-specification clauses for these operations and settings.

Read the existing inventory in the correct container

Use SQL*Plus with the instructor's local COURSE_DBA lab identity. The service alias must resolve to the designated LABPDB. The session prompts for its locally provisioned password unless an approved wallet is used.

sqlplus -L course_dba@LABPDB

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user
FROM dual;

SELECT file_id, file_name, bytes, autoextensible, maxbytes
FROM dba_data_files
WHERE tablespace_name = 'COURSE_GROW56'
ORDER BY file_id;

SELECT tablespace_name, contents, status, bigfile, block_size,
       extent_management, segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_GROW56';

SHOW is a SQL*Plus command. The queries inspect the current container. Confirm LABPDB, the recorded database identity, and the authorized account. The scratch baseline requires one 64 MiB OMF datafile, a permanent online smallfile tablespace, local extent management, and automatic segment space management. Record the actual absolute FILE_ID and exact FILE_NAME together. A relative file number is a different identifier. If the rows disagree with the instructor's manifest, stop before choosing a file.

Read free extent sizes beside each online file:

SELECT d.file_id,
       d.bytes / 1048576 AS file_mib,
       NVL(f.free_bytes, 0) / 1048576 AS free_extent_mib,
       NVL(f.largest_extent, 0) / 1048576 AS largest_free_extent_mib,
       d.autoextensible,
       d.maxbytes / 1048576 AS auto_ceiling_mib,
       d.increment_by * t.block_size / 1048576 AS next_mib
FROM dba_data_files d
JOIN dba_tablespaces t ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT file_id, SUM(bytes) AS free_bytes, MAX(bytes) AS largest_extent
  FROM dba_free_space
  WHERE tablespace_name = 'COURSE_GROW56'
  GROUP BY file_id
) f ON f.file_id = d.file_id
WHERE d.tablespace_name = 'COURSE_GROW56'
ORDER BY d.file_id;

The outer join retains files having no free-extent row. This interpretation assumes the verified online lab files and complete dictionary visibility. Offline locally managed files can have incomplete extent reporting. Two separate free 8 MiB extents total 16 MiB but cannot themselves supply one contiguous 12 MiB extent. Review the actual request and allocation policy before treating growth as the correction. Destination free space comes from separately verified host or storage evidence. It is outside the DBA_FREE_SPACE total. See DBA_DATA_FILES, DBA_TABLESPACES, and DBA_FREE_SPACE for the fields and visibility conditions.

Choose one action

These are alternative plans, each starting from its own verified baseline. Do not execute all three. Every placeholder below is a commented substitution template: replace it with the exact approved value only after matching the live inventory. No example file ID or actual host path is supplied.

Allocate more bytes to the identified file now

For a 64 MiB file, an absolute target of 96 MiB increases current file allocation by 32 MiB:

-- Substitute the exact absolute FILE_ID from the verified manifest:
-- ALTER DATABASE DATAFILE <verified_file_id> RESIZE 96M;

96M is the resulting total file size. Predict BYTES = 100663296 after a successful resize. Requery FILE_ID, FILE_NAME, BYTES, USER_BYTES, AUTOEXTENSIBLE, MAXBYTES, and INCREMENT_BY, and recheck available extents. The automatic-growth settings require their own interpretation after a manual size change. This example establishes immediate allocation. Future automatic growth needs a separately accepted budget and bound.

Consider two independent capacity scenarios. MiB means 1,048,576 bytes. The arithmetic assumes no additional competing commitment beyond the stated margin. Real neighboring growth must be included before proceeding.

Scenario	Free destination capacity	Operating margin	Additional file allocation	Decision
A: bounded immediate growth	64 MiB	16 MiB	96 − 64 = 32 MiB	The predicted remaining free space is 32 MiB, leaving the stated margin. Proceed only after all other bounds and extent needs are accepted.
B: destination shortfall	16 MiB	16 MiB	32 MiB	Capacity available above the margin is zero. Obtain an approved capacity correction before growth.

An ADD operation on the same full storage pool also needs physical capacity. Raising MAXSIZE cannot supply the missing storage in scenario B. The DBA must resolve that capacity condition before authorizing any growth there. See ALTER DATABASE for the absolute-size operation and the insufficient-space error.

Permit bounded growth when demand arrives

From an independent baseline at 64 MiB, this policy allows automatic growth up to 128 MiB, with a minimum increment of 16 MiB:

-- Substitute the exact absolute FILE_ID from the verified manifest:
-- ALTER DATABASE DATAFILE <verified_file_id>
--   AUTOEXTEND ON NEXT 16M MAXSIZE 128M;

The remaining potential allocation is 128 − 64 = 64 MiB. A separate feasible capacity plan could have 96 MiB destination free, 16 MiB committed to neighboring growth, and 16 MiB operating margin: 96 − 16 − 16 = 64 MiB. These are hypothetical inputs that must be measured and accepted in the lab. They are independent of the preceding resize example's 64 MiB free destination.

Verify AUTOEXTENSIBLE = YES, MAXBYTES = 134217728, and the product of INCREMENT_BY and the actual tablespace BLOCK_SIZE as 16777216 bytes. At an 8192-byte block size, 2048 blocks give 16 MiB. Accept an unchanged current BYTES when no new allocation has demanded extension. Altering the policy alone does not allocate its entire ceiling immediately.

Add a supported smallfile datafile

If the approved smallfile design calls for another file, and file-count, block and platform, PDB, and physical limits support it, add one bounded OMF file:

ALTER TABLESPACE course_grow56 ADD DATAFILE
  SIZE 32M AUTOEXTEND ON NEXT 16M MAXSIZE 128M;

This creates a new file with initial BYTES = 33554432, rather than enlarging the old file. Oracle generates its unique name in the effective, approved OMF destination. Record the newly returned FILE_ID and FILE_NAME. Compare the before and after inventory to identify exactly the added file and verify its growth settings. If the original file stays 64 MiB, current total file allocation becomes 96 MiB. User-data capacity still excludes file metadata.

The new file's full ceiling needs 128 MiB of potential allocation from the pre-add destination budget: 32 MiB initial plus 96 MiB potential later growth. A separate feasible plan is 160 MiB destination free, minus 16 MiB neighboring commitments and 16 MiB operating margin, leaving 128 MiB. Check the existing files' remaining growth commitments too. This budget is a separate scenario. It is not established by the resize example's free-space figure.

ADD DATAFILE is disallowed for bigfile tablespaces, which have one datafile. For an authorized bigfile target, grow that sole file through its supported resize or bounded autoextension operation. The tablespace-level form below is a concept template. This lesson's smallfile practice does not execute it:

-- For a separately verified BIGFILE tablespace only:
-- ALTER TABLESPACE <verified_bigfile_tablespace> RESIZE 96M;

See ALTER TABLESPACE for this ADD and RESIZE model restriction, using Oracle Managed Files for placement, and DB_CREATE_FILE_DEST for the effective destination and its existing-directory and write requirements.

Understand the downward-size limit before promising a reset

A smaller file end must leave allocated blocks intact. A high allocated extent near the end of the file can block a reduction, even when the file contains a large amount of free space elsewhere. Total free bytes, an extent that fits a request, and an unused file tail are separate measurements.

A segment's high water mark describes block usage within that segment. A datafile can contain extents belonging to many segments. One table's HWM therefore cannot determine the permitted smaller size of the whole file. Deleting table rows usually retains its allocated extents.

Inspect the extent ranges in the already recorded online file:

SELECT file_id,
       MAX(block_id + blocks - 1) AS highest_allocated_extent_block
FROM dba_extents
WHERE tablespace_name = 'COURSE_GROW56'
GROUP BY file_id
ORDER BY file_id;

This reports a highest allocated extent block from visible segment extents. It is diagnostic evidence, not a complete legal minimum-resize calculator. File headers and bitmap blocks, visibility, minimum size, and Oracle's own validation also matter. No downward resize is part of this exercise. If new extents occupy the grown tail, returning to 64 MiB can fail. Use the agreed disposable reset or tested recovery route instead of repeated shrink attempts. See logical storage structures for extent and high-water-mark concepts, and DBA_EXTENTS for this interpretation.

Practice, conditions, and pass criteria

First solve two fresh capacity cases on paper. Change the current file size, proposed target, free destination capacity, and stated neighboring commitments. For each, name the limiting evidence, calculate the additional allocation, and choose one action or a capacity stop. Explain whether the file model permits another datafile. A read-only inventory and prediction exercise requires no cleanup.

Optional execution uses only the instructor-owned disposable COURSE_GROW56. Prepare the following before any change:

1. Record the actual 19c release update, edition and platform, database identity, and LABPDB state. Confirm the target is an online permanent smallfile tablespace and record its exact original file count, absolute IDs, paths, bytes, ON or OFF policy, increment, and ceiling. Recheck immediately before the selected statement. If encrypted, the authorized instructor must establish the needed keystore availability.
2. The administrator needs ALTER DATABASE in the connected LABPDB for the file-ID operations. The grant can be local or common as applicable. ADD requires ALTER TABLESPACE. Cleanup requires DROP TABLESPACE. The named account alone supplies no privileges. The instructor provisions narrow dictionary access to the views used here and to version, state, and storage evidence. Ordinary growth does not require assuming SYSDBA. The PDB database_file_clauses delegate to ALTER DATABASE semantics under the target-PDB settings privileges. See ALTER PLUGGABLE DATABASE for PDB file settings and privileges.
3. Verify the effective OMF destination in this session (SHOW PARAMETER db_create_file_dest with authorized access). The directory must already exist and be writable to Oracle. Obtain current physical-storage evidence, account for other files' growth and the operating margin, and confirm file, block, and platform limits, the file count, and PDB storage bounds. Keep this parameter unchanged.
4. Capture ownership, database defaults, all user-default dependencies, and any quota rows. The disposable baseline must have no shared application objects, no database or user default depending on COURSE_GROW56, and no pre-existing quota dependencies. Main growth practice creates no table and changes no quota or account default. Unexpected rows require instructor resolution before work.
5. Have the instructor's tested Oracle restore or reset runbook and recorded recovery target available outside the changed storage. A VM snapshot alone is not proof of an Oracle restore rehearsal. Know the exact reset destination and service, and the acceptance checks for the recreated or recovered lab. Reserve enough capacity for the chosen action and the approved recovery plan.

The baseline and dependency checks are read-only:

SELECT property_name, property_value
FROM database_properties
WHERE property_name IN
  ('DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TEMP_TABLESPACE');

SELECT username, default_tablespace, temporary_tablespace,
       local_temp_tablespace
FROM dba_users
WHERE default_tablespace = 'COURSE_GROW56'
   OR temporary_tablespace = 'COURSE_GROW56'
   OR local_temp_tablespace = 'COURSE_GROW56'
ORDER BY username;

SELECT username, tablespace_name, bytes, max_bytes, dropped
FROM dba_ts_quotas
WHERE tablespace_name = 'COURSE_GROW56'
ORDER BY username;

SELECT owner, segment_name, segment_type, bytes
FROM dba_segments
WHERE tablespace_name = 'COURSE_GROW56'
ORDER BY owner, segment_name;

Capture the instructor's full original user-default and temporary assignments too, so the final comparison proves these were unchanged. Empty segment inventory is the simple growth-only baseline. The instructor must separately review any deferred objects or cross-tablespace dependencies. A zero segment count alone does not prove that all metadata dependencies are absent. See DBA_USERS for user defaults, DBA_TS_QUOTAS for quota inventory, and DATABASE_PROPERTIES for the database default tablespaces.

Execute only one authorized bounded choice. Do not deliberately exhaust the disk or run data-generation loops. Requery the target inventory and extents, verify the predicted size and settings or exactly one added file, and compare the other lab files, defaults, and quotas with the captured baseline. Independently verify remaining physical capacity. A command succeeding establishes neither a future capacity guarantee nor an observed workload improvement.

Pass when you can explain the decision and its limits, the resulting file inventory matches the prediction, destination capacity meets the accepted plan, and unrelated files, defaults, and quotas are unchanged. Preserve actual outputs and unexpected errors in local lab evidence. An unavailable grant, storage, or reset condition leaves execution pending. Complete the read-only decision exercise.

Restore or remove the owned scratch asset

File-growth DDL has durable effects. ROLLBACK does not undo it. Restoring the original autoextension policy requires the recorded ON or OFF state, increment, and ceiling. OFF clears the increment and maximum settings. If an original bound conflicts with the file's new size or tail allocations, use the tested reset rather than forcing settings or a smaller size. A newly added file may contain extents and has its own removal restrictions. This exercise resets the disposable tablespace as a whole instead of guessing which file is empty.

For removal, finish and disconnect the controlled lab workload. The instructor must confirm recovery readiness, exact scratch ownership, absence of default, quota, object, and external constraint dependencies, and the recorded catalog file manifest. Remove only manifest-owned test objects if an additional exercise created them. Stop if ownership or a dependency is unexpected. Never add CASCADE CONSTRAINTS to make an unknown dependency disappear.

After those conditions are met, use the designated cleanup administrator in the same verified LABPDB:

DROP TABLESPACE course_grow56 DROP QUOTA INCLUDING CONTENTS AND DATAFILES;

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_GROW56';

SELECT file_id, file_name
FROM dba_data_files
WHERE tablespace_name = 'COURSE_GROW56';

SELECT username, tablespace_name, dropped
FROM dba_ts_quotas
WHERE tablespace_name = 'COURSE_GROW56';

Deletion removes the owned tablespace contents and registered physical datafiles. OMF files are automatically removed. DROP QUOTA removes scratch quota metadata. Verify the expected absence of these rows, then have the authorized storage administrator confirm physical absence at exactly the recorded paths. If a file remains, preserve the evidence and inspect the alert log and cleanup errors with the instructor before resolving its ownership through the approved runbook. Recompare all original defaults, quotas, and the unrelated file inventory. Do not delete directories or files manually. The tablespace has no recycle-bin undrop. If the instructor recreates the empty scratch baseline, capture its newly assigned IDs and names and validate 64 MiB and the settings again. An exact recovery of the prior state uses the tested recovery runbook. See DROP TABLESPACE for deletion and dependency restrictions, and COMMIT for why an implicit DDL commit makes transaction rollback insufficient.

Recap

Choose one bounded action from the verified file inventory and storage evidence: resize the identified file to an absolute size, set a bounded AUTOEXTEND policy, or add one supported smallfile datafile. Do not run all three. Predict BYTES, MAXBYTES, and the increment, and confirm the destination can hold the additional allocation together with the operating margin and neighboring commitments. A bigfile tablespace has one datafile, so ADD DATAFILE does not apply. A downward resize can fail when an allocated extent lies beyond the new end. File-growth DDL is durable, and ROLLBACK does not undo it. After the approved cleanup, confirm that COURSE_GROW56, its files, and its quotas are absent, and that unrelated defaults are unchanged.

Quiz

1. What if AUTOEXTEND is on but MAXSIZE has been reached?

The file automatically exceeds the configured maximum. Oracle automatically adds another datafile. Verify physical headroom and file and tablespace limits, then deliberately raise the bound or add a supported file. Increasing a permanent user quota creates destination space.

2. A file is 64 MiB. What does RESIZE 96M request?

Add 96 MiB to the current size. Set total file size to 96 MiB, adding 32 MiB. Change only its automatic-growth ceiling. Create a second 96 MiB file.

3. Which file model permits ADD DATAFILE when all other conditions are met?

Smallfile. Bigfile. Both always. Neither.

4. A file has a large free-space total, but an allocated extent lies beyond the proposed smaller end. What should the DBA expect?

Oracle automatically relocates that extent. The free-space sum guarantees a successful resize. One table's low HWM guarantees successful resizing of the entire file. The downward resize can fail because allocated data lies beyond its target.

5. A new smallfile datafile starts at 32 MiB with MAXSIZE 128 MiB. How much potential file allocation should its separate destination budget cover from before ADD?

16 MiB. 32 MiB only. 128 MiB for this file, plus other commitments and operating margin. 96 MiB including the initial allocation.

These commands, predicted values, and scenarios were prepared from Oracle Database 19c documentation. They were not executed against a database, and no shown capacity figure is a measurement. Use the actual release update, privileges, file identity, storage evidence, and validated reset conditions in your authorized lab.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro