Oracle directory

An Oracle directory is a database object pointing to a operating system directory on the database server machine for reading and writing files.

Subdirectory access

Directories do not allow subdirectory traversal. That means that you need to create Oracle directories for all directories and subdirectories you need to access.

Examples

Create a database directory:

SQL> CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

Grant a user access to the directory:

SQL> GRANT read, write ON DIRECTORY tmp TO scott;

Revoke access from a directory:

SQL> REVOKE write ON DIRECTORY tmp FROM scott;

Allow a user to create their own directories (not recommended, seen as a security risk as users can overwrite any file on the OS):

SQL> GRANT create any directory TO scott;
SQL> GRANT drop any directory TO scott;

Related data dictionary view:

SQL> desc DBA_DIRECTORIES
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                            NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                   NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                            VARCHAR2(4000)

Also see

• UTL_FILE, PL/SQL package for reading and writing to files.