Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 47 Part 1 — Who Chooses the Data File Name?

Oracle Managed Files (OMF) lets Oracle generate unique file names and manage their lifecycle through database operations. The administrator chooses suitable creation destinations and growth settings. Existing file paths and the storage that is actually available still need inspection.

Oracle DBA Lesson 47 Part 1 — Who Chooses the Data File Name?

Destination, generated name, and storage

DB_CREATE_FILE_DEST supplies the default destination when a data file or temp file is created without a file name. The value can name a file-system directory or an Oracle Automatic Storage Management (ASM) disk group. OMF works with either destination. ASM is not required. ASM still provides storage-management features beyond OMF naming and lifecycle.

A file-system destination must already exist and be writable by the database software. Oracle generates a unique file name and may create database and container subdirectories under that destination. A pluggable database commonly has a subdirectory named with its globally unique identifier (GUID). The recorded path can therefore be much longer than the parameter value. Naming layouts differ by platform and storage type, so use the path stored in the catalog.

Managed files and explicitly named files can exist in the same database. A path under the OMF destination does not by itself show that the file is Oracle-managed, or that the file can be removed. Check its creation history, ownership, and catalog records. Trace, audit, and alert files stay outside OMF even when they share the directory tree. See Oracle Managed Files, PDB file placement, and ASM file naming.

Check the container and settings

Read the current user, the container, and the destination parameters in SQL*Plus:

SHOW USER
SHOW CON_NAME
SHOW PARAMETER db_create_file_dest
SHOW PARAMETER db_recovery_file_dest
SHOW PARAMETER db_create_online_log_dest

SHOW is a SQL*Plus client command. SHOW USER and SHOW CON_NAME report the session user and container. Each SHOW PARAMETER command lists the matching names, types, and values. The partial name db_create_online_log_dest can match destinations 1 through 5. See SQL*Plus SHOW.

One result for the data-file destination:

NAMETYPEVALUE
db_create_file_deststring/dbdata_a

Read that value in the same session and container. In Oracle Database 19c, DB_CREATE_FILE_DEST is session-modifiable, system-modifiable, and PDB-modifiable. It has no default value. A missing value has to be resolved before an unnamed tablespace data file can be created. A directory that happens to exist is not evidence of this setting. See DB_CREATE_FILE_DEST.

A creation statement can omit the file name

The administrator still chooses the tablespace and its size policy. The file name can be left out:

CREATE TABLESPACE app_data
  DATAFILE SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

When DB_CREATE_FILE_DEST is usable and this DATAFILE clause has no file name, Oracle generates an OMF name under the destination. SIZE is the initial size, NEXT is the autoextension increment, and MAXSIZE is the configured ceiling. In these Oracle size clauses, M means 1024 × 1024 bytes. This lesson calls that unit a mebibyte (MiB).

A complete ordinary file name selects an explicitly named file. ASM file specifications, and conversion clauses that belong to a particular operation, have additional rules. The statement above has no explicit file name and no file-conversion override. See CREATE TABLESPACE.

Find the file Oracle recorded

SELECT file_id, tablespace_name, file_name
FROM dba_data_files
ORDER BY file_id;

The catalog row for the file created above uses one continuous path:

FILE_IDTABLESPACE_NAMEFILE_NAME
7APP_DATA/dbdata_a/DB01/8C24A1D7B5CE4A759023D49C12483C6E/datafile/o1_mf_app_data_n8p1c2d3_.dbf

FILE_ID identifies the data file, TABLESPACE_NAME identifies the tablespace that owns it, and FILE_NAME is the location Oracle recorded. The generated suffix is an observed catalog value. Use that recorded path when you inspect the file.

DBA_DATA_FILES lists data files visible in the current container. Temp files are listed in DBA_TEMP_FILES. A root-wide listing uses the matching CDB_ views for the containers in scope. An encrypted tablespace can require an open keystore before its data-file row can be retrieved. See DBA_DATA_FILES.

Changing a default affects future creation

Suppose the effective data-file destination changes from /dbdata_a to /dbdata_b:

ItemAfter the change
Previously created APP_DATA fileKeeps its recorded path under /dbdata_a
Subsequent unnamed data-file creation using the new settingUses /dbdata_b and receives a newly generated name
Previously explicitly named fileKeeps its own recorded path

Changing the parameter does not relocate existing files. A move is a separate database operation, planned for the correct scope, availability, capacity, and recovery conditions. The change shown here is a new creation request: the next unnamed data file is created at the new destination and receives a new name. The earlier file stays on its recorded path. See managing data files and temp files.

Keep lifecycle tied to the owning object

Supported operations on managed data files keep the catalog record and the physical file together. Dropping an eligible tablespace removes its managed files. Other file types follow their own operations and retention rules. A data-file drop is not a general recovery-file cleanup policy.

Renaming or deleting a live file with operating-system tools can leave the database referring to the old path and can disrupt access. Changing an OMF name can also stop Oracle from recognizing the file as managed. RENAME FILE has its own control-file and physical-file rules, and it does not replace the supported move for an OMF file.

Remove the tablespace from the omitted-name statement with that tablespace's own drop:

DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;

INCLUDING CONTENTS AND DATAFILES removes that tablespace's contents and its files. After the drop, the tablespace row and the file row are gone, and so is the physical file that was recorded. Confirm those three results. Do not add an rm of the path, and do not delete other files because their names look similar. A recorded file that remains needs investigation of that file. See DROP TABLESPACE.

Distinguish file families and destination rules

SettingRole and relevant conditions
DB_CREATE_FILE_DESTDefault location for unnamed data files and temp files. It can also be a default for online redo and control files when no dedicated online-log destination is set.
DB_RECOVERY_FILE_DESTFast recovery area location. DB_RECOVERY_FILE_DEST_SIZE is also required. The area holds eligible recovery files such as archived redo logs, flashback logs, and backups. Exact backup and archive placement also depends on those operations' own settings.
DB_CREATE_ONLINE_LOG_DEST_1 through DB_CREATE_ONLINE_LOG_DEST_5Dedicated creation destinations for online redo and control files. They take precedence over DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST for those file types. Multiple destinations create multiplexed members and copies.

When no dedicated online-log destination is set, documented redo and control-file creation can use both the data-file destination and the recovery destination. When only one of those general destinations is set, that destination can supply the applicable default. An explicit file specification, or a rule that belongs to the statement, still applies. Read the OMF rules for the file type before treating one parameter as a redirect for every file.

In Oracle Database 19c, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE are not PDB-modifiable. DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n are PDB-modifiable. Control files and online redo logs belong to the database and instance that own them, so PDB modifiability does not give a PDB its own redo or control files. Check the parameter scope and the creation operation separately. Two directory names do not, by themselves, establish two physical failure domains. See Oracle Managed Files, DB_CREATE_ONLINE_LOG_DEST_n, DB_RECOVERY_FILE_DEST, and DB_RECOVERY_FILE_DEST_SIZE.

Compare growth allowance with usable storage

Read the current size, autoextension, and ceiling from the same catalog:

SELECT file_id, tablespace_name,
       bytes / 1024 / 1024 AS current_mib,
       autoextensible,
       maxbytes / 1024 / 1024 AS max_mib
FROM dba_data_files
ORDER BY file_id;
FILE_IDTABLESPACE_NAMECURRENT_MIBAUTOEXTENSIBLEMAX_MIB
7APP_DATA100YES500

Here the file is 100 MiB with a 500 MiB ceiling, so another 400 MiB of growth is configured. If the destination has only 180 MiB usable, storage can run out first. Other files can consume the same capacity. A growth limit is an allowance. It does not reserve physical space.

On a file system, check the mounted destination's free capacity, permissions, and any quota or storage limit. For ASM, disk-group capacity includes USABLE_FILE_MB. Mirroring and reserve space can make safely usable space smaller than raw free space. The view definition and the storage policy decide how to read that number. The fast recovery area has its own size limit, and its own retention and reclaim behavior. See ASM disk-group statistics and managing data files and temp files.

Recap

The administrator chooses the destination and the growth settings. When a data-file clause omits the file name, Oracle generates an OMF name under a usable DB_CREATE_FILE_DEST and records the full path. Changing that default affects later unnamed creation. Existing files keep their recorded paths. A growth ceiling does not reserve storage, and a managed file is removed with the database operation for its owning tablespace. Online redo, control files, and the fast recovery area follow their own destination rules.

Quiz

1. Will changing DB_CREATE_FILE_DEST relocate existing files?

2. What enables the unnamed data-file creation shown above?

3. Which evidence gives an existing data file's recorded path?

4. The file is 100 MiB, its ceiling is 500 MiB, and usable shared storage is 180 MiB. What follows?

5. How should you retire the exact disposable tablespace created by the omitted file-name statement?

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