Oracle DBA Lesson 56A — Plan Tablespace File Growth

Before creating permanent storage, choose a file model, define bounded growth, and compare the planned demand with capacity at the actual destination. This lesson prepares that decision; the following lesson implements and verifies the approved plan.

Oracle DBA Lesson 56A — Plan Tablespace File Growth

Three capacity numbers

Keep these measurements separate:

Measurement	Meaning	Useful evidence
Current file size	The bytes currently allocated to the datafile, including its file metadata	DBA_DATA_FILES.BYTES
Configured automatic-growth ceiling	The largest size allowed through this file's configured autoextension	AUTOEXTENSIBLE and MAXBYTES
Available destination capacity	Storage that the destination can actually supply, after accounting for competing demand and the required operating margin	A current, destination-specific filesystem or ASM capacity report

USER_BYTES reports the portion of the file available for user data. The difference between BYTES and USER_BYTES accounts for file metadata. These file measurements do not measure stored row payload or filesystem free space.

An autoextensible file can encounter either its configured ceiling or insufficient destination capacity. Planning must address both conditions. Files sharing a filesystem or ASM disk group draw on a common pool; evaluate their combined growth demand. See DBA_DATA_FILES and DBA_TABLESPACES for file size, autoextension, maximum size, block count, block size, and management fields.

Choose smallfile or bigfile

A smallfile tablespace can contain multiple datafiles. Oracle 19c documents up to 1,022 files, with each file supporting approximately 2^22 blocks. Database, operating system, and storage constraints can impose additional limits.

A bigfile tablespace has exactly one datafile for permanent storage. That file supports approximately 2^32 blocks. Its growth is managed through that single file. Adding a second datafile to a bigfile tablespace raises an error.

Convert a block-count ceiling to bytes using the tablespace's actual block size:

Approximate file-format ceiling in bytes = block ceiling × BLOCK_SIZE

For an 8 KiB block size, these scales are roughly 32 GiB per smallfile datafile and 32 TiB for a bigfile datafile. They describe format limits for that block size. The usable limit must also satisfy the platform, filesystem or ASM, and available-storage constraints. Choose the file model for the workload's growth and management needs. A large format limit alone does not establish available capacity.

For this lab, plan a smallfile, permanent, locally managed tablespace using automatic segment space management. Its proposed name is COURSE_TS53. Creation is reserved for Lesson 56B. See managing tablespaces for bigfile planning, and CREATE TABLESPACE for smallfile and bigfile structure and the storage clauses used in the next lesson.

Interpret the bounded growth settings

This is a planning fragment, not a command to execute:

SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE 256M

In the worked example, M values are expressed as mebibytes: one MiB is 1,048,576 bytes.

Setting	Planned behavior	Bytes
SIZE 64M	Establish an initial file size of 64 MiB	67,108,864
AUTOEXTEND ON	Allow the file to grow automatically when more space is needed	—
NEXT 16M	Configure a 16 MiB autoextension increment	16,777,216
MAXSIZE 256M	Bound automatic growth at a total file size of 256 MiB	268,435,456

The planned allowance beyond the initial size is:

256 MiB − 64 MiB = 192 MiB

NEXT expresses the configured increment used for autoextension. An operation's observed increase can reflect the demand Oracle must satisfy. Do not infer that every growth event must be exactly one 16 MiB increment. Check the resulting file size when verifying actual behavior.

If AUTOEXTENSIBLE is NO, the file has no automatic-growth allowance under its current setting. Oracle resets NEXT and MAXSIZE to zero when autoextension is disabled, so interpret these values together with the autoextension flag. See file specification for SIZE, AUTOEXTEND, NEXT, and MAXSIZE.

Convert block-based dictionary values

DBA_DATA_FILES.INCREMENT_BY is measured in Oracle blocks. DBA_TABLESPACES.BLOCK_SIZE is measured in bytes. Join the two views by tablespace name to interpret the configured increment correctly.

For the planned 16 MiB increment with an 8 KiB block size:

16 × 1,048,576 bytes ÷ 8,192 bytes/block = 2,048 blocks
2,048 × 8,192 bytes/block = 16,777,216 bytes = 16 MiB

Reading INCREMENT_BY = 2048 as 2,048 bytes would produce the wrong capacity plan. See DBA_DATA_FILES and DBA_TABLESPACES for the block-count and block-size fields used in that conversion.

Worked shared-capacity decision

Use these hypothetical planning inputs, measured before creating the proposed file:

Item	Assumed amount
Available capacity at the destination	384 MiB
Proposed file's full configured size	256 MiB
Forecast additional growth of neighboring files	96 MiB
Operating margin required by the lab's capacity policy	64 MiB

The full demand against the presently free pool is:

256 + 96 + 64 = 416 MiB
416 − 384 = 32 MiB shortfall

The 256 MiB includes the initial 64 MiB and the subsequent 192 MiB allowance. Count the new file once. The neighboring 96 MiB is forecast additional consumption beyond space already allocated. The 64 MiB is an assumed policy margin, not an observed Oracle reservation.

The equivalent check immediately after allocating the initial 64 MiB is:

384 − 64 = 320 MiB still available
192 + 96 + 64 = 352 MiB further demand and margin
352 − 320 = 32 MiB shortfall

Although the initial 64 MiB fits, the proposed full growth plan does not fit this forecast. Revise the requested growth, the neighboring demand, or the available capacity before approving creation. A reduced ceiling must still support the workload. An additional destination must be approved and measurable.

For ASM, use a capacity assessment that accounts for redundancy and the required recovery reserve. Raw FREE_MB alone is insufficient for that decision. USABLE_FILE_MB incorporates redundancy and reserve for applicable disk-group types. FLEX and EXTEND groups can report zero because that calculation is not provided. Have the instructor supply the correct usable-capacity assessment for the actual storage configuration. See administering ASM disk groups for capacity, redundancy, reserve, and the interpretation of available ASM space.

Management and placement choices

Local extent management uses bitmaps in the tablespace to track extent allocation. Automatic segment space management, shown as SEGMENT_SPACE_MANAGEMENT = AUTO, uses bitmaps to track free space within segments. These controls answer different storage questions. New permanent locally managed tablespaces normally use automatic segment space management; make both choices explicit in the creation plan. See managing tablespaces for local extent management and automatic segment space management.

For placement, select one approved method:

• Oracle Managed Files: verify the effective DB_CREATE_FILE_DEST in the intended PDB session. Oracle generates a unique filename. For a filesystem destination, the directory must already exist and the database process must have the necessary access.
• Explicit placement: obtain a unique, approved full filename within a verified writable destination. The later creation example must use a new file. REUSE is outside this exercise because it can reuse existing file contents.

Capacity evidence must refer to the destination that will actually receive the file. See DB_CREATE_FILE_DEST and using Oracle Managed Files for the effective destination and file-naming prerequisites.

Read-only lab practice

Baseline: Oracle Database 19c, single-instance Linux CDB, SQL*Plus connected directly to the designated LABPDB service as the instructor-provisioned COURSE_DBA account. This lesson does not create a tablespace, resize a file, grant privileges, change defaults, or connect to a database on your behalf.

Before practice, the instructor must provide:

• Existing read access to SYS.DBA_TABLESPACES and SYS.DBA_DATA_FILES in LABPDB. The SHOW PARAMETER check also requires suitable access to SYS.V_$PARAMETER.
• A verified service and account pair and a capacity report for the intended OMF or explicit-path destination, including other consumers and the approved margin.
• The planned COURSE_TS53 name, with no existing tablespace using that name. For any encrypted files included in metadata inspection, the required keystore must already be open under the instructor's runbook. This exercise does not operate a keystore.

All commands below are read-only examples. They have not been executed for these notes. No displayed planning number is a database result.

Confirm the session before interpreting container-specific results:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

Expected context is COURSE_DBA in LABPDB. Resolve a different identity or container with the instructor before proceeding. Do not treat root metadata as the target PDB's capacity plan.

Check the proposed name and inspect the existing permanent-tablespace configuration:

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_TS53';

SELECT tablespace_name, bigfile, block_size,
       extent_management, segment_space_management
FROM dba_tablespaces
WHERE contents = 'PERMANENT'
ORDER BY tablespace_name;

Before the later creation exercise, the first query should return no rows. BIGFILE = NO identifies smallfile tablespaces. LOCAL and AUTO identify the planned extent and segment-space management choices. Read the actual BLOCK_SIZE. Do not assume every tablespace uses 8 KiB.

Inspect existing permanent files and convert their configured increments:

SELECT d.tablespace_name, d.file_id, d.file_name,
       d.bytes, d.user_bytes, d.autoextensible, d.maxbytes,
       t.block_size, d.increment_by,
       d.increment_by * t.block_size AS increment_bytes,
       ROUND(d.increment_by * t.block_size / POWER(1024, 2), 3)
           AS configured_next_mib,
       CASE WHEN d.autoextensible = 'YES'
            THEN GREATEST(d.maxbytes - d.bytes, 0)
            ELSE 0
       END AS configured_auto_growth_bytes
FROM dba_data_files d
JOIN dba_tablespaces t
  ON t.tablespace_name = d.tablespace_name
WHERE t.contents = 'PERMANENT'
ORDER BY d.tablespace_name, d.file_id;

configured_auto_growth_bytes is a file-setting allowance. It does not establish that the shared destination can supply those bytes. The query reports existing files. The proposed file has no dictionary row before creation. The instructor's storage report supplies the separate physical-capacity evidence.

For the OMF choice, inspect the effective destination:

SHOW PARAMETER db_create_file_dest

A configured value establishes the intended naming destination. The instructor must also verify its actual existence, permissions, and usable capacity. For explicit placement, record the approved unique full path and the same destination-capacity evidence. See DB_CREATE_FILE_DEST and using Oracle Managed Files for that destination and its file-naming prerequisites.

Prepare a plan containing the file model, actual block size, LOCAL and AUTO, 64 MiB initial size, 16 MiB configured increment, 256 MiB ceiling, placement method, neighboring growth, and operating margin. Recalculate the shared budget using the instructor's current measurements.

Pass conditions: explain the distinction between allocated bytes, permitted automatic growth, and physical capacity; convert blocks to bytes correctly; reproduce the 32 MiB worked shortfall without counting the initial file twice; identify both a ceiling limit and a physical-capacity limit; and document a feasible plan in the correct PDB. If the budget fails, revise the plan before the next lesson.

Cleanup: none. This exercise reads metadata and prepares a plan. Tablespace creation and its dependency-aware cleanup belong to Lesson 56B.

Recap

Separate current file size, the configured automatic-growth ceiling, and the capacity the destination can actually supply. Choose smallfile or bigfile from the workload, convert the block-count ceiling with the tablespace block size, and bound growth with an initial size, increment, and maximum. Compare the new file's full size, neighboring growth, and the operating margin with that destination before approving creation. This lab plans smallfile permanent tablespace COURSE_TS53 with local extent management and automatic segment space management. Creation is reserved for Lesson 56B.

Quiz

1. Does MAXSIZE 256M reserve 256 MB of filesystem space at creation?

Yes, the full ceiling is allocated immediately. No, it bounds automatic growth; the initial size is allocated and later growth consumes available capacity. Yes, if the file uses local extent management. Only a bigfile tablespace reserves the full ceiling.

2. A file has INCREMENT_BY = 2048 and its tablespace has BLOCK_SIZE = 8192. What is its configured increment?

2,048 bytes. 8,192 bytes. 16,777,216 bytes, or 16 MiB. 2,048 MiB.

3. The destination has 384 MiB free before creation. A proposed file can grow to 256 MiB, neighbors need 96 MiB more, and the required margin is 64 MiB. What follows?

There is a 32 MiB shortfall; revise the plan before creation. There is a 128 MiB surplus. Creation automatically reserves all future neighboring growth. The remaining growth allowance is physical free space.

4. Which description of a bigfile tablespace is correct?

It permits multiple datafiles of unrestricted size. It has one datafile; its block-count limit must be interpreted using the actual block size and platform/storage limits. Its maximum size is always 32 TiB. It converts every smallfile tablespace automatically.

5. Which pairing matches the storage controls?

Local extent management tracks segment free space; ASSM names OMF files. Local extent management allocates user quotas; ASSM sets file ceilings. Local extent management tracks extent allocation; ASSM tracks free space within segments. Both controls measure filesystem free bytes.

Names, paths, and planning numbers in this lesson are unexecuted teaching examples. Run the read-only practice as COURSE_DBA in LABPDB in the designated Oracle Database 19c lab, confirm the exact release update, and record what you actually observe.

AIOps Pro

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