Oracle | DBMS_FILE

Oracle DBMS_FILE_GROUP Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_INVOK	DBMS_STREAMS_RPC
DBMS_FILE_GROUP_DECL	DBMS_LOGREP_UTIL	DBMS_STREAMS_TABLESPACE_ADM
DBMS_FILE_GROUP_EXP	DBMS_LOGREP_UTIL_INVOK	DBMS_STREAMS_TBS_INT
DBMS_FILE_GROUP_IMP	DBMS_PRIV_CAPTURE	DBMS_STREAMS_TBS_INT_INVOK
DBMS_FILE_GROUP_INTERNAL_INVOK	DBMS_STANDARD	KU$_JOBDESC
DBMS_FILE_GROUP_LIB	DBMS_STREAMS_ADM_UTL	UTL_FILE
DBMS_FILE_GROUP_UTL

Documented Yes: Packages and Types Reference

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmsfgr.sql

Subprograms

ADD_FILE	CREATE_VERSION	PURGE_FILE_GROUP
ALTER_FILE	DROP_FILE_GROUP	REMOVE_FILE
ALTER_FILE_GROUP	DROP_VERSION	REVOKE_OBJECT_PRIVILEGE
ALTER_VERSION	GRANT_OBJECT_PRIVILEGE	REVOKE_SYSTEM_PRIVILEGE
CREATE_FILE_GROUP	GRANT_SYSTEM_PRIVILEGE

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