Oracle DBA Lesson 47A — Choose Where Oracle Creates Files

Oracle Managed Files (OMF) separates choosing storage from naming and managing database files. You choose an eligible destination. Oracle generates a unique file name, records the file in the database catalog, and manages its lifecycle through database operations. That is useful when you create a tablespace: the administrator can specify the storage policy and size without inventing each physical file name.

Oracle DBA Lesson 47A — Choose Where Oracle Creates Files

The practical outcome is to identify the effective destination, connect existing datafiles to their catalog records, and compare future file growth with real storage capacity.

Choose the destination; preserve Oracle's generated name

For a datafile or tempfile created without an explicit file name, a configured DB_CREATE_FILE_DEST supplies the default location. It can identify an operating-system directory or an Oracle Automatic Storage Management (ASM) disk group. OMF works with either. An ASM deployment is a separate storage choice.

For a filesystem destination, the base directory must already exist and be writable by the database's operating-system account. Oracle can create generated subdirectories and file names beneath it. A successful parameter query shows the configured value. The instructor still needs to verify storage availability and operating-system permissions before creation.

Manage generated files through supported database operations. Preserve the exact generated name and catalog relationship. Operating-system renaming or deleting a database file can invalidate that relationship and damage the database. A directory containing managed files can also contain other files. Directory membership alone establishes neither ownership nor permission to remove anything.

An existing database can contain both managed and explicitly named, unmanaged files. Inspect the file's creation history and recorded identity rather than classifying every file under one base as managed. See Oracle Managed Files.

Read the effective settings in the intended container

Use SQL*Plus in the instructor-designated Oracle 19c lab. The main exercise is read-only. Connect using the configured LABPDB service as the authorized local course account. Use the access method provided by the instructor.

SHOW CON_NAME
SHOW PARAMETER db_create_file_dest

SHOW CON_NAME establishes the container in which the session is working. For the PDB exercise it should identify LABPDB. Resolve a different container before interpreting its destination or creating anything. SHOW PARAMETER displays the effective setting in that session. DB_CREATE_FILE_DEST is PDB-modifiable in Oracle 19c and can also have a session setting. Inspect it in the same session that would perform the creation. See V$PARAMETER.

Interpret the result as follows:

Observed setting	Meaning for unnamed datafile/tempfile creation
Filesystem directory	Oracle uses that base and generates the file name. Eligibility, permissions, and capacity need verification.
ASM disk group	Oracle uses that disk group and generates the managed file name. Request an appropriate ASM capacity assessment.
Blank value	The required default is missing. An unnamed datafile or tempfile creation fails until an eligible destination is deliberately configured or a supported explicit specification is used.

Do not change a parameter merely to make the exercise run. Destination changes require the instructor's placement decision and the appropriate session, container, or system scope.

Separate the file families

SHOW PARAMETER db_recovery_file_dest
SHOW PARAMETER db_recovery_file_dest_size
SHOW PARAMETER db_create_online_log_dest

These queries expose related settings. They do not move any files.

File family	Relevant placement settings
Unnamed datafiles and tempfiles	DB_CREATE_FILE_DEST
Fast Recovery Area	DB_RECOVERY_FILE_DEST together with DB_RECOVERY_FILE_DEST_SIZE
Default control-file and online redo-log creation	Configured DB_CREATE_ONLINE_LOG_DEST_1 through _5 take precedence. If none are configured, data and recovery destinations supply the documented fallback locations.

For that default control and redo case, if both the data destination and the recovery destination are configured, Oracle can create a copy in each. With only one configured, it uses that one. Explicit CONTROL_FILES specifications, LOGFILE specifications, and operation-specific rules must be reviewed before any creation. This table is not a relocation procedure or permission to change the shared files.

Control files and online redo logs belong to the container database. Their actual creation and recovery configuration need the instructor's root/instance assessment. DB_RECOVERY_FILE_DEST is not PDB-modifiable in 19c. The 19c reference marks DB_CREATE_ONLINE_LOG_DEST_n PDB-modifiable, but that parameter property does not give a local PDB account authority over the shared control/redo lifecycle. Obtain the authorized root/instance settings when making those placement decisions. Different directory names also require separate verification before being treated as independent storage failure domains. See DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, and DB_CREATE_ONLINE_LOG_DEST_n.

Connect files to the catalog

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

FILE_ID identifies a datafile, TABLESPACE_NAME identifies its tablespace, and FILE_NAME is its recorded path. In this PDB exercise, read the inventory in the current container. Root-wide inventory is a separate exercise with suitable container-aware views and privileges. Tempfiles have a separate catalog. This query covers datafiles. See DBA_DATA_FILES.

Copy the exact recorded name into your observations. A recognizable generated name or container directory is a clue to managed placement. Use creation records to establish management history. A path outside today's default can be perfectly legitimate because of an earlier default, a supported move, or an explicit file name at creation.

Worked example: changing A to B

Suppose an existing managed file was created under destination A. Later, the effective default changes to destination B. The next unnamed managed datafile is created at B. The existing file remains at A with its recorded identity.

Changing DB_CREATE_FILE_DEST affects subsequent file creation. Relocating an existing datafile requires a separately planned, supported database move, with its own privileges, capacity, and recovery conditions. A move can temporarily require room for the original file and its copy. The video labels A and B are schematic destinations, not literal paths to enter into SQL. See Managing Data Files and Temp Files.

Check real capacity independently

Inspect existing file allocation and growth settings:

SELECT file_id, tablespace_name,
       ROUND(bytes / POWER(1024, 3), 3) AS allocated_gib,
       autoextensible,
       ROUND(maxbytes / POWER(1024, 3), 3) AS max_gib
FROM dba_data_files
ORDER BY file_id;

BYTES is the currently allocated physical file size. AUTOEXTENSIBLE indicates whether automatic extension is enabled. When it is enabled, MAXBYTES is the configured maximum. When it is disabled, interpret the current size as the present limit for automatic growth. Manual resizing requires a separate decision. The query does not measure free space in the backing filesystem or ASM disk group.

The hypothetical video example uses binary units: 1 GiB = 1,073,741,824 bytes. A file allocated at 1 GiB with a 3 GiB maximum has up to 2 GiB of further configured growth. A destination with only 1 GiB currently free cannot accommodate that full growth. Other files, workloads, reserves, and storage redundancy can reduce the headroom available to this file further.

Obtain a current, instructor-verified capacity report for the exact destination. For filesystem storage, assess the corresponding filesystem. For ASM, assess usable capacity under the disk group's redundancy and reserve requirements. Compare planned initial allocation, growth, and concurrent demand with that headroom. A maximum file size is a ceiling, not preallocated backing capacity.

Read-only practice and pass criteria

1. Confirm LABPDB, then record the effective DB_CREATE_FILE_DEST in that session.
2. Record at least one datafile's identifier, tablespace, and exact path. Explain how that file's history could differ from the current default.
3. Identify the data, recovery, and control/redo placement responsibilities. Ask the instructor for root/instance evidence where required.
4. Obtain allocation and growth settings and a current destination capacity report. Explain the 1 GiB / 3 GiB / 1 GiB calculation and include other storage demand in your decision.
5. Explain why a destination change leaves an existing file in place and which kind of supported operation would be needed to relocate it.

Pass when your explanation connects scope, destination, recorded identity, and available capacity without assuming that generated names authorize operating-system edits. If the capacity report or permissions are unavailable, leave those observations pending. Do not invent a successful check.

Optional instructor-authorized empty-tablespace exercise

Complete the read-only practice first. Perform this optional exercise only in the designated disposable lab, with explicit instructor authorization, CREATE TABLESPACE and DROP TABLESPACE privileges in LABPDB, access to the catalog checks, verified destination permissions and capacity, and the course's recovery and cleanup readiness. The instructor must provide broader catalog access for the emptiness and default-use checks below. Use a dedicated session with no pending work: these DDL statements commit implicitly, and ROLLBACK is not their cleanup mechanism. Tablespace deletion removes contents and is destructive.

Keep the generated file, tablespace, and any resulting allocation confined to this exercise. Do not create application objects, grant quotas, assign user defaults, or reuse an existing tablespace.

First verify the proposed name is unused:

SHOW CON_NAME
SHOW PARAMETER db_create_file_dest

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_OMF47A_TS';

Proceed only when the container and destination are correct and the name query returns no row. The explicit size and growth choice below avoid relying on OMF's default datafile allocation:

CREATE SMALLFILE TABLESPACE course_omf47a_ts
  DATAFILE SIZE 10M AUTOEXTEND OFF
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

SELECT file_id, tablespace_name, file_name, bytes, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'COURSE_OMF47A_TS';

The omitted file name lets Oracle generate the managed datafile at the effective destination. Record every returned file identifier and exact path before cleanup, and retain that record until the instructor confirms removal. This is a disposable empty tablespace exercise, not a storage creation recommendation for an application.

Before dropping, have the instructor confirm that this exact scratch tablespace is still empty, has no users or dependencies, and is not a database default. Useful catalog checks include:

SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE tablespace_name = 'COURSE_OMF47A_TS';

SELECT username
FROM dba_users
WHERE default_tablespace = 'COURSE_OMF47A_TS'
   OR temporary_tablespace = 'COURSE_OMF47A_TS';

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

The first two checks should return no matching rows. The database defaults must identify other tablespaces. These checks supplement the instructor's ownership and dependency confirmation. Stop if the scratch tablespace has been used or the required visibility is unavailable.

DROP TABLESPACE course_omf47a_ts INCLUDING CONTENTS AND DATAFILES;

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = 'COURSE_OMF47A_TS';

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

The post-drop queries should return no rows. The database removes its managed datafile through the tablespace lifecycle. INCLUDING CONTENTS AND DATAFILES explicitly requests file removal and must be confined to this verified scratch object. The instructor should separately confirm absence of only the recorded exact path through authorized storage inspection. Do not issue an operating-system deletion or remove anything based on a directory listing. If creation or drop fails, or an unexplained remnant remains, retain the observations and have the instructor investigate the database and storage state. See CREATE TABLESPACE, file_specification, and DROP TABLESPACE.

All commands and numeric examples here are instructional. No live Oracle execution or actual storage measurement is claimed. If encrypted catalog access needs an open keystore, obtain the instructor's approved setup. Do not change the keystore to bypass an access failure.

Quiz

1. Who chooses the OMF destination and the generated file name?

Oracle chooses both automatically from any directory The administrator configures the destination; Oracle generates the unique file name A user must supply a name for every managed datafile The tablespace name is the operating-system path

2. What happens to existing datafiles when DB_CREATE_FILE_DEST changes?

They all move immediately Their catalog paths change while the files stay behind They remain in place; future default creation uses the new value They are deleted and recreated automatically

3. Which storage choice can support OMF?

Only ASM Only the recovery area Any existing directory, regardless of permissions An eligible filesystem directory or ASM disk group

4. A 1 GiB file has a 3 GiB growth ceiling, while the destination has 1 GiB free. What should you conclude?

The full additional 2 GiB growth needs more available headroom The ceiling reserves 2 GiB of physical space OMF always supplies the missing storage DBA_DATA_FILES.MAXBYTES is the free filesystem capacity

5. How should the verified empty scratch tablespace and its managed file be removed?

Delete all files under the destination Rename the file and remove its catalog row manually Use the authorized database drop for that exact scratch tablespace, then verify catalog and recorded-path removal Run ROLLBACK after creation

All commands and numeric examples in this lesson are instructional. No live Oracle execution or actual storage measurement is claimed. Run the practice in the designated Oracle Database 19c lab and record what you actually observe. If encrypted catalog access needs an open keystore, obtain the instructor's approved setup. Do not change the keystore to bypass an access failure.

AIOps Pro

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