Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

41 DBMS_FILE_GROUP

The DBMS_FILE_GROUP package, one of a set of Streams packages, provides administrative interfaces for managing file groups, file group versions, and files. A file group repository is a collection of all of the file groups in a database and can contain multiple versions of a particular file group. This package can be used to create and manage file group repositories.

See Also:

Oracle Streams Concepts and Administration

This chapter contains the following topics:


Using DBMS_FILE_GROUP

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


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:

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, specify DBMS_FILE_GROUP.EXPORT_DUMP_FILE for an export dump file.

Table 41-1 DBMS_FILE_GROUP Parameters with Enumerated Constants

Parameter Enumerated Constant Type Description
file_type

new_file_type

  • DATAFILE
  • EXPORT_DUMP_FILE

  • DATAPUMP_LOG_FILE

VARCHAR2(30) DATAFILE is a data file 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 41-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 41-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:

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 41-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:

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

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.

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


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 41-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 41-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:


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 41-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:

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


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