Oracle | DBMS_DBFS

Oracle DBMS_DBFS_HS 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 This package is an API service provider, underneath DBMS_DBFS_CONTENT that enables use of tape or Amazon S3 web service as store for data. The data on tape (or Amazon S3 web service) is part of the Oracle Database and can be accessed through all standard APIs, but only via the database. DBMS_DBFS_HS package provides users the ability to use tape (or Amazon S3 web service) as a storage tier when doing Information Lifecycle Management of their content.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
createStore Methods
STORETYPE_AMAZONS3	VARCHAR2(50)	'HS_S3'
STORETYPE_TAPE	VARCHAR2(50)	'HS_TAPE'
setStoreProperty Properties
PROPNAME_BUCKET	VARCHAR2(50)	'BUCKET'
PROPNAME_CACHESIZE	VARCHAR2(50)	'CACHE_SIZE'
PROPNAME_COMPRESSLEVEL	VARCHAR2(50)	'COMPRESSION_LEVEL'
PROPVAL_COMPLVL_HIGH	VARCHAR2(50)	'HIGH'
PROPNAME_HTTPPROXY	VARCHAR2(50)	'HTTP_PROXY'
PROPNAME_LICENSEID	VARCHAR2(50)	'LICENSE_ID'
PROPNAME_LOBCACHE_QUOTA	VARCHAR2(50)	'LOBCACHE_QUOTA'
PROPVAL_COMPLVL_LOW	VARCHAR2(50)	LOW'
PROPNAME_MEDIAPOOL	VARCHAR2(50)	'MEDIA_POOL'
PROPVAL_COMPLVL_MEDIUM	VARCHAR2(50)	'MEDIUM'
PROPVAL_COMPLVL_NONE	VARCHAR2(50)	'NONE'
PROPNAME_OPTTARBALLSIZE	VARCHAR2(50)	'OPTIMAL_TARBALL_SIZE'
PROPNAME_READCHUNKSIZE	VARCHAR2(50)	'READ_CHUNK_SIZE'
PROPNAME_SBTLIBRARY	VARCHAR2(50)	'SBT_LIBRARY'
PROPNAME_S3HOST	VARCHAR2(50)	'S3_HOST'
PROPNAME_WALLET	VARCHAR2(50)	'WALLET'
PROPNAME_ALIAS	VARCHAR2(50)	'WALLET_ALIAS'
PROPNAME_WRITECHUNKSIZE	VARCHAR2(50)	'WRITE_CHUNK_SIZE'
LRU timestamp update frequency
PROPNAME_LRUTS_UPD_FREQ	VARCHAR2(50)	'LRUTS_UPD_FREQ'
PROPVAL_CHUNKSIZE	VARCHAR2(50)	'1048576'
PROPVAL_MAXBF_S3	NUMBER	3154728
Miscellaneous
PROPNAME_ENABLECLEANUPONDELETE	VARCHAR2(50)	'ENABLE_CLEANUP_ON_DELETE'
PROPNAME_MAX_BACKUPFILE_SIZE	VARCHAR2(50)	'MAX_BACKUPFILE_SIZE'
PROPNAME_STORE_TYPE	VARCHAR2(50)	'STORE_TYPE'
PROPNAME_STREAMABLE	VARCHAR2(50)	'STREAMABLE'
FAIL	NUMBER	0
SUCCESS	NUMBER	1
ERROR	NUMBER	2

Data Types CREATE OR REPLACE TYPE dbms_dbfs_hs_item_t AUTHID DEFINER AS OBJECT ( storename VARCHAR2(32), storeowner VARCHAR2(32), path VARCHAR2(1024), contentfilename VARCHAR2(1024)); / CREATE OR REPLACE TYPE dbms_dbfs_hs_litems_t AS TABLE OF dbms_dbfs_hs_item_t; /

Dependencies

DBFS_HS$_FS	DBMS_DBFS_CONTENT_LIST_ITEM_T	DBMS_SCHEDULER
DBMS_APBACKEND	DBMS_DBFS_CONTENT_PROPERTIES_T	DBMS_STANDARD
DBMS_APBACKEND_LIB	DBMS_DBFS_CONTENT_RAW_T	DBMS_SYSTEM
DBMS_ARCH_PROVIDER_INTL	DBMS_DBFS_HS_ITEM_T	PLITBLM
DBMS_ASSERT	DBMS_DBFS_HS_LITEMS_T	USER_DBFS_HS
DBMS_DBFS_CONTENT	DBMS_DBFS_SFS	USER_DBFS_HS_FILES
DBMS_DBFS_CONTENT_CONTEXT_T	DBMS_LOB	UTL_RAW
DBMS_DBFS_CONTENT_LIST_ITEMS_T	DBMS_LOCK

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-20200	Store with the given name was not found
ORA-20201	A required store property was not found
ORA-20202	Specified staging area tablespace not found
ORA-20203	Make sure that stagingarea is clean by calling DBMS_DBFS_HS.STOREPUSH
ORA-20204	The Policy Context passed in is invalid
ORA-20205	STORETYPE is not one of the types defined by DBMS_DBFS_HS
ORA-20206	CACHESIZE provided to DBMS_DBFS_HS store is insufficient
ORA-20207	String is longer than allowed length
ORA-20208	condition 0 < lobCacheQuota < 1 violated
ORA-20209	COMPRESSION_LEVEL StoreProperty has an invalid value
ORA-20210	Cause: Data has been corrupted
ORA-20211	LOB cache is too small for the larger working set
ORA-20212	File not found
ORA-64007	Invalid store specified

First Available 11.2

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

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

also review: {ORACLE_HOME}/rdbms/admin/catapt.sql

Subprograms

CHECKACCESS	FLUSHCACHE	PUTPATH
CLEANUPUNUSEDBACKUPFILES	GETFEATURES	RECONFIGCACHE
CREATEBUCKET	GETPATH	REGISTERSTORECOMMAND
CREATEDIRECTORY	GETPATHBYSTOREID	RENAMEPATH
CREATEFILE	GETPATHNOWAIT	RESTOREALL
CREATELINK	GETSTOREID	RESTOREPATH
CREATEREFERENCE	GETSTOREPROPERTY	SEARCH
CREATESTORE	GETVERSION	SENDCOMMAND
DELETECONTENT	LIST	SETPATH
DELETEDIRECTORY	LISTCONTENTFILENAME	SETSTOREPROPERTY
DELETEFILE	LOCKPATH	SPACEUSAGE
DEREGSTORECOMMAND	PURGEALL	STOREPUSH
DROPSTORE	PURGEPATH

CHECKACCESS

Undocumented dbms_dbfs_hs.checkAccess( store_name IN VARCHAR2, path IN VARCHAR2, pathtype IN INTEGER, operation IN VARCHAR2, principal IN VARCHAR2) RETURN INTEGER;

TBD

CLEANUPUNUSEDBACKUPFILES

Removes files created on the external storage device that hold no currently used data dbms_dbfs_hs.cleanUpUnusedBackupFiles(store_name IN VARCHAR2);

exec dbms_dbfs_hs.cleanUpUnusedBackupFiles('UWStore');

CREATEBUCKET

Creates an AWS bucket, associated with a store of type STORETYPE_AMAZONS3 into which the Archive provider can move data dbms_dbfs_hs.createBucket(store_name IN VARCHAR2);

exec dbms_dbfs_hs.createBucket('UWStore');

CREATEDIRECTORY

Undocumented dbms_dbfs_hs.createDirectory( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, prop_flags IN INTEGER, recurse IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

CREATEFILE

Undocumented dbms_dbfs_hs.createFile( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, content IN OUT NOCOPY BLOB, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

CREATELINK

Undocumented dbms_dbfs_hs.createLink( store_name IN VARCHAR2, srcPath IN VARCHAR2, dstPath IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

CREATEREFERENCE

Undocumented dbms_dbfs_hs.createReference( store_name IN VARCHAR2, srcPath IN VARCHAR2, dstPath IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

CREATESTORE

Creates a new HS store in the schema under the ownership of the invoking session user."tbl_name" in tablespace "tbs_space" is a placeholder of the store content cached in database. A "cache_size" worth of space will be used by the store to cache content in "tbs_name". dbms_dbfs_hs.createStore( store_name IN VARCHAR2, store_type IN VARCHAR2, tbl_name IN VARCHAR2, tbs_name IN VARCHAR2, cache_size IN NUMBER, lob_cache_quota IN NUMBER DEFAULT NULL, optimal_tarball_size IN NUMBER DEFAULT NULL, schema_name IN VARCHAR2 DEFAULT NULL);

exec dbms_dbfs_hs.createStore('UWStore', dbms_dbfs_hs.STORETYPE_AMAZONS3, 'UWStoreTable', 'UWDATA', 1000000);

DELETECONTENT

Undocumented dbms_dbfs_hs.deleteContent( store_name IN VARCHAR2, contentID IN RAW, filter IN VARCHAR2, soft_delete IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

DELETEDIRECTORY

Undocumented dbms_dbfs_hs.deleteDirectory( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, soft_delete IN INTEGER, recurse IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

DELETEFILE

Undocumented dbms_dbfs_hs.deleteFile( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, soft_delete IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

DEREGSTORECOMMAND

Deregister a store message dbms_dbfs_hs.deregStoreCommand( store_name IN VARCHAR2, message IN VARCHAR2);

TBD

DROPSTORE

Drops the previously created "store_name" under the ownership of the invoking session_user dbms_dbfs_hs.dropStore( store_name IN VARCHAR2, opt_flags =IN INTEGER DEFAULT 0); -- alt. opt_flag = AP_DISABLE_CLEANUPBACKUPFILES CONSTANT INTEGER := 1;

col storename format a15

      col storeowner format a15

      

      SELECT * FROM sys.dbfs_hs$_storeidtable;

      

      BEGIN

        dbms_dbfs_hs.dropStore('UWSTORE', 1);

      END;

      /

FLUSHCACHE

Flushes out dirty contents from level-1 cache and truncates all the lockable cache contents dbms_dbfs_hs.flushCache(store_name IN VARCHAR2);

exec dbms_dbfs_hs.flushCache('UWStore');

GETFEATURES

Undocumented dbms_dbfs_hs.getFeatures(store_name IN VARCHAR2) RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := dbms_dbfs_hs.getFeatures('UWStore');

        dbms_output.put_line(TO_CHAR(retVal));

      END;

      /

GETPATH

Undocumented

Overload 1 dbms_dbfs_hs.getPath( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, content OUT BLOB, item_type OUT INTEGER, prop_flags IN INTEGER, forUpdate IN INTEGER, deref in IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

Overload 2 dbms_dbfs_hs.getPath( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, amount IN OUT NUMBER, offset IN NUMBER, buffer OUT RAW, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

Overload 3 dbms_dbfs_hs.getPath( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, amount IN OUT NUMBER, offset IN NUMBER, buffers OUT dbms_dbfs_content_raw_t, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

GETPATHBYSTOREID

Undocumented dbms_dbfs_hs.getPathByStoreID( store_name IN VARCHAR2, guid IN INTEGER) RETURN VARCHAR2;

TBD

GETPATHNOWAIT

Undocumented dbms_dbfs_hs.getPathNoWait( store_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, content OUT NOCOPY BLOB, item_type OUT INTEGER, prop_flags IN INTEGER, deref IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

GETSTOREID

Undocumented dbms_dbfs_hs.getStoreID(store_name IN VARCHAR2) RETURN NUMBER;

DECLARE

       sid NUMBER;

      BEGIN

        sid := dbms_dbfs_hs.getStoreID('UWStore');

        dbms_output.put_line(TO_CHAR(sid));

      END;

      /

GETSTOREPROPERTY

Retrieves the values of a property, identified by PropertyName, of a store from the database. If NoExcp is false, an exception is raised if the property does not exist. If noexcp is true, null is returned if the property does not exist. dbms_dbfs_hs.getStoreProperty( store_name IN VARCHAR2, property_name IN VARCHAR2, noexcp IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;

TBD

GETVERSION

Undocumented dbms_dbfs_hs.getVersion(store_name IN VARCHAR2) RETURN VARCHAR2;

DECLARE

       sVer VARCHAR2(30);

      BEGIN

        sVer := dbms_dbfs_hs.getVersion('UWStore');

        dbms_output.put_line(sVer);

      END;

      /

      1.0.0

      

      PL/SQL procedure successfully completed.

LIST

Undocumented dbms_dbfs_hs.list( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, recurse IN INTEGER, ctx IN dbms_dbfs_content_context_t) RETURN dbms_dbfs_content_list_items_t PIPELINED;

TBD

LISTCONTENTFILENAME

Lists all the content file names across all the stores owned by the current session user dbms_dbfs_hs.listContentFileName RETURN dbms_dbfs_hs_litems_t PIPELINED;

SELECT * FROM TABLE(dbms_dbfs_hs.listContentFileName);

      

      no rows selected

      

      -- code sample from $ORACLE_HOME/rdbms/admin/depspspi.sql

      CREATE OR REPLACE FORCE VIEW USER_DBFS_HS_FILES(PATH, SEQUENCENUMBER, STARTOFFSET, ENDOFFSET, TARBALLID, BACKUPFILENAME, TARSTARTOFFSET, TARENDOFFSET) AS

      SELECT APT.path, SF.SequenceNumber, SF.StartOffset, SF.EndOffset, SF.TarballId, BF.BackupFileName, BF.TarStartOffset, BF.TarEndOffset

      FROM DBFS_HS$_SFLocatorTable SF, DBFS_HS$_ContentFnMapTbl MP, DBFS_HS$_StoreId2PolicyCtx PC, DBFS_HS$_BackupFileTable BF, TABLE(dbms_dbfs_hs.listcontentfilename) APT

      WHERE MP.ArchiveRefId = SF.ArchiveRefId

      AND PC.StoreId = BF.StoreId

      AND BF.TarballId = SF.TarballId

      AND APT.contentfilename = MP.ContentFilename

      /

LOCKPATH

Undocumented dbms_dbfs_hs.lockPath( store_name IN VARCHAR2, path IN VARCHAR2, lock_type IN INTEGER, lock_data IN VARCHAR2, ctx IN dbms_dbfs_content_context_t);

TBD

PURGEALL

Undocumented dbms_dbfs_hs.purgeAll( repos_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, ctx IN dbms_dbfs_content_context_t);

TBD

PURGEPATH

Undocumented dbms_dbfs_hs.purgePath( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, ctx IN dbms_dbfs_content_context_t);

TBD

PUTPATH

Undocumented

Overload 1 dbms_dbfs_hs.putPath( repos_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, content IN OUT NOCOPY BLOB, item_type OUT INTEGER, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

Overload 2 dbms_dbfs_hs.putPath( repos_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, amount IN NUMBER, offset IN NUMBER, buffers OUT RAW, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

Overload 3 dbms_dbfs_hs.putPath( repos_name IN VARCHAR2, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, written OUT NUMBER, offset IN NUMBER, buffers IN dbms_dbfs_content_raw_t, prop_flags IN INTEGER, ctx IN dbms_dbfs_content_context_t);

TBD

RECONFIGCACHE

Registers commands for a store with the Archive Provider to be sent to the Media Manager for the external storage device associated with the repository dbms_dbfs_hs.reconfigCache( repos_name IN VARCHAR2, cache_size IN NUMBER DEFAULT NULL, lob_cache_quota IN NUMBER DEFAULT NULL, optimal_tarball_size IN NUMBER DEFAULT NULL);

TBD

REGISTERSTORECOMMAND

To begin pushing data to or getting data from an external store, first begin an API session (to talk to the store), and after beginning the session, send all registered messages to the store before writing data dbms_dbfs_hs.registerReposCommand( repos_name IN VARCHAR2, message IN VARCHAR2, flags IN NUMBER); BEFORE_PUT CONSTANT NUMBER := 1; BEFORE_GET CONSTANT NUMBER := 2;

TBD

RENAMEPATH

Undocumented dbms_dbfs_hs.renamePath( store_name IN VARCHAR2, oldPath IN VARCHAR2, newPath IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, ctx IN dbms_dbfs_content_context_t);

TBD

RESTOREALL

Undocumented dbms_dbfs_hs.restoreAll( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, ctx IN dbms_dbfscontent_context_t);

TBD

RESTOREPATH

Undocumented dbms_dbfs_hs.restorePath( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, ctx IN dbms_content_context_t);

TBD

Undocumented dbms_dbfs_hs.search( store_name IN VARCHAR2, path IN VARCHAR2, filter IN VARCHAR2, recurse IN INTEGER, ctx IN dbms_dbfs_content_context_t) RETURN dbms_dbfs_content_list_items_t PIPELINED;

TBD

SENDCOMMAND

Sends a command to be executed on the external storage device's Media Manager dbms_dbfs_hs.sendCommand( store_name IN VARCHAR2, message IN VARCHAR2);

TBD

SETPATH

Undocumented dbms_dbfs_hs.setPath( store_name IN VARCHAR2, contentID IN RAW, path IN VARCHAR2, properties IN OUT NOCOPY dbms_dbfs_content_properties_t, ctx IN dbms_dbfs_content_context_t);

TBD

SETSTOREPROPERTY

Stores store properties (as name, value pairs) in the database dbms_dbfs_hs.setReposProperty( store_name IN VARCHAR2, property_name IN VARCHAR2, property_value IN VARCHAR2);

TBD

SPACEUSAGE

Undocumented dbms_dbfs_hs.spaceUsage( store_name IN VARCHAR2, blksize OUT INTEGER, tbytes OUT INTEGER, fbytes OUT INTEGER, nfile OUT INTEGER, ndir OUT INTEGER, nlink OUT INTEGER, nref OUT INTEGER);

DECLARE

       bsize  INTEGER;

       tbytes INTEGER;

       fbytes INTEGER;

       nfile  INTEGER;

       ndir   INTEGER;

       nlink  INTEGER;

       nref   INTEGER;

      BEGIN

        dbms_dbfs_hs.spaceUsage('UWStore', bsize, tbytes, fbytes, nfile, ndir, nlink, nref);

        dbms_output.put_line(TO_CHAR(bsize));

        dbms_output.put_line(TO_CHAR(tbytes));

        dbms_output.put_line(TO_CHAR(fbytes));

        dbms_output.put_line(TO_CHAR(nfile));

        dbms_output.put_line(TO_CHAR(ndir));

        dbms_output.put_line(TO_CHAR(nlink));

        dbms_output.put_line(TO_CHAR(nref));

      END;

      /

STOREPUSH

Pushes locally staged data to a remote store dbms_dbfs_hs.storePush( store_name IN VARCHAR2, path IN VARCHAR2 DEFAULT NULL);

TBD