Setup a directory object to a shared directory
create directory DUMP_FILES as '/test/test01'; CREATE OR REPLACE DIRECTORY MY_DIR AS '/my_apps/my_files';
Query code for directories in the database
Change the where clause to search for a specific directory
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
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>