Mir Sayeed Hassan – Oracle Blog

How to create directory in Oracle database 11gR2

The directories must be created if any of the external tables are used in database for any operation as example for expdp/impdp etc & this directory should be granted the object privilege as read & write, data dictionary view are all_directories/dba_directories.

Database Status:

sys@TESTDB> select instance_name,status,version from V$instance;

INSTANCE_NAME STATUS VERSION
 ---------------- ------------ -----------------
 testdb OPEN 11.2.0.4.0

Create the directory at OS Level

[oracle@testdb backup]$ mkdir dir_test

[oracle@testdb backup]$ ls -la
 total 2340
 drwxrwxr-x. 2 oracle oracle 6 Feb 4 13:58 dir_test

Login to sqlplus, create & assign the directory location

sys@TESTDB> create or replace directory dir_test as '/backup/dir_test/';
 Directory created.

Grant the read, write privilege to directory

sys@TESTDB> grant read,write on directory dir_test to system;
 Grant succeeded.

Verify the directory created in database

sys@TESTDB> select * from all_directories where directory_name='DIR_TEST';

OWNER DIRECTORY_NAME DIRECTORY_PATH
 --------------------------------------------------------------------------------
 SYS DIR_TEST /backup/dir_test/

Drop the directory created if not require.

sys@TESTDB> drop directory dir_test;
 Directory dropped.

Verify

sys@TESTDB> select * from all_directories where directory_name='DIR_TEST';
 no rows selected

[oracle@testdb backup]$ rm -rf dir_test/

========Hence tested & verified in our test env=========