In this second tutorial of the Control File Series, I will answer the second most asked question on my YouTube channel: What is the location of the Oracle Control file or where to find the control file in the Oracle Database.

CONTROL_FILES initialization parameter.

In the last tutorial, I mentioned CONTROL_FILES initialization parameter. Today we’re gonna need this parameter, so let me explain what it is.

To understand about CONTROL_FILES initialization parameter we first need to understand what initialization parameters are.

Initialization Parameters

To manage the Oracle instances, the Oracle Universal installer configures parameters that can affect the basic operations of the instance. These parameters are called Initialization parameters.

Initialization parameters are stored in parameter files famously known as SPFILE or PFILE. On startup, Oracle instance reads initialization parameters from these.

Now let’s come to our CONTROL_FILES initialization parameters.

CONTROL_FILES initialization Parameter specifies one or more control file names along with their location in the database.

Whenever we execute CREATE DATABASE statement, all the control files listed in this CONTROL_FILES initialization parameter get created.

What is the location of Oracle Control file

Now let’s come to our main question which is What is the location of the Oracle Control File?

We can find out the name and location of the control file using the said initialization parameter like this –

Step 1: Log on to your database

C> SQLPLUS / as sysdba

I am connected with my database using sys user. Next, simply write

SQL> SHOW PARAMETER control_files;

On execution, this statement will show you the name of the control file along with the location.

v$Controlfile View

The second way of finding out the name and the location of the control file in Oracle is by using v$controlfile dynamic performance view. Like this –

Connect To Your Database using the SYS user

C> SQLPLUS / as sysdba

Let’s write the SELECT statement

SQL> SELECT name FROM v$controlfile;

On execution, this statement will also show you the names of the control file along with the location.

These are the two most common ways to find the name and location of the control files in the Oracle Database.

So subscribe to my YouTube channel as in the next tutorial we will learn how to read a control file in Oracle Database.

Thanks and have a great day