Home > Create directory in Oracle > Create directory in Oracle

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;
Advertisements
  1. Hank
    October 26, 2010 at 12:10 am

    I inadvertently create a directory in lower case.
    How do I remove it? when I use the DROP DIRECTORY myfolder;

    it changes myfolder to Upper Case and does not delete.

    • October 26, 2010 at 10:09 am

      You have to issue mkdir/rmdir as a OS command to create/remove the directory from the file system.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: