Oracle | DBMS_FILE

Oracle DBMS_FILE_GROUP Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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