【区别】“UTL_FILE_DIR参数” VS “DIRECTORY数据库对象”

2020-01-16 17:16:02

从Oracle 9iR2版本开始使用“UTL_FILE_DIR参数”和“DIRECTORY数据库对象”都可以用于提供Oracle UTL_FILE包可访问和操作的系统目录信息。但从文章《【参数】关于UTL_FILE_DIR参数“新奇诡异”的修改方法》（http://space.itpub.net/519536/viewspace-700147）中的描述，我们可以看到无论使用pfile还是spfile调整UTL_FILE_DIR参数都需要重新启动数据库，这便是使用这种方法的大弊端。对于7*24小时运行的系统来说使用这种方法实现PL/SQL可操作的目录是不现实的。取而代之的便是DIRECTORY数据库对象。DIRECTORY数据库对象可以随需进行创建，同时权限上的限制也可以通过DCL语句简便调整。

MOS参考文章：Using CREATE DIRECTORY Instead of UTL_FILE_DIR init.ora Parameter [ID 196939.1]

Using CREATE DIRECTORY Instead of UTL_FILE_DIR init.ora Parameter

Pre 9iR2 (9.2) :
------------------
Each directory to be accessed by UTL_FILE must be listed (comma separated) in
the UTL_FILE_DIR init.ora parameter. This cannot be done with ALTER SYSTEM
(ORA-02095) - you have to bounce the database. The directory must be explicitly
specified again in UTL_FILE.FOPEN. And the number of characters for the utl_file_dir entry
is limited by the 255 character buffer size.

From 9iR2 :
---------------
Each directory to be accessed by UTL_FILE can be specified via the CREATE
DIRECTORY command. And specified via this level of indirection in
UTL_FILE.FOPEN.

It is the responsibility of the system and database administrators to implement
appropriate file and directory security on the database host. UTL_FILE won't
attempt to check for permission before executing an open/read/write/delete
request. We expect that the operating system will deny the request where
appropriate. UTL_FILE will blindly issue any action requested and look for
success or failure return status from the operating system.

By default UTL_FILE will have no file access because it will have no directory
access, until granted access by CREATE DIRECTORY by SYS or SYSTEM or a user
with DBA privileges. Since access privileges are granted on a per directory
basis, the DBA can control directory access by either (1) creating separate
directories for users with differing access requirements, or (2) use operating
system utilities and features for controling read and write access by users.

Good luck.

secooler
11.06.16

-- The End --