首页 > Linux操作系统 > Linux操作系统 > 【区别】“UTL_FILE_DIR参数” VS “DIRECTORY数据库对象”


原创 Linux操作系统 作者:secooler 时间:2011-06-16 22:01:46 0 删除 编辑
  从Oracle 9iR2版本开始使用“UTL_FILE_DIR参数”和“DIRECTORY数据库对象”都可以用于提供Oracle UTL_FILE包可访问和操作的系统目录信息。但从文章《【参数】关于UTL_FILE_DIR参数“新奇诡异”的修改方法》(中的描述,我们可以看到无论使用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

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.


-- The End --

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。


  • 博文量
  • 访问量