Oracle DBMS_FILE_GROUP
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_LIB DBMS_STREAMS_RPC
DBMS_FILE_GROUP_DECL DBMS_FILE_GROUP_UTL DBMS_STREAMS_TABLESPACE_ADM
DBMS_FILE_GROUP_EXP DBMS_FILE_GROUP_UTL_INVOK DBMS_STREAMS_TBS_INT_INVOK
DBMS_FILE_GROUP_IMP DBMS_LOGREP_UTIL KU$_JOBDESC
DBMS_FILE_GROUP_INTERNAL_INVOK DBMS_STREAMS_ADM_UTL UTL_FILE
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

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-2014 Daniel A. Morgan All Rights Reserved