Create directory in Oracle

Directories must be created if external tables are used. Created directories are shown in either dba_directories or all_directories. There is no user_directories. When a directory has been created, the read and write object privileges can be granted on it

Related Data Dictionary Objects

dir$    all_directories         ku$_directory_t
        dba_directories         ku$_directory_view

System Privileges

GRANT create any directory TO <user_name>;
GRANT drop any directory TO <user_name>;

Syntax: CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>';

SQL Code:
create or replace directory attachment_dir as '/u02';

Select * from  all_directories
Where directory_name = 'ATTACHMENT_DIR'

Granting Privileges to a Directory

grant read, write on directory attachment_dir to cpdb;

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'ATTACHMENT_DIR';

Set UTL_FILE_DIR using ALTER SYSTEM command

alter system set UTL_FILE_DIR = '/u02/attachment_dir' scope=spfile;

OS Commend to create a directory

[oracle@vasdbsrv u02]$ mkdir /u02/attachment_dir
[oracle@vasdbsrv u02]$ ls -la
total 36
drwxrwxr-x   5 oracle oinstall  4096 Dec  8 13:08 .
drwxr-xr-x  26 root   root      4096 Dec  7 09:10 ..
drwxr-xr-x   2 oracle oinstall  4096 Dec  8 13:08 attachment_dir
drwxrwxr-x   2 oracle oinstall 16384 Nov 12 15:35 lost+found
drwxr-x---   3 oracle oinstall  4096 Nov 12 10:50 oradata

PLSQL Code to read or write a file to the directory

Source: {ORACLE_HOME}/rdbms/admin/utlfile.sql

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('ATTACHMENT_DIR', myfile.txt', 'w');
  utl_file.put_line(f, 'line test 1: text-1');
  utl_file.put_line(f, 'line test 2: text-2');
  utl_file.fclose(f);
end;
/

Drop Directory

Syntax: DROP DIRECTORY <directory_name>;
SELECT *
FROM dba_directories;

DROP DIRECTORY 'ATTACHMENT_DIR;