To create directory in oracle we can use ‘create directory‘ statement (CREATE OR REPLACE DIRECTORY <directory_name> AS ‘<operating_system_path>’;).
In this article, we will learn how to create directory in oracle. The following statement will create directory oracle database object that points to a directory on the server:
How to create directory in oracle.
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';To grant read, write on to user.
SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO APPS;To check the dba directory created.
SQL>SELECT *FROM DBA_DIRECTORIES;
SQL> SELECT *FROM ALL_DIRECTORIES;OS Commend to create oracle directory: –
#mkdir -p /DATA01/TEST01
#chmod -R 777 /DATA01/TEST01
#ls -ltr /DATA01/TEST01Drop directory in oracle: –
SQL> DROP DIRECTORY 'TEST_DIR';How to check privilege on oracle directories.
To check the given privileges on oracle directories, use following script: –
SQL>SELECT *from all_tab_privs where table_name in (select directory_name from dba_directories where directory_name='XXX_FILES');
SQL>select
p.table_name
, p.table_schema
, p.privilege
, p.grantee
, p.grantor
, o.object_type
from all_tab_privs p
join all_objects o on o.owner = p.table_schema
and o.object_name = p.table_name
and p.table_name like '&v_object_name'
--and o.object_type like '&v_object_type'
order by p.table_name, p.table_schema, p.grantee ;
SQL>SELECT * FROM all_tab_privs WHERE table_name = 'XXX_XXX_XXX';
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;Useful Post: –