66 DBMS_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. You can use this package to create and manage file group repositories.

This chapter contains the following topics:

• Overview

• Security Model

• Constants

• Examples

• Summary of DBMS_FILE_GROUP Subprograms

66.1 DBMS_FILE_GROUP Overview

It is helpful to understand terminology before using the DBMS_FILE_GROUP package.

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 data files 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 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.

See Also:

Oracle Streams Concepts and Administration

66.2 DBMS_FILE_GROUP Security Model

There are two ways to define control on the DBMS_FILE_GROUP package.

• Granting EXECUTE on this package to selected users or roles.

• Granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

66.3 DBMS_FILE_GROUP Constants

The DBMS_FILE_GROUP package defines several enumerated constants for specifying parameter values. Enumerated constants must be prefixed with the package name. For example, DBMS_FILE_GROUP.EXPORT_DUMP_FILE.

The following table lists the parameters and enumerated constants.

Table 66-1 DBMS_FILE_GROUP Parameters with Enumerated Constants

Parameter	Option	Type	Description
file_type new_file_type	DATAFILE EXPORT_DUMP_FILE DATAPUMP_LOG_FILE	VARCHAR2(30)	DATAFILE is a datafile for a database. This constant can be specified as 'DATAFILE'. EXPORT_DUMP_FILE is a Data Pump export dump file. This constant can be specified as 'DUMPSET'. DATAPUMP_LOG_FILE is a Data Pump export log file. This constant can be specified as 'DATAPUMPLOG'.
max_versions retention_days	INFINITE	NUMBER	INFINITE specifies no limit. The max_versions or retention_days can increase without reaching a limit.
privilege	System privilege specified in the GRANT_SYSTEM_PRIVILEGE procedure: READ_ANY_FILE_GROUP MANAGE_ANY_FILE_GROUP MANAGE_FILE_GROUP Object privilege specified in the GRANT_OBJECT_PRIVILEGE procedure: READ_ON_FILE_GROUP MANAGE_ON_FILE_GROUP	BINARY_INTEGER	READ_ANY_FILE_GROUP grants the privilege to view information about any file group in any schema in the data dictionary. MANAGE_ANY_FILE_GROUP grants the privilege to create, manage, and drop any file group in any schema. MANAGE_FILE_GROUP grants the privilege to create, manage, and drop file groups in the user's schema. READ_ON_FILE_GROUP grants the privilege to view information about a specific file group in the data dictionary. MANAGE_ON_FILE_GROUP grants the privilege to manage a specific file group in a schema other than the user's schema.

66.4 DBMS_FILE_GROUP Examples

DBMS_FILE_GROUP examples are in another book.

Oracle Streams Concepts and Administration includes an example of a business that sells books and music over the internet. The business runs weekly reports on the sales data in the inst1.example.com database and stores these reports in two HTML files on a computer file system. The book_sales.htm file contains the report for book sales, and the music_sales.htm file contains the report for music sales.

The business wants to store these weekly reports in a file group repository at the inst2.example.com remote database. Every week, the two reports are generated on the inst1.example.com database, transferred to the computer system running the inst2.example.com database, and added to the repository as a file group version. The file group repository stores all of the file group versions that contain the reports for each week.

66.5 Summary of DBMS_FILE_GROUP Subprograms

This table lists the DBMS_FILE_GROUP subprograms and briefly describes them.

Table 66-2 DBMS_FILE_GROUP Package Subprograms

Subprogram	Description
ADD_FILE Procedure	Adds a file to a version of a file group
ALTER_FILE Procedure	Alters a file in a version of a file group
ALTER_FILE_GROUP Procedure	Alters a file group
ALTER_VERSION Procedure	Alters a version of a file group
CREATE_FILE_GROUP Procedure	Creates a file group
CREATE_VERSION Procedure	Creates a version of a file group
DROP_FILE_GROUP Procedure	Drops a file group
DROP_VERSION Procedure	Drops a version of a file group
GRANT_OBJECT_PRIVILEGE Procedure	Grants object privileges on a file group to a user
GRANT_SYSTEM_PRIVILEGE Procedure	Grants system privileges for file group operations to a user
PURGE_FILE_GROUP Procedure	Purges a file group using the file group's retention policy
REMOVE_FILE Procedure	Removes a file from a version of a file group
REVOKE_OBJECT_PRIVILEGE Procedure	Revokes object privileges on a file group from a user
REVOKE_SYSTEM_PRIVILEGE Procedure	Revokes system privileges for file group operations from a user

Note:

All subprograms commit unless specified otherwise.

66.5.1 ADD_FILE Procedure

This procedure adds a file to a version of a file group.

See Also:

An example that uses this procedure for a file group repository in Oracle Streams Concepts and Administration

Syntax

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);

Parameters

Table 66-3 ADD_FILE Procedure Parameters

Parameter	Description
file_group_name	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: 'DUMPSET' Data Pump metadata is populated when a Data Pump export dump file is imported. 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 either enter a text description of the file type, or specify NULL to omit a file type description. See "Constants" for more information about the reserved file types.
file_directory	The name of the directory object that corresponds to the directory containing the file. If NULL, then the procedure uses the default directory object for the version. If NULL and no default directory object exists for the version, then the procedure uses the default directory object for the file group. If NULL and no default directory object exists for the version or file group, then the procedure raises an error.
version_name	The name of the version to which the file is added. 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 added to version 1 of the file group. If NULL, then the procedure uses the version with the latest creation time for the file group.
comments	Comments about the file being added

Usage Notes

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 privileges

66.5.2 ALTER_FILE Procedure

This procedure alters a file in a version of a file group.

Syntax

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');

Parameters

Table 66-4 ALTER_FILE Procedure Parameters

Parameter	Description
file_group_name	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

See Also:

Oracle Database Utilities for more information about Data Pump privileges

66.5.3 ALTER_FILE_GROUP Procedure

This procedure alters a file group.

Syntax

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');

Parameters

Table 66-5 ALTER_FILE_GROUP Procedure Parameters

Parameter	Description
file_group_name	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 because of a DROP USER CASCADE 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 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.

66.5.4 ALTER_VERSION Procedure

This procedure alters a version of a file group.

Syntax

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');

Parameters

Table 66-6 ALTER_VERSION Procedure Parameters

Parameter	Description
file_group_name	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.

66.5.5 CREATE_FILE_GROUP Procedure

This procedure creates a file group.

See Also:

An example that uses this procedure for a file group repository in Oracle Streams Concepts and Administration

Syntax

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);

Parameters

Table 66-7 CREATE_FILE_GROUP Procedure Parameters

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.
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 because of a DROP USER CASCADE 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 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.

66.5.6 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.

See Also:

• PURGE_FILE_GROUP Procedure

• An example that uses this procedure for a file group repository in Oracle Streams Concepts and Administration

Syntax

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);

Parameters

Table 66-8 CREATE_VERSION Procedure Parameters

Parameter	Description
file_group_name	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.

66.5.7 DROP_FILE_GROUP Procedure

This procedure drops a file group.

Syntax

DBMS_FILE_GROUP.DROP_FILE_GROUP(
  file_group_name  IN  VARCHAR2,
  keep_files       IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 66-9 DROP_FILE_GROUP Procedure Parameters

Parameter	Description
file_group_name	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.

66.5.8 DROP_VERSION Procedure

This procedure drops a version of a file group.

Syntax

DBMS_FILE_GROUP.DROP_VERSION( 
  file_group_name  IN  VARCHAR2, 
  version_name     IN  VARCHAR2 DEFAULT NULL,
  keep_files       IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 66-10 DROP_VERSION Procedure Parameters

Parameter	Description
file_group_name	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.

66.5.9 GRANT_OBJECT_PRIVILEGE Procedure

This procedure grants object privileges on a file group to a user.

Syntax

DBMS_FILE_GROUP.GRANT_OBJECT_PRIVILEGE(
  object_name   IN  VARCHAR2,
  privilege     IN  BINARY_INTEGER,
  grantee       IN  VARCHAR2,
  grant_option  IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 66-11 GRANT_OBJECT_PRIVILEGE Procedure Parameters

Parameter	Description
object_name	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

66.5.10 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 allows access to any schema, including SYS. Set the O7_DICTIONARY_ACCESSIBILITY initialization parameter with caution.

Syntax

DBMS_FILE_GROUP.GRANT_SYSTEM_PRIVILEGE(
  privilege     IN  BINARY_INTEGER,
  grantee       IN  VARCHAR2,
  grant_option  IN  BOOLEAN  DEFAULT FALSE);

Parameters

Table 66-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters

Parameter	Description
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 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.

66.5.11 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 unless purging a version would 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 66-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.

66.5.12 REMOVE_FILE Procedure

This procedure removes a file from a version of a file group.

Syntax

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);

Parameters

Table 66-14 REMOVE_FILE Procedure Parameters

Parameter	Description
file_group_name	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.

66.5.13 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 66-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.

66.5.14 REVOKE_SYSTEM_PRIVILEGE Procedure

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

Syntax

DBMS_FILE_GROUP.REVOKE_SYSTEM_PRIVILEGE(
  privilege  IN  BINARY_INTEGER,
  revokee    IN  VARCHAR2);

Parameters

Table 66-16 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters

Parameter	Description
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 runs the procedure cannot be specified.