Oracle directory

From Oracle FAQ
Jump to: navigation, search

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

[edit] 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.

[edit] 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)

[edit] Also see

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