【區別】“UTL_FILE_DIR引數” VS “DIRECTORY資料庫物件”

secooler發表於2011-06-16
  從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 --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-700177/,如需轉載,請註明出處,否則將追究法律責任。

相關文章