how to create dba directory in oracle 11g

Directories let database code interact with the file-system.
SQL> create or replace directory EXPORT_DIR as '/var/local/instance0/export';
SQL> create or replace directory IMPORT_DIR as '/var/local/instance0/import';
SQL> grant READ on directory IMPORT_DIR to APPlmgr;
SQL> grant WRITE on directory EXPORT_DIR to APPlmgr;

set linesize 200
col grantee format a7
col table_schema format a12
col table_name format a10
col privilege format a9
select grantee,table_schema,table_name,privilege
from all_tab_privs
where table_name = 'EXPORT_DIR' or table_name = 'IMPORT_DIR';

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

The ALL_DIRECTORIES data dictionary view will have information about all the directories that you have access to.
 That includes the operating system path

SQL> SELECT owner, directory_name, directory_path
  FROM all_directories

SQL> select * from all_objects where object_type ='DIRECTORY';
SQL> SELECT * FROM dba_directories;


System Privileges

SQL> GRANT create any directory TO <user_name>;

SQL> 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';

SQL> Select * from  all_directories
Where directory_name = 'ATTACHMENT_DIR'

Granting Privileges to a Directory

SQL> grant read, write on directory attachment_dir to cpdb;

SQL> SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'ATTACHMENT_DIR';

SQL> Set UTL_FILE_DIR using ALTER SYSTEM command

SQL> 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]$ ll

drwxr-xr-x   2 oracle oinstall  4096 Dec  8 13:08 attachment_dir

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_DIRmmyfile.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>;

SQL> SELECT * FROM dba_directories;

SQL> DROP DIRECTORY 'ATTACHMENT_DIR;

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...