How to create directory in oracle database

Using 'create directory' statement to create directory object in oracle database. The following statement creates a directory database object that points to a directory on the server:

Syntax:-

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

Note:- 'directory_name' is unique directory name and 'operating_system_path' specify the full path name of operating system directory of the server.


SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/DATA01/TEST01';

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO APPS;


To check the Directory created.

SQL>SELECT *FROM DBA_DIRECTORIES;

SQL> SELECT *FROM ALL_DIRECTORIES;

OS Commend to create a directory:-

#mkdir -p /DATA01/TEST01
#chmod -R 777 /DATA01/TEST01

#ls -ltr /DATA01/TEST01


To check the privileges give on following directories.

SQL>SELECT directory_name, grantee, privilege  FROM user_tab_privs t,all_directories d WHERE t.table_name(+)=d.directory_name ORDER BY 1,2,3;



Drop Directory:-

SQL> DROP DIRECTORY 'TEST_DIR';