oracle 如何查看dir,ORACLE 11G UTL_FILE_DIR简单测试

UTL_FILE_DIR

lets you specify one or more directories that Oracle should use for PL/SQL file I/O.

If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file.

All users can read or write to all files specified by this parameter.

Therefore all PL/SQL users must be trusted with the information in the directories specified by this parameter.

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter.

However, UTL_FILE_DIR access is no longer recommended. <==========这种方式来访问os层面的文件,oracle已经不推荐了

Oracle recommends that you instead use the directory object feature, which replaces UTL_FILE_DIR.

Directory objects offer more flexibility and granular control to the UTL_FILE application administrator,

can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools.

CREATE ANY DIRECTORY privilege is granted only to SYS and SYSTEM by default.

利用utl_file_dir参数来操作os层面的文件

SQL> host more /home/oracle/trsen/trsen.txt

SB O

SQL> show parameter utl_file_dir

NAME TYPE VALUE

———————————— ———– ——————————–

utl_file_dir string /home/oracle/, /home/oracle/trsen

SQL>set serverout on;

SQL> DECLARE

2 V1 VARCHAR2(32767);

3 F1 UTL_FILE.FILE_TYPE;

4 BEGIN

5 F1 := UTL_FILE.FOPEN(‘/home/oracle/trsen’,’trsen.txt’,’R’);

6 UTL_FILE.GET_LINE(F1,V1);

7 DBMS_OUTPUT.PUT_LINE(V1);

8 UTL_FILE.FCLOSE(F1);

9 END;

10 /

SB O<====================================输出结果

PL/SQL procedure successfully completed.

利用object directory来访问os层面的文件

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME

—————————— ——————————

DIRECTORY_PATH

——————————————————————————–

SYS ORACLE_OCM_CONFIG_DIR

/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SYS DATA_PUMP_DIR

/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/

SYS DUMP<==================================需要访问的数据文件路径

/home/oracle

SQL> DECLARE

2 V1 VARCHAR2(32767);

3 F1 UTL_FILE.FILE_TYPE;

4 BEGIN

5 F1 := UTL_FILE.FOPEN(‘DUMP’,’cDB.sql’,’R’);

6 UTL_FILE.GET_LINE(F1,V1);

7 DBMS_OUTPUT.PUT_LINE(V1);

8 UTL_FILE.FCLOSE(F1);

9 END;

10 /

create database “PROD1″<=========================结果集

PL/SQL procedure successfully completed.