Setup a directory object to a shared directory

[code language=”sql”]

create directory DUMP_FILES as ‘/test/test01’;

CREATE OR REPLACE DIRECTORY MY_DIR AS ‘/my_apps/my_files’;

[/code]

Query code for directories in the database
Change the where clause to search for a specific directory

[code language=”sql”]
select directory_name, directory_path
from dba_directories
where directory_name=’DATA_PUMP_DIR’;

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

impdp nbkzzzz/password@testd01_svc01
tables=blah01,blah02
directory=TEST01_DATA_DIR
dumpfile=TESTMU01_BLAH_ML_TAB_Export_20190415.log

USERID=nbkzzzz/password
DUMPFILE=TESTMU01_BLAH_ML_TAB_20190415.expd
LOGFILE=
DIRECTORY=
SCHEMAS=BLAH
REMAP_SCHEMA=hr:hrtest
INCLUDE=TABLE:”IN (‘table1′,’table2’)”

expdp userid=user/pw@//host:port/service-name

impdp userid=user/pw@//host:port/service-name
[/code]

[code language=”sql”]
CREATE ROLE MY_ROLE NOT IDENTIFIED;
GRANT MY_ROLE TO MY_USER;
GRANT READ, WRITE ON DIRECTORY MY_DIR TO MY_ROLE;

GRANT EXECUTE ON SYS.UTL_FILE TO MY_USER;

CREATE OR REPLACE PROCEDURE MY_USER.TEST_WRITEFILE IS
out_File UTL_FILE.FILE_TYPE;
BEGIN
out_File := UTL_FILE.FOPEN (‘MY_DIR’, ‘test.txt’, ‘W’);
UTL_FILE.PUT_LINE (out_File, ‘hello world’);
UTL_FILE.FCLOSE (out_File);
END;

SQL> conn / as sysdba
Connected.

SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS ‘c:\temp’;

Directory created.

SQL>
SQL> CREATE ROLE MY_ROLE;

Role created.

SQL> GRANT READ, WRITE ON DIRECTORY MY_DIR TO MY_ROLE;

Grant succeeded.

SQL>
SQL> GRANT connect, MY_ROLE TO MY_USER identified by my_user;

Grant succeeded.

SQL>
SQL> GRANT EXECUTE ON SYS.UTL_FILE TO MY_USER;

Grant succeeded.

SQL>
SQL> CREATE OR REPLACE PROCEDURE MY_USER.TEST_WRITEFILE IS
2 out_File UTL_FILE.FILE_TYPE;
3 BEGIN
4 out_File := UTL_FILE.FOPEN (‘MY_DIR’, ‘test.txt’, ‘W’);
5 UTL_FILE.PUT_LINE (out_File, ‘hello world’);
6 UTL_FILE.FCLOSE (out_File);
7 END;
8 /

Procedure created.

SQL>
SQL>
SQL> conn my_user/my_user
Connected.

SQL> exec TEST_WRITEFILE
BEGIN TEST_WRITEFILE; END;

*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "MY_USER.TEST_WRITEFILE", line 4
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

SQL> GRANT READ, WRITE ON DIRECTORY MY_DIR TO my_user;

Grant succeeded.

SQL> conn my_user/my_user
Connected.

SQL>
SQL> exec TEST_WRITEFILE

PL/SQL procedure successfully completed.

SQL>
SQL>

[/code]