64 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. This package can be used to create and manage file group repositories.

This chapter contains the following topics:

Using DBMS_FILE_GROUP
- Overview
- Constants
Summary of DBMS_FILE_GROUP Subprograms

Using DBMS_FILE_GROUP

This section contains topics which relate to using the DBMS_FILE_GROUP package.

Overview
Constants

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.

Table 64-1 lists the parameters and enumerated constants.

Table 64-1 DBMS_FILE_GROUP Parameters with Enumerated Constants

Parameter Option Type Description

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.

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.

Summary of DBMS_FILE_GROUP Subprograms

Table 64-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.

ADD_FILE Procedure

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

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 64-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

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 64-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

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 64-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 as a result 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 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.

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

CREATE_FILE_GROUP Procedure

This procedure creates a file group.

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 64-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 as a result 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 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

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"

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

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 64-9 DROP_FILE_GROUP Procedure Parameters

Parameter Description

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.

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.

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

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 64-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

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.

Syntax

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

Parameters

Table 64-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.

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 64-13 PURGE_FILE_GROUP Procedure Parameter

Parameter Description

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.

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.

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

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

Syntax

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

Parameters

Table 64-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.