Create, replace or drop an Oracle directory object

An Oracle DIRECTORY object gives a database name to a filesystem path on the database server. The original article uses such an object for Data Pump exports. Creating the database object does not create the physical folder.

1. Connect with the appropriate permissions

The original example uses SQL*Plus with AS SYSDBA. Do not assume that this broad access is required for every task or that a schema name is a connection identifier. Ask the DBA to provide the correct connection and minimum necessary privileges.

Oracle documents CREATE ANY DIRECTORY for creating these objects and DROP ANY DIRECTORY for removing them. Filesystem permissions are separate, and Oracle's server process must have the appropriate access. See CREATE DIRECTORY.

2. Create or replace the object

The original syntax uses NOMBREDELAVARIABLE for the directory-object name and PATHDELDIRECTORIO for the server path:

CREATE OR REPLACE DIRECTORY NOMBREDELAVARIABLE AS 'PATHDELDIRECTORIO';

For example, after the administrator has prepared a dedicated folder and confirmed that the object is safe to create or replace:

CREATE OR REPLACE DIRECTORY MIEXPORTACION AS 'C:\Oracle\Exports';

Use your actual server path. This example replaces the original drive-root path and its stray trailing space. OR REPLACE changes an existing object's definition: check its users and dependent jobs before doing so.

Check the stored path

SELECT DIRECTORY_PATH
FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME = 'MIEXPORTACION';

With the adapted example, the expected path is C:\Oracle\Exports. Access to the DBA catalogue view requires suitable privileges. When an authorised Data Pump job uses this directory object, its files refer to that server location, not automatically to the client computer.

3. Drop the directory object

Only do this when the object is no longer needed and no jobs are using it:

DROP DIRECTORY MIEXPORTACION;

DROP DIRECTORY removes the database object, not the physical directory or its files. It can still break jobs that rely on the object.

The original final example contains the typo “DROM”. It is corrected to “DROP” here. The filesystem distinction and safer example path are also made explicit. These examples were not executed.