9 DBFS Hierarchical Store

This section discusses hierarchical store wallet management, and provides specifics of the RDMS_DBFS_HS PL/SQL package.

This section contains the following topics:

Wallet Management

The command-line utility mkstore creates wallets and adds aliases for the secret store. Use the following commands to create and manage wallets:

Creating wallet:

mkstore -wrl wallet_location -create

Adding KEY alias. Specify the access_key and secret_key by enclosing it within single quotes.

mkstore -wrl wallet_location -createCredential alias 'access_key' 'secret_key'

For example:

mkstore -wrl /home/user1/mywallet -createCredential mykey 'abc' 'xyz'

Deleting KEY alias:

mkstore -wrl wallet_location -deleteCredential alias

For example:

mkstore -wrl /home/user1/mywallet -deleteCredential mykey

See Also:

Managing Storage with DBMS_DBFS_HS

The Oracle Database File System Hierarchical Store package (DBMS_DBFS_HS) is a store provider for DBMS_DBFS_CONTENT that supports hierarchical storage for DBFS content. The DBFS Hierarchical Store (DBFS HS) package stores content in external storage devices like tape or the Amazon S3 web service and associated metadata (or properties) in the database. The DBFS HS may cache frequently accessed content in database table(s) to improve performance.

The DBMS_DBFS_HS package can be used in conjunction with the DBMS_DBFS_CONTENT package to implement Hierarchical Storage Management for SecureFiles LOBs utilizing DBFS Links. Using the package, less frequently used data is migrated to a cheaper external device like tape, achieving significant reduction in storage costs by using the more expensive database disk only for more frequently accessed data. The DBMS_DBFS_HS package can also be plugged in as a store provider into the DBMS_DBFS_CONTENT package to implement a tape file system, if the associated external storage device is tape, or a cloud file system, if the associated external storage device is the Amazon S3 storage service.

The DBMS_DBFS_HS package provides you the ability to use tape as a storage tier when doing Information Lifecycle Management (ILM) for database tables or content. The package also supports other forms of storage targets including Web Services like Amazon S3. This service enables users to store data in the database on tape and other forms of storage that were previously not supported by Oracle. The data on tape or Amazon S3 is part of the Oracle Database and can be accessed by all standard APIs, but only through the database.

DBMS_DBFS_HS provides the primitives for an Information Lifecycle Management solution. All the important primitives such as CREATE, PUT, GET, and DELETE, as defined by the DBMS_DBFS_CONTENT_SPI interface are implemented by the DBMS_DBFS_HS package.

DBMS_DBFS_HS implements the methods defined in DBMS_DBFS_CONTENT_SPI. It also has some additional interfaces needed to manage the external storage device and the cache associated with each store.

See Also:

Oracle Database PL/SQL Packages and Types Reference, for details of the DBMS_DBFS_HS Package

Constants for DBMS_DBFS_HS Package

See Oracle Database PL/SQL Packages and Types Reference for details of constants used by DBMS_DBFS_HS PL/SQL package

Methods of DBMS_DBFS_HS Package

Table 9-1 summarizes the methods of the DBMS_DBFS_HS PL/SQL package.

Table 9-1 Methods of the DBMS_DBFS_HS Pl/SQL Packages

Method Description

CREATESTORE()

Creates a DBFS HS store.

DROPSTORE()

Deletes a previously created DBFS HS store.

RECONFIGCACHE()

Reconfigures the parameters of the database cache used by the store.

SETSTOREPROPERTY()

Associates name/value properties with a registered Hierarchical Store.

GETSTOREPROPERTY()

Retrieves the values of a property of a store in the database.

CREATEBUCKET()

Creates an AWS bucket, for use with the STORETYPE_AMAZON3 store.

STOREPUSH()

Pushes locally cached data to an archive store.

CLEANUPUNUSEDBACKUPFILES()

Removes files that are created on the external storage device if they have no current content.

REGISTERSTORECOMMAND()

Registers commands (messages) for a store so they are sent to the Media Manager of an external storage device.

DEREGSTORECOMMAND()

Removes a command (message) that was associated with a store.

SENDCOMMAND()

Sends a command (message) to the Media Manager of an external storage device.


CREATESTORE()

This method enables users to create a new DBFS HS store named store_name of type store_type (STORETYPE_TAPE or STORETYPE_AMAZONS3) in schema schema_name (defaulting to the current schema) under the ownership of invoking session user.tbl_name in tablespace tbs_space that holds store entries in the database.cache_size amount of space to be used to cache the content.

Store names must be unique for an owner. But the same store names can be used for different stores owned by different owners.

Currently CREATESTORE() sets certain properties of the store to default values. The user can use the methods SETSTOREPROPERTY() and RECONFIGCACHE() to appropriately change the property values and to set other properties of the store.

See Oracle Database PL/SQL Packages and Types Reference for more information.

DROPSTORE()

This method deletes a previously created DBFS HS store with the name store_name and owned by the invoking session_user.

This method unregisters the store from the DBFS Content API, the DBMS_DBFS_CONTENT package. All files in the given store are deleted from the store (tape or Amazon S3 web service). The database table holding the store's entries in the database is also dropped by this method.

This method executes like a DDL (auto-commit before and after its execution).

See Oracle Database PL/SQL Packages and Types Reference for more information.

RECONFIGCACHE()

This procedure reconfigures the parameters of the database cache being used by the store.

The Hierarchical Store uses a level 1 cache and a level 2 cache. The level 1 cache subsumes most of the working set and the level 2 cache is used to perform bulk writes to the backend device.

The DBMS_DBFS_HS package optimistically tries to allocate more than one tarball's worth of size for level 2 cache to facilitate concurrency, though a minimum of one tarball size is necessary for level 2 cache.

The values for cumulative cache size and LOB cache quota determine allocation of space for the two caches. If values are not provided, a user might see an INSUFFICIENT CACHE exception. In that case, it is better to revise the cache parameters in order to have a working store.

If this subprogram successfully executes, its actions cannot be rolled back by the user. In that case, the user must call RECONFIGCACHE again with new or modified parameters.

See Oracle Database PL/SQL Packages and Types Reference for more information.

SETSTOREPROPERTY()

This method associates properties with a store registered with the Hierarchical Store. Each property is a name value pair.

See Oracle Database PL/SQL Packages and Types Reference for more information about this method, and Oracle Database PL/SQL Packages and Types Reference for details of the DBMS_DBFS_HS constants used by this method.

Note that the DBFS hierarchical store has the ability to store its files in compressed form. Compression can be enabled by means of the property PROPNAME_COMPRESSLVL. This property specifies the compression level to be used in compressing the files. It can be one of the following four allowed values: PROPVAL_COMPLVL_NONE indicaes no compression, PROPVAL_COMPLVL_LOW indicates LOW compression, PROPVAL_COMPLVL_MEDIUM indicates MEDIUM compression, and PROPVAL_COMPLVL_HIGH indicates HIGH compression.

In general, the compression level LOW is expected to have the best performance while still providing a good compression ratio. Compression level MEDIUM and compression level HIGH are expected to provide a significantly better compression ratio but compression time can be correspondingly longer. It is recommended to use NONE or LOW when write performance is critical such as when files in the DBFS HS store are updated frequently. If space is critical and the best possible compression ratio is desired, MEDIUM or HIGH should be used.

Note that files are compressed as they are paged out of the cache into the staging area (before they are subsequently pushed into the back end tape/ S3 storage). Therefore, compression also benefits by storing smaller files in the staging area and thereby effectively increasing the total available capacity of the staging area.

Regarding PROPNAME_ENABLECLEANUPONDELETE behavior, a job is created for each store by the DBMS_DBFS_HS to remove the unused files from the external storage. By default, the job is enabled for STORETYPE_AMAZONS3 and is disabled for STORETYPE_TAPE. If the ENABLECLEANUPONDELETE property is set to TRUE, the job is enabled; if the property is set to FALSE, the job is disabled. If enabled, the job runs at an interval of one hour by default. The DBMS_SCHEDULER package can be used to modify the schedule. The name of the job can be obtained by querying USER_DBFS_HS_FIXED_PROPERTIES for prop_name = 'DELJOB_NAME'.

GETSTOREPROPERTY()

This method retrieves the values of a property, identified by PropertyName, of a store in the database.

See Oracle Database PL/SQL Packages and Types Reference for more information.

CREATEBUCKET()

The S3 bucket, associated with a store of type STORETYPE_AMAZONS3, must exist when the DBFS HS tries to move content into that bucket.

One way of creating the S3 bucket is to use the DBMS_DBFS_HS.CREATEBUCKET method. The PROPNAME_BUCKET property of the store must be set before this method is called.

See Oracle Database PL/SQL Packages and Types Reference for more information.

STOREPUSH()

This pushes locally cached data to the archive store identified by storename.

See Oracle Database PL/SQL Packages and Types Reference for more information.

CLEANUPUNUSEDBACKUPFILES()

This method removes files created on the external storage device that have no currently used data (content) in them. This method can be executed periodically (perhaps once a week) to clear space on the external storage device. Asynchronously deleting content from the external storage device is useful because it has minimal impact on OLTP performance. Periodic scheduling can be implemented using the DBMS_SCHEDULER package.

See Oracle Database PL/SQL Packages and Types Reference for more information.

REGISTERSTORECOMMAND()

A client uses this method to register commands (messages) for a store with the DBFS HS to be sent to the Media Manager for the external storage device associated with the store. These commands are sent before the next read or write of content. When the DBFS HS wants to push or get data to or from the storage device, it begins an API session to talk to the device. After beginning the session, it sends all registered commands, for that particular device, to the device before writing or getting any data.

See Oracle Database PL/SQL Packages and Types Reference for more information.

DEREGSTORECOMMAND()

This method removes a command (message) that had been previously associated with a store through the REGISTERSTORECOMMAND.

See Oracle Database PL/SQL Packages and Types Reference for more information.

SENDCOMMAND()

This sends a command (message) to be executed on the Media Manager of the external storage device.

See Oracle Database PL/SQL Packages and Types Reference for more information.

User View for DBFS Hierarchical Store

This view for DBFS Hierarchical Store is available:

USER_DBFS_HS_FILES

This view shows the files archived by this user, and their location on the back end device.

See Also:

Oracle Database Reference, USER_DBFS_HS_FILES view

Examples Using DBMS_DBFS_HS

For you to be able to use package DBMS_DBFS_HS, you must be granted dbfs_role by the DBA.

Setting up the Store

To set up the store:

  1. Call createStore.

    DBMS_DBFS_HS.createStore( store_name, store_type, tbl_name, tbs_name, cache_size, lob_cache_quota, optimal_tarball_size, schema_name);
    
  2. Set mandatory and optional properties using the following interface:

    DBMS_DBFS_HS.setStoreProperty(StoreName, PropertyName, PropertyValue);
    

    For store_type = STORETYPE_TAPE, mandatory properties are:

    PROPNAME_DEVICELIBRARY, PROPNAME_MEDIAPOOL, PROPNAME_CACHESIZE.
     
    PROPNAME_CACHESIZE is already set by createStore. 
    

    You can change the value of PROPNAME_CACHESIZE using setStoreProperty.

    Optional properties are:

    PROPNAME_OPTTARBALLSIZE, PROPNAME_READCHUNKSIZE, PROPNAME_WRITECHUNKSIZE, PROPNAME_STREAMABLE.
    

    For store_type = STORETYPE_AMAZONS3 mandatory properties are:

    PROPNAME_DEVICELIBRARY, PROPNAME_CACHESIZE, PROPNAME_S3HOST,PROPNAME_BUCKET, PROPNAME_LICENSEID, PROPNAME_WALLET.
    

    PROPNAME_CACHESIZE is set by createStore. You can change the value of PROPNAME_CACHESIZE using setStoreProperty.

    Optional properties are:

    PROPNAME_OPTTARBALLSIZE, PROPNAME_READCHUNKSIZE, PROPNAME_WRITECHUNKSIZE, PROPNAME_STREAMABLE, PROPNAME_HTTPPROXY.
    
  3. Register the store with DBFS Content API using:

    DBMS_DBFS_CONTENT.registerStore(store_name, provider_name, provider_package);
    
  4. Mount the stores for access using:

    DBMS_DBFS_CONTENT.mountStore(store_name, store_mount, singleton,principal,   owner, acl, asof, read_only);
    

Using the Hierarchical Store

The Hierarchical Store can be used as an independent file system or as an archive solution for SecureFiles LOBs.

Using Hierarchical Store as a File System

Use the DBMS_DBFS_CONTENT package to create, update, read, and delete file system entries in the store.

Refer to the documentation of DBMS_DBFS_CONTENT for details.

Using Hierarchical Store as an Archive Solution For SecureFiles LOBs

Use the DBMS_LOB package to archive SecureFiles LOBs in Tape or S3 store.

Refer to the documentation of DBMS_LOB for details.

To free space in the cache or to force cache resident contents to be written to external storage device, call:

DBMS_DBFS_HS.storePush(store_name);

Dropping a Hierarchical Store

Call:

DBMS_DBFS_HS.dropStore(store_name, opt_flags);

Example: Using Amazon S3

The following example program configures and uses an Amazon S3 store.

Valid values must be substituted in some places, indicated by <...>, for the program to run successfully.

Please refer to DBMS_DBFS_HS documentation for complete details about the methods and their parameters.

Rem Example to configure and use an Amazon S3 store.
Rem
Rem hsuser should be a valid database user who has been granted
Rem the role dbfs_role.
 
connect hsuser/hsuser 
 
Rem The following block sets up a STORETYPE_AMAZONS3 store with
Rem DBMS_DBFS_HS acting as the store provider.
 
declare 
storename varchar2(32) ; 
tblname varchar2(30) ; 
tbsname varchar2(30) ; 
lob_cache_quota number := 0.8 ; 
cachesz number ; 
ots number ; 
begin 
cachesz := 50 * 1048576 ; 
ots := 1048576 ; 
storename := 's3store10' ; 
tblname := 's3tbl10' ; 
tbsname := '<TBS_3>' ; -- Substitute a valid tablespace name
 
-- Create the store.
-- Here tbsname is the tablespace used for the store,
-- tblname is the table holding all the store entities,
-- cachesz is the space used by the store to cache content
--   in the tablespace,
-- lob_cache_quota is the fraction of cachesz allocated
--   to level-1 cache and
-- ots is minimum amount of content that is accumulated
--   in level-2 cache before being stored in AmazonS3
dbms_dbfs_hs.createStore(
  storename,  
  dbms_dbfs_hs.STORETYPE_AMAZONS3,
  tblname, tbsname, cachesz,
  lob_cache_quota, ots) ; 
 
dbms_dbfs_hs.setstoreproperty(storename,
  dbms_dbfs_hs.PROPNAME_SBTLIBRARY,
  '<ORACLE_HOME/work/libosbws11.so>');
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_S3HOST,
  's3.amazonaws.com') ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_BUCKET,
  'oras3bucket10') ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_WALLET,
  'LOCATION=file:<ORACLE_HOME>/work/wlt CREDENTIAL_ALIAS=a_key') ;
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_LICENSEID,
  '<xxxxxxxxxxxxxxxx>') ; -- Substitute a valid SBT license id
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_HTTPPROXY,
  '<http://www-proxy.mycompany.com:80/>') ;
  -- Substitute valid value. If a proxy is not used,
  -- then this property need not be set.
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_COMPRESSLEVEL,
  'NONE') ; 
 
dbms_dbfs_hs.createbucket(storename) ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.registerstore(
  storename,
  's3prvder10',
  'dbms_dbfs_hs') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.mountstore(
  storename,
  's3mnt10') ; 
end ; 
/ 
 
Rem The following code block does file operations
Rem using DBMS_DBFS_CONTENT on the store configured
Rem in the previous code block
 
connect hsuser/hsuser 
 
declare 
path varchar2(256) ; 
path_pre varchar2(256) ; 
mount_point varchar2(32) ; 
store_name varchar2(32) ; 
prop1 dbms_dbfs_content_properties_t ; 
prop2 dbms_dbfs_content_properties_t ; 
mycontent blob := empty_blob() ; 
buffer varchar2(1050) ; 
rawbuf raw(1050) ; 
outcontent blob := empty_blob() ; 
itemtype integer ; 
pflag integer ; 
filecnt integer ; 
iter integer ; 
offset integer ; 
rawlen integer ; 
begin 
 
  mount_point := '/s3mnt10' ; 
  store_name := 's3store10' ; 
  path_pre := mount_point ||'/file' ; 
 
  -- We create 10 empty files in the following loop
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    mycontent := empty_blob() ; 
    prop1 := null ; 
 
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.createFile(
      path, prop1, mycontent) ; -- Create the file
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- We populate the newly created files with content
  -- in the following loop
  pflag := dbms_dbfs_content.prop_data +
           dbms_dbfs_content.prop_std  +
           dbms_dbfs_content.prop_opt  ; 
 
  buffer := 'Oracle provides an integrated management '  ||
            'solution for managing Oracle database with '||
            'a unique top-down application management '  ||
            'approach. With new self-managing '          ||
            'capabilities, Oracle eliminates time-'      ||
            'consuming, error-prone administrative '     ||
            'tasks, so database administrators can '     ||
            'focus on strategic business objectives '    ||
            'instead of performance and availability '   ||
            'fire drills. Oracle Management Packs for '  ||
            'Database provide signifiCant cost and time-'||
            'saving capabilities for managing Oracle '   ||
            'Databases. Independent studies demonstrate '||
            'that Oracle Database is 40 percent easier ' ||
            'to manage over DB2 and 38 percent over '    ||
            'SQL Server.'; 
 
  rawbuf := utl_raw.cast_to_raw(buffer) ; 
  rawlen := utl_raw.length(rawbuf) ; 
  offset := 1 ; 
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    prop1 := null;
  
    -- Append buffer to file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.putpath(
      path, prop1, rawlen,
      offset, rawbuf) ;
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Clear out level 1 cache
  dbms_dbfs_hs.flushCache(store_name) ; 
  commit ; 
 
  -- Do write operation on even-numbered files.
  -- Do read operation on odd-numbered files.
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10; 
    path := path_pre || to_char(filecnt) ; 
    if mod(filecnt, 2) = 0 then 
      -- Get writable file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype,
        pflag, null, true) ;
  
      buffer := 'Agile businesses want to be able to '    ||
                'quickly adopt new technologies, whether '||
                'operating systems, servers, or '         ||
                'software, to help them stay ahead of '   ||
                'the competition. However, change often ' ||
                'introduces a period of instability into '||
                'mission-critical IT systems. Oracle '    ||
                'Real Application Testing-with Oracle '   ||
                'Database 11g Enterprise Edition-allows ' ||
                'businesses to quickly adopt new '        ||
                'technologies while eliminating the '     ||
                'risks associated with change. Oracle '   ||
                'Real Application Testing combines a '    ||
                'workload capture and replay feature '    ||
                'with an SQL performance analyzer to '    ||
                'help you test changes against real-life '||
                'workloads, and then helps you fine-tune '||
                'the changes before putting them into'    ||
                'production. Oracle Real Application '    ||
                'Testing supports older versions of '     ||
                'Oracle Database, so customers running '  ||
                'Oracle Database 9i and Oracle Database ' ||
                '10g can use it to accelerate their '     ||
                'database upgrades. '; 
 
      rawbuf := utl_raw.cast_to_raw(buffer) ; 
      rawlen := utl_raw.length(rawbuf) ; 
 
      -- Modify file content
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_lob.write(outcontent, rawlen, 10, rawbuf);
      commit ; 
    else 
      -- Read the file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype, pflag) ;
    end if ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Delete the first 2 files
  filecnt := 0; 
 
  loop 
    exit when filecnt = 2 ; 
    path := path_pre || to_char(filecnt) ; 
    -- Delete file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.deleteFile(path) ;
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
 
  -- Move content staged in database to Amazon S3 store
  dbms_dbfs_hs.storePush(store_name) ; 
  commit ; 
 
end ; 
/

Example: Using Tape

The following example program configures and uses a tape store.

Valid values must be substituted in some places, indicated by <...>, for the program to run successfully.

Please refer to the DBMS_DBFS_HS documentation for complete details about the methods and their parameters.

Rem Example to configure and use a Tape store.
Rem
Rem hsuser should be a valid database user who has been granted
Rem the role dbfs_role.
 
connect hsuser/hsuser 
 
Rem The following block sets up a STORETYPE_TAPE store with
Rem DBMS_DBFS_HS acting as the store provider.
 
declare 
storename varchar2(32) ; 
tblname varchar2(30) ; 
tbsname varchar2(30) ; 
lob_cache_quota number := 0.8 ; 
cachesz number ; 
ots number ; 
begin 
cachesz := 50 * 1048576 ; 
ots := 1048576 ; 
storename := 'tapestore10' ; 
tblname := 'tapetbl10' ; 
tbsname := '<TBS_3>' ; -- Substitute a valid tablespace name
 
-- Create the store.
-- Here tbsname is the tablespace used for the store,
-- tblname is the table holding all the store entities,
-- cachesz is the space used by the store to cache content
--   in the tablespace,
-- lob_cache_quota is the fraction of cachesz allocated
--   to level-1 cache and
-- ots is minimum amount of content that is accumulated
--   in level-2 cache before being stored in AmazonS3
dbms_dbfs_hs.createStore(
  storename,
  dbms_dbfs_hs.STORETYPE_TAPE,
  tblname, tbsname, cachesz,
  lob_cache_quota, ots) ; 
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_SBTLIBRARY,
  '<ORACLE_HOME/work/libobkuniq.so>') ;
  -- Substitute your ORACLE_HOME path
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_MEDIAPOOL,
  '<0>') ;  -- Substitute valid value
 
dbms_dbfs_hs.setstoreproperty(
  storename,
  dbms_dbfs_hs.PROPNAME_COMPRESSLEVEL,
  'NONE') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.registerstore(
  storename,
  'tapeprvder10',
  'dbms_dbfs_hs') ; 
 
-- Please refer to DBMS_DBFS_CONTENT documentation
-- for details about this method
dbms_dbfs_content.mountstore(storename, 'tapemnt10') ; 
end ; 
/ 
 
Rem The following code block does file operations
Rem using DBMS_DBFS_CONTENT on the store configured
Rem in the previous code block
 
connect hsuser/hsuser 
 
declare 
  path varchar2(256) ; 
  path_pre varchar2(256) ; 
  mount_point varchar2(32) ; 
  store_name varchar2(32) ; 
  prop1 dbms_dbfs_content_properties_t ; 
  prop2 dbms_dbfs_content_properties_t ; 
  mycontent blob := empty_blob() ; 
  buffer varchar2(1050) ; 
  rawbuf raw(1050) ; 
  outcontent blob := empty_blob() ; 
  itemtype integer ; 
  pflag integer ; 
  filecnt integer ; 
  iter integer ; 
  offset integer ; 
  rawlen integer ; 
begin 
 
  mount_point := '/tapemnt10' ; 
  store_name := 'tapestore10' ; 
  path_pre := mount_point ||'/file' ; 
 
 
-- We create 10 empty files in the following loop
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    mycontent := empty_blob() ; 
    prop1 := null ; 
 
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.createFile(
      path, prop1, mycontent) ; -- Create the file
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- We populate the newly created files with content
  -- in the following loop
  pflag := dbms_dbfs_content.prop_data +
           dbms_dbfs_content.prop_std  +
           dbms_dbfs_content.prop_opt  ; 
 
  buffer := 'Oracle provides an integrated management '  ||
            'solution for managing Oracle database with '||
            'a unique top-down application management '  ||
            'approach. With new self-managing '          ||
            'capabilities, Oracle eliminates time-'      ||
            'consuming, error-prone administrative '     ||
            'tasks, so database administrators can '     ||
            'focus on strategic business objectives '    ||
            'instead of performance and availability '   ||
            'fire drills. Oracle Management Packs for '  ||
            'Database provide signifiCant cost and time-'||
            'saving capabilities for managing Oracle '   ||
            'Databases. Independent studies demonstrate '||
            'that Oracle Database is 40 percent easier ' ||
            'to manage over DB2 and 38 percent over '    ||
            'SQL Server.'; 
 
  rawbuf := utl_raw.cast_to_raw(buffer) ; 
  rawlen := utl_raw.length(rawbuf) ; 
  offset := 1 ; 
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10 ; 
    path := path_pre || to_char(filecnt) ; 
    prop1 := null;
  
    -- Append buffer to file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.putpath(
      path, prop1, rawlen,
      offset, rawbuf) ;
 
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Clear out level 1 cache
  dbms_dbfs_hs.flushCache(store_name) ; 
  commit ; 
 
  -- Do write operation on even-numbered files.
  -- Do read operation on odd-numbered files.
  filecnt := 0 ; 
  loop 
    exit when filecnt = 10; 
    path := path_pre || to_char(filecnt) ; 
    if mod(filecnt, 2) = 0 then 
      -- Get writable file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype,
        pflag, null, true) ;
  
      buffer := 'Agile businesses want to be able to '    ||
                'quickly adopt new technologies, whether '||
                'operating systems, servers, or '         ||
                'software, to help them stay ahead of '   ||
                'the competition. However, change often ' ||
                'introduces a period of instability into '||
                'mission-critical IT systems. Oracle '    ||
                'Real Application Testing-with Oracle '   ||
                'Database 11g Enterprise Edition-allows ' ||
                'businesses to quickly adopt new '        ||
                'technologies while eliminating the '     ||
                'risks associated with change. Oracle '   ||
                'Real Application Testing combines a '    ||
                'workload capture and replay feature '    ||
                'with an SQL performance analyzer to '    ||
                'help you test changes against real-life '||
                'workloads, and then helps you fine-tune '||
                'the changes before putting them into'    ||
                'production. Oracle Real Application '    ||
                'Testing supports older versions of '     ||
                'Oracle Database, so customers running '  ||
                'Oracle Database 9i and Oracle Database ' ||
                '10g can use it to accelerate their '     ||
                'database upgrades. '; 
 
      rawbuf := utl_raw.cast_to_raw(buffer) ; 
      rawlen := utl_raw.length(rawbuf) ; 
 
      -- Modify file content
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_lob.write(outcontent, rawlen, 10, rawbuf);
      commit ; 
    else 
      -- Read the file
      -- Please refer to DBMS_DBFS_CONTENT documentation
      -- for details about this method
      dbms_dbfs_content.getPath(
        path, prop2, outcontent, itemtype, pflag) ;
    end if ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
  -- Delete the first 2 files
  filecnt := 0; 
 
  loop 
    exit when filecnt = 2 ; 
    path := path_pre || to_char(filecnt) ; 
    -- Delete file
    -- Please refer to DBMS_DBFS_CONTENT documentation
    -- for details about this method
    dbms_dbfs_content.deleteFile(path) ;
    commit ; 
    filecnt := filecnt + 1 ; 
  end loop ; 
 
 
  -- Move content staged in database to Amazon S3 store
  dbms_dbfs_hs.storePush(store_name) ; 
  commit ; 
 
end ; 
/