Skip to content

DB_CREATE_FILE_DEST, How and Why

  • Oracle

DB_CREATE_FILE_DEST is a server parameter which specifies the default destination where new Oracle-managed data files (OMF) should be placed.

In this post, we'll talk about the following topics.

  1. How to Set DB_CREATE_FILE_DEST
  2. How to Use DB_CREATE_FILE_DEST

How to Set DB_CREATE_FILE_DEST

Since the server parameter can be changed online, so we can set it at system-level or session-level.

  1. System-Level
  2. Session-Level

Alter System Set DB_CREATE_FILE_DEST

RAC DB

For RAC databases, we should only specify the diskgroup name for the value, ASM knows where to categorize new data files.

SQL> alter system set db_create_file_dest='+DATA2' sid='*' scope=both;

System altered.

Single Instance DB

In contrast, we should specify the base directory for single-instance databases, because Oracle doesn't categorize new files in file system.

SQL> alter system set db_create_file_dest='/oradata2/ORCLCDB/ORCLPDB' scope=both;

System altered.

It just follows the base directory to create data files.

Alter Session Set DB_CREATE_FILE_DEST

For changing the value temporarily, you can set it in your session.

SQL> alter session set db_create_file_dest='+DATA2';

Session altered.

This could be useful when we want to allocate new datafiles evenly among several diskgroups.

How to Use DB_CREATE_FILE_DEST

To use DB_CREATE_FILE_DEST properly after setting a new place, we have several use cases:

  1. Create Tablespace Datafile
  2. Alter Tablespace Add Datafile
  3. Alter Database Move Datafile

Create Tablespace Datafile

Creating new tablespaces is a very common operation for a new database. With DB_CREATE_FILE_DEST specified, we don't have to specify the absolute path of files.

SQL> create tablespace ERPTBS datafile size 10m autoextend on next 10m maxsize unlimited, size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Then we check the full path of the files.

SQL> set linesize 100;
SQL> column name format a60;
SQL> select file#, name from v$datafile where name like '%erptbs%' order by 1;

     FILE# NAME
---------- ------------------------------------------------------------
         2 +DATA2/ORCL/DATAFILE/erptbs.282.1693915171
         8 +DATA2/ORCL/DATAFILE/erptbs.283.1693915171

Alter Tablespace Add Datafile

Adding new datafiles is a very common operation for a database in use. With DB_CREATE_FILE_DEST specified, we don't have to specify the absolute path of files.

SQL> alter tablespace ERPTBS add datafile size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

SQL> select file#, name from v$datafile where name like '%erptbs%' order by 1;

     FILE# NAME
---------- ------------------------------------------------------------
         2 +DATA2/ORCL/DATAFILE/erptbs.282.1693915171
         8 +DATA2/ORCL/DATAFILE/erptbs.283.1693915171
         9 +DATA2/ORCL/DATAFILE/erptbs.284.1693962938

Alter Database Move Datafile

To move a datafile from the original place to the default one, you don't have to specify the destination. More about moving datafiles to another places, we have some ways to do it.

SQL> alter database move datafile 7;

Database altered.

Then we check the full path of the file.

SQL> select file#, name from v$datafile where file# = 7;

     FILE# NAME
---------- ------------------------------------------------------------
         7 +DATA2/ORCL/DATAFILE/users.312.1103143591

As for Oracle-managed redo logfiles, you should set DB_CREATE_ONLINE_LOG_DEST_n for the default destination.

Leave a Reply

Your email address will not be published. Required fields are marked *