This section describes various administrative tasks to manage databases that
13.5.1 Guidelines for DIRECTORY Usage
Learn about the guidelines for efficient management of
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
Do not map a
DIRECTORYobject to a data file directory. A
DIRECTORYobject 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
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
DIRECTORYobjects should be granted to different users carefully. The same holds for the use of the
OPTIONclause when granting privileges to users.
Do not drop or replace
DIRECTORYobjects when database is in operation. If this were to happen, then operations from all sessions on all files associated with this
DIRECTORYobject fail. Further, if a
REPLACEcommand 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
FILECLOSEALLand 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
DIRECTORYobjects. Revoking a user's privilege on a
DIRECTORYobject using the
REVOKEstatement 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
FILECLOSEALLin 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.
See Also:Security on Directory Objects
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:
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
See Also:DIRECTORY Objects