An Oracle database directory is a logical object mapping of a physical folder on the database server file system. We often need to create an oracle directory in our database to store binary files.

To create an Oracle database directory object, we first need to have a physical directory on the database server and CREATE ANY DIRECTORY privilege in our schema. We then use CREATE DIRECTORY SQL command to create a directory object.

In this article, we will see how to create an oracle directory in the oracle database using different tools and techniques as follows:

• Create a Physical Directory in Oracle Database Server Filesystem.
• Create Oracle Database Directory using SQL Command
• Create Oracle Database Directory using SQL Developer
• Create Oracle Database Directory using TOAD for Oracle
• Errors you may encounter while creating an oracle directory in the oracle database

Create a Physical Directory in Oracle Database Server Filesystem

Create Physical Directory in Windows & Linux OS Command

Step 1: Head over to the desired directory

C:\ // This is in Windows
/u01 // This is in Linux

Step 2: Create a physical directory using the below command

mkdir oraaskdir

Create Oracle Database Directory using SQL Command

After making the physical directory on the database server, it’s time to create the directory database object using the following command.

In Windows:

CREATE OR REPLACE DIRECTORY ORAASK_DIR AS 'C:\oraaskdir';

In Linux:

CREATE OR REPLACE DIRECTORY ORAASK_DIR AS '/u01/oraaskdir';

The above example uses the Oracle CREATE DIRECTORY command to create an oracle directory object mapped to our physical OS directory.

Create Oracle Database Directory using SQL Developer

To create an Oracle directory object in the oracle database using SQL Developer, follow the below steps:

Step 1: Navigate to the Connections navigator, open the schema node

Step 2: Right-click on the directory node > Create Directory

Step 3: Enter the directory name and directory server path

Step 4: Click on the Apply button to create the directory object

Using the above four simple steps, we created a new directory object called the “ORAASK_DIR” in our oracle database that is mapped to the physical database server directory in this path “C:\oraaskdir” using SQL Developer.

Create Oracle Database Directory using TOAD for Oracle

To create an Oracle directory object in the oracle database using TOAD, follow the below steps:

Step 1: Click on the menu Database > Create > Directory

Step 2: Enter the directory name and directory path

Step 3: Click on the Ok button to create the directory object

Using the above three simple steps, we created a new directory object called the “ORAASK_DIR” in our oracle database that is mapped to the physical database server directory in this path “C:\oraaskdir” using TOAD.

Errors you may Encounter While Creating an Oracle Directory in the Oracle Database

As we mentioned earlier in the article ,we have to have a CREATE ANY DIRECTORY to be able to create a database directory. In case we don’t have this privilege, we will get the following error message:

ORA-01031: insufficient privileges

To overcome this error, we can ask DBA to grant the required privilege to our database user.

Conclusion

In this article, we explained how to create an oracle directory in the oracle database using the SQL command line, TOAD, and SQL Developer tools with detailed steps and screenshots.