Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

📚 preface

Adding tablespace data files is a common part of the daily work of an Oracle DBA. If you do not use the OMF parameter, it will be a troublesome task.

☀ ️ OMF is introduced

Oracle OMF stands for Oracle Managed File.

OMF simplifies the administrator’s task by not specifying the name, size, and path of a file. The name, size, and path are automatically assigned by Oracle.

When you delete logs, data, and control files that are no longer needed, the OMF can automatically delete the corresponding OS files.

⭐️ Set OMF parameters

Using the OMF parameter, it is stored in the default generated file path.

Format for:

  • Data file:OMF/is/datafile path
  • Log file:OMF path/is/onlinelog /

Check whether the OMF feature is enabled

To determine whether OMF is enabled for the Oracle database, view the DB_CREATE_FILE_DEST parameter.

sqlplus / as sysdba
show parameter db_create_file_dest
Copy the code

If the DB_CREATE_FILE_DEST parameter is empty, OMF is disabled.

Configuration commands:

alter system set db_create_file_dest='/oradata';
Copy the code

The modification takes effect immediately!

Creating a data file

OMF not set:

alter tablespace users add datafile '/oradata/orcl/user02.dbf' size 1M autoextend off;
Copy the code

Data files need to be specifiedThe path.The name of the, need to query before each increase, increase the workload.

After setting OMF:

alter tablespace users add datafile size 1M autoextend off;
Copy the code

As shown above, you can add data files directly if you know the tablespace!

Creating a log file

The same goes for online redo logs!

After using the OMF parameter:

alter database add logfile group 20 size 512M;
Copy the code

You only need to specify the group!

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️