The DBMS_FILE_GROUP
package, one of a set of Streams packages, provides administrative interfaces for managing file groups, file group versions, and files. A file group repository is a collection of all of the file groups in a database and can contain multiple versions of a particular file group. This package can be used to create and manage file group repositories.
This chapter contains the following topics:
Overview
Constants
This section contains topics which relate to using the DBMS_FILE_GROUP
package.
The following terms pertain to the DBMS_FILE_GROUP
package:
A file is a reference to a file stored on hard disk. A file is composed of a file name, a directory object, and a file type. The directory object references the directory in which the file is stored on hard disk. For example, a file might have the following components:
The file name is expdat.dmp
.
The directory object that contains the file is db_files
.
The file type is DBMS_FILE_GROUP.EXPORT_DUMP_FILE
.
A version is a collection of related files. For example, a version might consist of a set of datafiles and a Data Pump export dump file generated by a Data Pump transportable tablespace export. Only one Data Pump export dump file is allowed in a version.
A file group is a collection of versions. A file group can be used to logically group a set of versions. For example, a file group named financial_quarters
can keep track of quarterly financial data by logically grouping versions of files related to a tablespace set. The tablespaces containing the data can be exported at the end of each quarter and versioned under names such as Q1FY04
, Q2FY04
, and so on.
The DBMS_FILE_GROUP
package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name. For example, specify DBMS_FILE_GROUP.EXPORT_DUMP_FILE
for an export dump file.
Table 41-1 DBMS_FILE_GROUP Parameters with Enumerated Constants
Parameter | Enumerated Constant | Type | Description |
---|---|---|---|
|
|
|
|
|
|
|
|
|
System privilege specified in the
Object privilege specified in the
|
|
|
Table 41-2 DBMS_FILE_GROUP Package Subprograms
Subprogram | Description |
---|---|
Adds a file to a version of a file group |
|
Alters a file in a version of a file group |
|
Alters a file group |
|
Alters a version of a file group |
|
Creates a file group |
|
Creates a version of a file group |
|
Drops a file group |
|
Drops a version of a file group |
|
Grants object privileges on a file group to a user |
|
Grants system privileges for file group operations to a user |
|
Purges a file group using the file group's retention policy |
|
Removes a file from a version of a file group |
|
Revokes object privileges on a file group from a user |
|
Revokes system privileges for file group operations from a user |
Note:
All subprograms commit unless specified otherwise.This procedure adds a file to a version of a file group.
DBMS_FILE_GROUP.ADD_FILE( file_group_name IN VARCHAR2, file_name IN VARCHAR2, file_type IN VARCHAR2 DEFAULT NULL, file_directory IN VARCHAR2 DEFAULT NULL, version_name IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 41-3 ADD_FILE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group that contains the version, specified as |
|
The name of the file being added to the version. Each file name in a version must be unique. |
|
The file type. The following are reserved file types:
If the file type is not one of the reserved file types, then either enter a text description of the file type, or specify See "Constants" for more information about the reserved file types. |
|
The name of the directory object that corresponds to the directory containing the file. If If If |
|
The name of the version to which the file is added. If a positive integer is specified as a If |
|
Comments about the file being added |
To run this procedure with either DBMS_FILE_GROUP.EXPORT_DUMP_FILE
or 'DUMPSET'
specified for the file_type
parameter, a user must meet the following requirements:
Have the appropriate privileges to import the Data Pump export dump file
Have READ
privilege on the directory object that contains the Data Pump export dump file
See Also:
Oracle Database Utilities for more information about Data Pump privilegesThis procedure alters a file in a version of a file group.
DBMS_FILE_GROUP.ALTER_FILE( file_group_name IN VARCHAR2, file_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, new_file_name IN VARCHAR2 DEFAULT NULL, new_file_directory IN VARCHAR2 DEFAULT NULL, new_file_type IN VARCHAR2 DEFAULT NULL, remove_file_type IN VARCHAR2 DEFAULT 'N', new_comments IN VARCHAR2 DEFAULT NULL, remove_comments IN VARCHAR2 DEFAULT 'N');
Table 41-4 ALTER_FILE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group that contains the version, specified as |
|
The name of the file being altered in the version |
|
The name of the version that contains the file being altered. If a positive integer is specified as a If |
|
The new name of the file if the file name is being changed. Each file name in a version must be unique. If Note: When a non- |
|
The new name of the directory object that corresponds to the directory containing the file, if the directory object is being changed. If Note: When a non- |
|
The file type. The following are reserved file types:
If the file type is not one of the reserved file types, then enter a text description of the file type. If See Also: "Constants" for more information about the reserved file types. |
|
If If |
|
New comments about the file being altered. If non- If |
|
If If |
If the file type is changed to DBMS_FILE_GROUP.EXPORT_DUMP_FILE
or 'DUMPSET'
, then Data Pump metadata for the file is populated. If the file type is changed from DBMS_FILE_GROUP.EXPORT_DUMP_FILE
or 'DUMPSET'
, then Data Pump metadata for the file is purged.
To run this procedure with DBMS_FILE_GROUP.EXPORT_DUMP_FILE
or 'DUMPSET'
specified for the new_file_type
parameter, a user must meet the following requirements:
Have the appropriate privileges to import the Data Pump export dump file
Have READ
privilege on the directory object that contains the Data Pump export dump file
See Also:
Oracle Database Utilities for more information about Data Pump privilegesThis procedure alters a file group.
DBMS_FILE_GROUP.ALTER_FILE_GROUP( file_group_name IN VARCHAR2, keep_files IN VARCHAR2 DEFAULT NULL, min_versions IN NUMBER DEFAULT NULL, max_versions IN NUMBER DEFAULT NULL, retention_days IN NUMBER DEFAULT NULL, new_default_directory IN VARCHAR2 DEFAULT NULL, remove_default_directory IN VARCHAR2 DEFAULT 'N', new_comments IN VARCHAR2 DEFAULT NULL, remove_comments IN VARCHAR2 DEFAULT 'N');
Table 41-5 ALTER_FILE_GROUP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group being altered, specified as |
|
If If If Note: If the file group is dropped as a result of a |
|
The minimum number of versions to retain. The specified value must be greater than or equal to If |
|
The maximum number of versions to retain. The specified value must be greater than or equal to the value specified for Specify If |
|
The maximum number of days to retain a version. The specified value must be greater than or equal to A decimal value can be used to specify a fraction of a day. For example, Specify If |
|
The default directory object used when files are added to a file group if no directory is specified when the files are added, and no default directory object is specified for the version. If |
|
If If |
|
Comments about the file group. If non- If |
|
If If |
If min_versions
is set to 1
, then the only version of the file group can be purged when a new version is added. If the addition of the new version is not complete when the existing version is purged, then there can be a period of time when no version of the file group is available. Therefore, set min_versions
to at least 2
if a version of the file group must be available at all times.
This procedure alters a version of a file group.
DBMS_FILE_GROUP.ALTER_VERSION( file_group_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, new_version_name IN VARCHAR2 DEFAULT NULL, remove_version_name IN VARCHAR2 DEFAULT 'N', new_default_directory IN VARCHAR2 DEFAULT NULL, remove_default_directory IN VARCHAR2 DEFAULT 'N', new_comments IN VARCHAR2 DEFAULT NULL, remove_comments IN VARCHAR2 DEFAULT 'N');
Table 41-6 ALTER_VERSION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group that contains the version, specified as |
|
The name of the version being altered. If a positive integer is specified as a If If |
|
The new name of the version. Do not specify a schema. The specified version name cannot be a positive integer or an asterisk ( If |
|
If If |
|
The default directory object used when files are added to a version if no directory is specified when the files are added. If |
|
If If |
|
Comments about the version. If non- If |
|
If If |
This procedure creates a file group.
DBMS_FILE_GROUP.CREATE_FILE_GROUP( file_group_name IN VARCHAR2, keep_files IN VARCHAR2 DEFAULT 'Y', min_versions IN NUMBER DEFAULT 2, max_versions IN NUMBER DEFAULT DBMS_FILE_GROUP.INFINITE, retention_days IN NUMBER DEFAULT DBMS_FILE_GROUP.INFINITE, default_directory IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 41-7 CREATE_FILE_GROUP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group, specified as |
|
If If Note: If the file group is dropped as a result of a |
|
The minimum number of versions to retain. The specified value must be greater than or equal to |
|
The maximum number of versions to retain. The specified value must be greater than or equal to the value specified for Specify |
|
The maximum number of days to retain a version. The specified value must be greater than or equal to A decimal value can be used to specify a fraction of a day. For example, Specify |
|
The default directory object used when files are added to a file group if no directory is specified when the files are added, and no default directory object is specified for the version. |
|
Comments about the file group being created. |
If min_versions
is set to 1
, then the only version of the file group can be purged when a new version is added. If the addition of the new version is not complete when the existing version is purged, then there can be a period of time when no version of the file group is available. Therefore, set min_versions
to at least 2
if a version of the file group must be available at all times.
This procedure creates a version of a file group.
This procedure automatically runs the PURGE_FILE_GROUP
procedure. Therefore, versions can be purged based on the file group's retention policy.
This procedure is overloaded. One version of the procedure contains the OUT
parameter version_out
, and the other does not.
See Also:
"PURGE_FILE_GROUP Procedure"DBMS_FILE_GROUP.CREATE_VERSION( file_group_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, default_directory IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL); DBMS_FILE_GROUP.CREATE_VERSION( file_group_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, default_directory IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL, version_out OUT VARCHAR2);
Table 41-8 CREATE_VERSION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group to which the new version is added, specified as |
|
The name of the version being created. Do not specify a schema. The specified version name cannot be a positive integer because, when a version is created, a version number is generated automatically. The specified version name cannot be an asterisk ( |
|
The default directory object used when files are added to a version if no directory is specified when the files are added. |
|
Comments about the version being created |
|
If the If the |
This procedure drops a file group.
DBMS_FILE_GROUP.DROP_FILE_GROUP( file_group_name IN VARCHAR2, keep_files IN VARCHAR2 DEFAULT NULL);
Table 41-9 DROP_FILE_GROUP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group being dropped, specified as |
|
If If If |
If this procedure deletes files on hard disk, then the user who runs the procedure must have WRITE
privilege on the directory object that contains the files.
This procedure drops a version of a file group.
DBMS_FILE_GROUP.DROP_VERSION( file_group_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, keep_files IN VARCHAR2 DEFAULT NULL);
Table 41-10 DROP_VERSION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group that contains the version, specified as |
|
The name of the version being dropped. If a positive integer is specified as a If If |
|
If If If |
If this procedure deletes files on hard disk, then the user who runs the procedure must have WRITE
privilege on the directory object that contains the files.
This procedure grants object privileges on a file group to a user.
DBMS_FILE_GROUP.GRANT_OBJECT_PRIVILEGE( object_name IN VARCHAR2, privilege IN BINARY_INTEGER, grantee IN VARCHAR2, grant_option IN BOOLEAN DEFAULT FALSE);
Table 41-11 GRANT_OBJECT_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group on which the privilege is granted, specified as |
|
The constant that specifies the privilege. See "Constants" for valid privileges. |
|
The name of the user or role for which the privilege is granted. The specified user cannot be the owner of the object. |
|
If If |
To run this procedure, a user must meet at least one of the following requirements:
Be the owner of the object on which the privilege is granted
Have the same privilege as the privilege being granted with the grant option
This procedure grants system privileges for file group operations to a user.
Note:
When you grant a privilege on"ANY"
object (for example, ALTER_ANY_RULE
), and the initialization parameter O7_DICTIONARY_ACCESSIBILITY
is set to FALSE
, you give the user access to that type of object in all schemas, except the SYS
schema. By default, the initialization parameter O7_DICTIONARY_ACCESSIBILITY
is set to FALSE
.
If you want to grant access to an object in the SYS
schema, then you can grant object privileges explicitly on the object. Alternatively, you can set the O7_DICTIONARY_ACCESSIBILITY
initialization parameter to TRUE
. Then privileges granted on "ANY"
object will allow access to any schema, including SYS
. Set the O7_DICTIONARY_ACCESSIBILITY
initialization parameter with caution.
DBMS_FILE_GROUP.GRANT_SYSTEM_PRIVILEGE( privilege IN BINARY_INTEGER, grantee IN VARCHAR2, grant_option IN BOOLEAN DEFAULT FALSE);
Table 41-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The constant that specifies the privilege. See "Constants" for valid privileges. |
|
The name of the user or role for which the privilege is granted. The user who runs the procedure cannot be specified. |
|
If If |
This procedure purges a file group using the file group's retention policy.
A file group's retention policy is determined by its settings for the max_versions
, min_versions
, and retention_days
parameters. The following versions of a file group are removed when a file group is purged:
All versions greater than the max_versions
setting for the file group when versions are ordered in descending order by creation time. Therefore, the older versions are purged before the newer versions.
All versions older than the retention_days
setting for the file group except when purging a version will cause the number of versions to drop below the min_versions
setting for the file group.
A job named SYS.FGR$AUTOPURGE_JOB
automatically purges all file groups in a database periodically according to the job's schedule. You can adjust this job's schedule using the DBMS_SCHEDULER
package. Alternatively, you can create a job that runs the PURGE_FILE_GROUP
procedure periodically.
DBMS_FILE_GROUP.PURGE_FILE_GROUP( file_group_name IN VARCHAR2);
Table 41-13 PURGE_FILE_GROUP Procedure Parameter
Parameter | Description |
---|---|
|
The name of the file group, specified as If |
If this procedure deletes files on hard disk, then the user who runs the procedure must have WRITE
privilege on the directory object that contains the files. Files are deleted when a version is purged and the keep_files
parameter is set to N
for the version's file group.
This procedure removes a file from a version of a file group.
DBMS_FILE_GROUP.REMOVE_FILE( file_group_name IN VARCHAR2, file_name IN VARCHAR2, version_name IN VARCHAR2 DEFAULT NULL, keep_file IN VARCHAR2 DEFAULT NULL);
Table 41-14 REMOVE_FILE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group that contains the version, specified as |
|
The name of the file being removed from the version |
|
The name of the version from which the file is removed. If a positive integer is specified as a If If |
|
If If If |
If this procedure deletes files on hard disk, then the user who runs the procedure must have WRITE
privilege on the directory object that contains the files.
This procedure revokes object privileges on a file group from a user.
DBMS_FILE_GROUP.REVOKE_OBJECT_PRIVILEGE( object_name IN VARCHAR2, privilege IN BINARY_INTEGER, revokee IN VARCHAR2);
Table 41-15 REVOKE_OBJECT_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the file group on which the privilege is revoked, specified as |
|
The constant that specifies the privilege. See "Constants" for valid privileges. |
|
The name of the user or role from which the privilege is revoked. The user who owns the object cannot be specified. |
This procedure revokes system privileges for file group operations from a user.
DBMS_FILE_GROUP.REVOKE_SYSTEM_PRIVILEGE( privilege IN BINARY_INTEGER, revokee IN VARCHAR2);
Table 41-16 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The constant that specifies the privilege. See "Constants" for valid privileges. |
|
The name of the user or role from which the privilege is revoked. The user who runs the procedure cannot be specified. |