;
Oracle DBMS_FILE_GROUP
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose APIs for building and managing a File Group repository
AUTHID CURRENT_USER
Constants
Name Data Type Value
General
INFINITE NUMBER 4294967295
File Types
DATAFILE VARCHAR2(30) 'DATAFILE'
DATAPUMP_LOG_FILE VARCHAR2(30) 'DATAPUMPLOG'
EXPORT_DUMP_FILE VARCHAR2(30) 'DUMPSET'
System Privileges
MANAGE_FILE_GROUP BINARY_INTEGER 1
MANAGE_ANY_FILE_GROUP BINARY_INTEGER 2
READ_ANY_FILE_GROUP BINARY_INTEGER 3
Object Privileges
MANAGE_ON_FILE_GROUP BINARY_INTEGER 1
READ_ON_FILE_GROUP BINARY_INTEGER 2
Dependencies
DBMS_APPLY_ADM DBMS_FILE_GROUP_UTL DBMS_STREAMS_RPC
DBMS_FILE_GROUP_DECL DBMS_FILE_GROUP_UTL_INVOK DBMS_STREAMS_TABLESPACE_ADM
DBMS_FILE_GROUP_EXP DBMS_LOGREP_UTIL DBMS_STREAMS_TBS_INT_INVOK
DBMS_FILE_GROUP_IMP DBMS_STANDARD KU$_JOBDESC
DBMS_FILE_GROUP_INTERNAL_INVOK DBMS_STREAMS_ADM_UTL UTL_FILE
DBMS_FILE_GROUP_LIB    
Documented Yes
First Available 10.1.0.4
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsfgr.sql
Subprograms
 
ADD_FILE
Adds a file to a version of a file group dbms_file_group.add_file(
file_group_name IN VARCHAR2,
file_name       IN VARCHAR2,
file_type       IN VARCHAR2 DEFAULT NULL,
file_directory  IN VARCHAR2 DEFAULT NULL,
version_name    IN VARCHAR2 DEFAULT NULL,
comments        IN VARCHAR2 DEFAULT NULL);
TBD
 
ALTER_FILE
Alters a file in a version of a file group dbms_file_group.alter_file(
file_group_name    IN VARCHAR2,
file_name          IN VARCHAR2,
version_name       IN VARCHAR2 DEFAULT NULL,
new_file_name      IN VARCHAR2 DEFAULT NULL,
new_file_directory IN VARCHAR2 DEFAULT NULL,
new_file_type      IN VARCHAR2 DEFAULT NULL,
remove_file_type   IN VARCHAR2 DEFAULT 'N',
new_comments       IN VARCHAR2 DEFAULT NULL,
remove_comments    IN VARCHAR2 DEFAULT 'N');
BEGIN
  dbms_file_group.alter_file(
    file_group_name => 'sh.grp_rptrepos',
    file_name => 'TSP_SFH.DBF',
    version_name => 'salesforce_v3',
    new_file_name => 'TSP_SFH01.DBF',
    new_comments => 'Datafile for Tablespace TSF_SFH');
END;
/
 
ALTER_FILE_GROUP
Alters a file group dbms_file_group.alter_file_group(
file_group_name          IN VARCHAR2,
keep_files               IN VARCHAR2 DEFAULT NULL,
min_versions             IN NUMBER   DEFAULT NULL,
max_versions             IN NUMBER   DEFAULT NULL,
retention_days           IN NUMBER   DEFAULT NULL,
new_default_directory    IN VARCHAR2 DEFAULT NULL,
remove_default_directory IN VARCHAR2 DEFAULT 'N',
new_comments             IN VARCHAR2 DEFAULT NULL,
remove_comments          IN VARCHAR2 DEFAULT 'N');
BEGIN
  dbms_file_group.alter_file_group(
    file_group_name => 'sh.grp_rptrepos',
    max_versions => 10,
    retention_days => 30,
    new_comments => 'Tablespace Repository (10 versions, kept 30 days)');
END;
/
 
ALTER_VERSION
Alters a version of a file group dbms_file_group.alter_version(
file_group_name          IN VARCHAR2,
version_name             IN VARCHAR2 DEFAULT NULL,
new_version_name         IN VARCHAR2 DEFAULT NULL,
remove_version_name      IN VARCHAR2 DEFAULT 'N',
new_default_directory    IN VARCHAR2 DEFAULT NULL,
remove_default_directory IN VARCHAR2 DEFAULT 'N',
new_comments             IN VARCHAR2 DEFAULT NULL,
remove_comments          IN VARCHAR2 DEFAULT 'N');
BEGIN
  dbms_file_group.alter_version(
    file_group_name => 'sh.grp_rptrepos',
    version_name => 'salesforce_v3',
    new_version_name => 'salesforce_v0',
    new_comments => 'Sales Force Tablespace Version 0 (was 3)');
END;
/
 
CREATE_FILE_GROUP
Creates a file group dbms_file_group.create_file_group(
file_group_name   IN VARCHAR2,
keep_files        IN VARCHAR2 DEFAULT 'Y',
min_versions      IN NUMBER   DEFAULT 2,
max_versions      IN NUMBER   DEFAULT DBMS_FILE_GROUP.INFINITE,
retention_days    IN NUMBER   DEFAULT DBMS_FILE_GROUP.INFINITE,
default_directory IN VARCHAR2 DEFAULT NULL,
comments          IN VARCHAR2 DEFAULT NULL);
TBD
 
CREATE_VERSION
Creates a version of a file group

Overload 1
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);
TBD
Overload 2 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);
TBD
 
DROP_FILE_GROUP
Drops a file group dbms_file_group.drop_file_group(
file_group_name IN VARCHAR2,
keep_files      IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_file_group.drop_file_group(file_group_name => 'sh.grp_rptrepos', keep_files => 'N');
END;
/
 
DROP_VERSION
Drops a version of a file group dbms_file_group.drop_version(
file_group_name IN VARCHAR2,
version_name    IN VARCHAR2 DEFAULT NULL,
keep_files      IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_file_group.drop_version(
    file_group_name => 'sh.grp_rptrepos',
    version_name => 'salesforce_v1',
    keep_files => 'Y');
END;
/
 
GRANT_OBJECT_PRIVILEGE
Grants object privileges on a file group to a user dbms_file_group.grant_object_privilege(
object_name  IN VARCHAR2,
privilege    IN BINARY_INTEGER,
grantee      IN VARCHAR2,
grant_option IN BOOLEAN DEFAULT FALSE);
TBD
 
GRANT_SYSTEM_PRIVILEGE
Grants system privileges for file group operations to a user dbms_file_group.grant_system_privilege(
privilege    IN BINARY_INTEGER,
grantee      IN VARCHAR2,
grant_option IN BOOLEAN DEFAULT FALSE);
BEGIN
  dbms_file_group.grant_system_privilege(dbms_file_group.manage_file_group, 'DBA', TRUE);
END;
/
 
PURGE_FILE_GROUP
Purges a file group using the file group's retention policy dbms_file_group.purge_file_group(file_group_name IN VARCHAR2);
BEGIN
  dbms_file_group.purge_file_group('sh.grp_rptrepos');
END;
/
 
REMOVE_FILE
Removes a file from a version of a file group dbms_file_group.remove_file(
file_group_name IN VARCHAR2,
file_name       IN VARCHAR2,
version_name    IN VARCHAR2 DEFAULT NULL,
keep_file       IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_file_group.remove_file(
    file_group_name => 'sh.grp_rptrepos',
    file_name => 'LMT_SFH.DBF',
    version_name => 'salesforce_v1',
    keep_file => 'N');
END;
/
 
REVOKE_OBJECT_PRIVILEGE
Revokes object privileges on a file group from a user dbms_file_group.revoke_object_privilege(
object_name IN VARCHAR2,
privilege   IN BINARY_INTEGER,
revokee     IN VARCHAR2);
TBD
 
REVOKE_SYSTEM_PRIVILEGE
Revokes system privileges for file group operations from a user dbms_file_group.revoke_system_privilege(
privilege IN BINARY_INTEGER,
revokee   IN VARCHAR2);
TBD

Related Topics
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_TABLESPACE_ADM
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved