DIRECTORY

NOTE

Only the DBA team should have privileges to create a new DIRECTORY object (due to the vulnerability discussed here)

Check

Create

First create an OS level directory.. e.g. on UNIX compatible systems (commonly as root)...

Remember to apply the correct UNIX level permissions to the UNIX level directory when you create it.

mkdir -p /u01/out

chown oracle:dba /u01/out

chmod g+w /u01/out

Then create an Oracle database DIRECTORY object

(as SYSDBA or a user with the CREATE ANY DIRECTORY privilege)

CREATE DIRECTORY oraout AS '/u01/out';

Grant Privileges

NOTE: The Privileges must be GRANTed to a specific USER, they cannot be inherited from a ROLE.NOTE: You can also GRANT EXECUTE ON DIRECTORY which would allow &USER to run scripts from &DIR. Only GRANT this privilege where necessary. (See Bibliography for more detail).

GRANT read,write ON DIRECTORY &DIR TO &USER;

Ocassionally you may also need to grant JAVA privileges on a DIRECTORY. e.g.

dbms_java.grant_permission( '&USER', 'SYS:java.io.FilePermission', '&PATH', 'read,write' );

NOTE that this is GRANTing READ/WRITE directly on the OS level directory i.e. Java is bypassing the DIRECTORY object

Update

To change an existing Directory object without Dropping it, therefore retaining all existing Grants...

(as SYSDBA or a user with the CREATE ANY DIRECTORY privilege)

CREATE OR REPLACE DIRECTORY oraout AS '/u01/out/mydir';

Check OS Directories for Data Guard

In a Data Guard environment it's common for an OS Directory and the related Oracle Directory object to only get created on the Primary instance. In the event of a failover, this can lead to unexpected failures. Use these scripts to check the OS directory structure (for UNIX environments). These scripts assume you have a server with ssh access to both the server hosting the Primary instance and the Standby instance.

The scripts work by generating a list of OS directories from each server, eliminating any directories that exist on both servers (by sorting then removing all lines that are not unique) then reporting which directories are missing from which server...

Generate a file containing only directories that don't exist on both servers...

PRIMARY=myHost1

STANDBY=myHost2

MYPATH=/u01/out

for host in ${PRIMARY} ${STANDBY}

do

   ssh -q ${host} find ${MYPATH} -type d

done | sort | uniq -u >/tmp/diff$$.txt

Generate a report showing which files are missing from each server...

for host in ${PRIMARY} ${STANDBY}

do

   echo ${host}

   for file in $(cat /tmp/diff$$.txt)

   do

      ssh -q ${host} ls -ld ${file}

   done | grep "does not exist"

done

Cleanup...

rm /tmp/diff$$.txt

Bibliography