BFILEs Management

13.5.1 Guidelines for DIRECTORY Usage

Learn about the guidelines for efficient management of DIRECTORY objects.

The main goal of the DIRECTORY feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server file system. But to realize this goal, it is very important that the DBA follow these guidelines when using DIRECTORY objects:

Do not map a DIRECTORY object to a data file directory. A DIRECTORY object should not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.
Only the DBA should have system privileges. The system privileges such as CREATE ANY DIRECTORY or DROP ANY DIRECTORY(granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.
Use caution when granting the DIRECTORY privilege. Privileges on DIRECTORY objects should be granted to different users carefully. The same holds for the use of the WITH GRANT OPTION clause when granting privileges to users.
Do not drop or replace DIRECTORY objects when database is in operation. If this were to happen, then operations from all sessions on all files associated with this DIRECTORY object fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, then the references to these files are lost in the programs, and system resources associated with these files are not be released until the session(s) is shut down.

The only recourse left to PL/SQL users, for example, is to either run a program block that calls DBMS_LOB.FILECLOSEALL and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.
Use caution when revoking a user's privilege on DIRECTORY objects. Revoking a user's privilege on a DIRECTORY object using the REVOKE statement causes all subsequent operations on dependent files from the user's session to fail. The user must either re-acquire the privileges to close the file, or run a FILECLOSEALL in the session and restart the file operations.

In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.

DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.

13.5.2 Rules for Using Directory Objects and BFILEs

You can create a directory object or BFILE objects if these conditions are met.

When you create a directory object or BFILE objects, ensure that the following conditions are met:

The operating system file must not be a symbolic or hard link.
The operating system directory path named in the Oracle DIRECTORY object must be an existing operating system directory path.
The operating system directory path named in the Oracle DIRECTORY object should not contain any symbolic links in its components.

13.5.3 Setting Maximum Number of Open BFILEs

Only limited number of BFILEs can be open simultaneously in each session. Learn to define this number in this section.

The initialization parameter, SESSION_MAX_OPEN_FILES, defines an upper limit on the number of simultaneously open files in a session.

The default value for this parameter is 10. Using this default, you can open a maximum of 10 files at the same time in each session. To alter this limit, the database administrator must change the parameter value in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

If the number of unclosed files reaches the SESSION_MAX_OPEN_FILES value, then you cannot open additional files in the session. To close all open files, use the DBMS_LOB.FILECLOSEALL call.