How to Create Directory in Oracle

Posted by antonionavarror on

hello guys

In this post I want to review what it is and the options to create a directory in Oracle. It will work for all versions of the RDBMS.

What is an Oracle directory, basically it is an alias to a directory of the operating system. With it we will be able to read or write the files that are located inside it.

Creating an oracle directory is a very powerful feature of the database engine that is used to work in bulk, reading or writing files.


Oracle directories, why create an oracle directory?

It is a simple, fast and secure way to work with files. For when we download for example invoices of millions of customers in pdf or when we upload files generated by a sensor network.

It is important for you to see that it is a way to work mainly with batch and mass processing.


Syntax to create an oracle directory

The syntax to create a directory is quite easy. You can see it in the following code snippet

Create directory <directoy_name> AS ‘<path>’;

The syntax to recreate a directory that already exists is;

Create or recreate directory <directoy_name> AS ‘<path>’;


Create or replace directory

Let’s see how you can create a directory in several ways

Create a directory with Sqlplus

  • Step 1: Logon with sqlplus to the database
  • Step 2: Create directory mydirectory as ‘/tmp/bulk’;

Create a directory with Toad

Toad is one of the best tools for working with Oracle. Created in the 90’s by Quest Software.

  • Step 1: in menu Database, click on Create, click on Directory
  • Step 2: A Create Directory window appears.
  • Step 3: Enter the name MYDIRECTORY
  • Step 4: Enter the path /tmp/bulk
  • Step 5: Click OK

Create a directory with SQL Developer

SQL Developer is a tool developed in JAVA by Oracle, Jeff Smith is the Product Manager.

  • Step 1: Go to Connections, click on Schematic.
  • Step 2: Select Directories and right-click on it.
  • Step 3: Choose Create Directory
  • Step 4: A window with the title Create Directory will pop up.
  • Step 5: Enter the name MYDIRECTORY
  • Step 6: Enter the path /tmp/bulk
  • Step 7: Click on Apply


How grant read write on directory

Once you have created your directory in the database you need to give permission to a user or schema, you can give it read or write permission. 

&nbsp;
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;">&nbsp;</p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;">-- permission of write to user antonio</span></p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;">GRANT WRITE ON&nbsp; DIRECTORY MYDIRECTORY TO ANTONIO;</span></p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;">&nbsp;</p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;">-- permission of read to user antonio</span></p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;">GRANT READ ON&nbsp; DIRECTORY MYDIRECTORY TO ANTONIO;</span></p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:0;padding:0 0 7pt;">&nbsp;</p>
<p dir="ltr" style="line-height:1.896;background-color:#ffffff;margin-top:0;margin-bottom:7pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;">



Create directory on remote server

Normally when we create a database directory we do it on a physical directory that is on the same machine. You can create a remote directory, on Unix/Linux for example using NFS (Network File System).

It is not a good idea to work with this kind of directories located on remote machines. They give performance problems and since this feature is oriented to work in bulk it can be quite penalizing.


List directories 

You have already created your directories or directories, to work with them Oracle has an inventory of them in several views. I recommend you to use the DBA_DIRECTORIES. 

SELECT * FROM DBA_DIRECTORIES;


Create directories in different operating systems.

At this point of the post, you already know how to create a directory without problems. Oracle allows us to do it in a standard way independent of the operating system where we are.

We are going to see three examples to create directories in Unix/Linux, Windows and Open VMS, all cases are the same;

  • Unix/linux 👉 create directory mydirectory as ‘/users/antonio/data’;
  • Windows 👉 create directory mydirectory as ‘d:\data\bulk’;
  • OpenVMS 👉 create directory mydirectory as ‘disk$data8:[data.download]’;

In case you don’t know how to create a directory in Linux, here you can see how to do it;



HTH – Antonio NAVARRO



< Netsuite Login Step by Step

> How to Select Top 100 in Oracle, 2 Methods

Leave a comment