55DBMS_FILE_GROUP

The DBMS_FILE_GROUP package, one of a set of Oracle 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.

Using DBMS_FILE_GROUP

Overview

The following terms pertain to the DBMS_FILE_GROUP package:

File

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.

Version

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.

File Group

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.

Constants

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, DBMS_FILE_GROUP.EXPORT_DUMP_FILE.

The name of the file group that contains the version, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

file_name

The name of the file being added to the version. Each file name in a version must be unique.

file_type

The file type. The following are reserved file types:

If the file is a datafile, then enter the following:

'DATAFILE'

If the file is a Data Pump export dump file, then enter the following:

The name of the file group that contains the version, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

file_name

The name of the file being altered in the version

version_name

The name of the version that contains the file being altered.

If a positive integer is specified as a VARCHAR2 value, then the integer is interpreted as a version number. For example, if '1' is specified, then the file in version 1 of the file group is altered.

If NULL, then the procedure uses the version with the latest creation time for the file group.

new_file_name

The new name of the file if the file name is being changed. Each file name in a version must be unique.

If NULL, then the procedure does not change the file name.

Note: When a non-NULL new file name is specified, this procedure changes the metadata for the file name in the data dictionary, but it does not change the file name on the hard disk.

new_file_directory

The new name of the directory object that corresponds to the directory containing the file, if the directory object is being changed.

If NULL, then the procedure does not change the directory object name.

Note: When a non-NULL new file directory is specified, this procedure changes the metadata for the file directory in the data dictionary, but it does not change the file directory on the hard disk.

new_file_type

The file type. The following are reserved file types:

If the file is a datafile, then enter the following:

'DATAFILE'

If the file is a Data Pump export dump file, then enter the following:

'DUMPSET'

If the file is a Data Pump export log file, then enter the following:

'DATAPUMPLOG'

If the file type is not one of the reserved file types, then enter a text description of the file type.

If NULL, then the procedure does not change the file type.

See Also:"Constants" for more information about the reserved file types.

remove_file_type

If Y, then the procedure removes the file type. If Y and the new_file_type parameter is non-NULL, then the procedure raises an error.

If N, then the procedure does not remove the file type.

new_comments

New comments about the file being altered. If non-NULL, then the procedure replaces the existing comments with the specified comments.

If NULL, then the procedure does not change the existing comments.

remove_comments

If Y, then the procedure removes the comments for the file. If Y and the new_comments parameter is non-NULL, then the procedure raises an error.

If N, then the procedure does not change the existing comments.

Usage Notes

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

The name of the file group being altered, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

keep_files

If Y, then the files in the file group are retained on hard disk if the file group or a version of the file group is dropped or purged.

If N, then the files in the file group are deleted from hard disk if the file group or a version of the file group is dropped or purged.

If NULL, then this parameter is not changed.

Note: If the file group is dropped as a result of a DROPUSERCASCADE statement, then the setting of this parameter determines whether the files are dropped from the hard disk.

min_versions

The minimum number of versions to retain. The specified value must be greater than or equal to 1.

If NULL, then the procedure does not change the min_versions setting for the file group.

max_versions

The maximum number of versions to retain. The specified value must be greater than or equal to the value specified for min_versions. When the number of versions exceeds the specified max_versions, the oldest version is purged.

Specify DBMS_FILE_GROUP.INFINITE for no limit to the number of versions.

If NULL, then the procedure does not change the max_versions setting for the file group.

retention_days

The maximum number of days to retain a version. The specified value must be greater than or equal to 0 (zero). When the age of a version exceeds the specified retention_days and there are more versions than the number specified in min_versions, the version is purged. The age of a version is calculated by subtracting the creation time from the current time.

A decimal value can be used to specify a fraction of a day. For example, 1.25 specifies one day and six hours.

Specify DBMS_FILE_GROUP.INFINITE for no limit to the number of days a version can exist.

If NULL, then the procedure does not change the retention_days setting for the file group.

new_default_directory

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 NULL, then the procedure does not change the default directory.

remove_default_directory

If Y, then the procedure removes the default directory for the file group. If Y and the new_default_directory parameter is set to a non-NULL value, then the procedure raises an error.

If N, then the procedure does not remove the default directory for the file group.

new_comments

Comments about the file group. If non-NULL, then the new comments replace the existing comments for the file group.

If NULL, then the procedure does not change the existing comments.

remove_comments

If Y, then the comments for the file group are removed. If Y and the new_comments parameter is set to a non-NULL value, then the procedure raises an error.

If N, then the procedure does not change the comments for the file group.

Usage Notes

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.

The name of the file group that contains the version, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

version_name

The name of the version being altered.

If a positive integer is specified as a VARCHAR2 value, then the integer is interpreted as a version number. For example, if '1' is specified, then version 1 of the file group is altered.

If '*' is specified, then the procedure alters all versions, and the new_version_name parameter must be NULL.

If NULL, then the procedure uses the version with the latest creation time for the file group.

new_version_name

The new name of the version. Do not specify a schema.

The specified version name cannot be a positive integer or an asterisk ('*').

If NULL, then the procedure does not change the version name.

remove_version_name

If Y, then the procedure removes the version name. If the version name is removed, then the version number must be used to manage the version. If Y and the new_version_name parameter is set to a non-NULL value, then the procedure raises an error.

If N, then the procedure does not remove the version name.

new_default_directory

The default directory object used when files are added to a version if no directory is specified when the files are added.

If NULL, then the procedure does not change the default directory.

remove_default_directory

If Y, then the procedure removes the default directory. If Y and the new_default_directory parameter is set to a non-NULL value, then the procedure raises an error.

If N, then the procedure does not remove the default directory.

new_comments

Comments about the version. If non-NULL, then the new comments replace the existing comments for the version.

If NULL, then the procedure does not change the comments.

remove_comments

If Y, then the procedure removes the comments for the version. If Y and the new_comments parameter is set to a non-NULL value, then the procedure raises an error.

If N, then the procedure does not remove the comments for the version.

The name of the file group, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

keep_files

If Y, then the files in the file group are retained on hard disk if the file group or a version of the file group is dropped or purged.

If N, then the files in the file group are deleted from hard disk if the file group or a version of the file group is dropped or purged.

Note: If the file group is dropped as a result of a DROPUSERCASCADE statement, then the setting of this parameter determines whether the files are dropped from the hard disk.

min_versions

The minimum number of versions to retain. The specified value must be greater than or equal to 1.

max_versions

The maximum number of versions to retain. The specified value must be greater than or equal to the value specified for min_versions. When the number of versions exceeds the specified max_versions, the oldest version is purged.

Specify DBMS_FILE_GROUP.INFINITE for no limit to the number of versions.

retention_days

The maximum number of days to retain a version. The specified value must be greater than or equal to 0 (zero). When the age of a version exceeds the specified retention_days and there are more versions than the number specified in min_versions, the version is purged. The age of a version is calculated by subtracting the creation time from the current time.

A decimal value can be used to specify a fraction of a day. For example, 1.25 specifies one day and six hours.

Specify DBMS_FILE_GROUP.INFINITE for no limit to the number of days a version can exist.

default_directory

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

Comments about the file group being created.

Usage Notes

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.

CREATE_VERSION Procedure

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.

The name of the file group to which the new version is added, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

version_name

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 ('*').

default_directory

The default directory object used when files are added to a version if no directory is specified when the files are added.

comments

Comments about the version being created

version_out

If the version_name parameter is set to a non-NULL value, then this parameter contains the specified version name.

If the version_name parameter is set to NULL, then this parameter contains the generated version number.

The name of the file group being dropped, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

keep_files

If Y, then the procedure retains the files in the file group on hard disk.

If N, then the procedure deletes the files in the file group from hard disk.

If NULL, then the procedure uses the default keep files property of the file group.

Usage Notes

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.

The name of the file group that contains the version, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

version_name

The name of the version being dropped.

If a positive integer is specified as a VARCHAR2 value, then the integer is interpreted as a version number. For example, if '1' is specified, then version 1 of the file group is dropped.

If NULL, then the procedure uses the version with the oldest creation time for the file group.

If '*', then the procedure drops all versions.

keep_files

If Y, then the procedure retains the files in the version on hard disk.

If N, then the procedure deletes the files in the version from hard disk.

If NULL, then the procedure uses the default keep files property of the file group.

Usage Notes

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.

The name of the file group on which the privilege is granted, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

privilege

The constant that specifies the privilege. See "Constants" for valid privileges.

grantee

The name of the user or role for which the privilege is granted. The specified user cannot be the owner of the object.

grant_option

If TRUE, then the specified user granted the specified privilege can grant this privilege to others.

If FALSE, then the specified user granted the specified privilege cannot grant this privilege to others.

Usage Notes

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

GRANT_SYSTEM_PRIVILEGE Procedure

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.

The constant that specifies the privilege. See "Constants" for valid privileges.

grantee

The name of the user or role for which the privilege is granted. The user who runs the procedure cannot be specified.

grant_option

If TRUE, then the specified user granted the specified privilege can grant this privilege to others.

If FALSE, then the specified user granted the specified privilege cannot grant this privilege to others.

PURGE_FILE_GROUP Procedure

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.

Syntax

DBMS_FILE_GROUP.PURGE_FILE_GROUP(
file_group_name IN VARCHAR2);

Parameter

Table 55-13 PURGE_FILE_GROUP Procedure Parameter

Parameter

Description

file_group_name

The name of the file group, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

If NULL and this procedure is run by SYS user, then the procedure purges all file groups.

Usage Notes

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.

The name of the file group that contains the version, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

file_name

The name of the file being removed from the version

version_name

The name of the version from which the file is removed.

If a positive integer is specified as a VARCHAR2 value, then the integer is interpreted as a version number. For example, if '1' is specified, then the file is removed from version 1 of the file group.

If NULL, then the procedure uses the version with the latest creation time for the file group.

If '*', then the procedure removes the file from all versions.

keep_file

If Y, then the procedure retains the file on hard disk.

If N, then the procedure deletes the file from hard disk.

If NULL, then the procedure uses the default keep files property of the file group.

Usage Notes

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.

REVOKE_OBJECT_PRIVILEGE Procedure

This procedure revokes object privileges on a file group from a user.

Syntax

DBMS_FILE_GROUP.REVOKE_OBJECT_PRIVILEGE(
object_name IN VARCHAR2,
privilege IN BINARY_INTEGER,
revokee IN VARCHAR2);

Parameters

Table 55-15 REVOKE_OBJECT_PRIVILEGE Procedure Parameters

Parameter

Description

object_name

The name of the file group on which the privilege is revoked, specified as [schema_name.]file_group_name. For example, if the schema is hq_dba and the file group name is sales_tbs, then specify hq_dba.sales_tbs. If the schema is not specified, then the current user is the default.

privilege

The constant that specifies the privilege. See "Constants" for valid privileges.

revokee

The name of the user or role from which the privilege is revoked. The user who owns the object cannot be specified.

REVOKE_SYSTEM_PRIVILEGE Procedure

This procedure revokes system privileges for file group operations from a user.