Oracle Database File System (DBFS) PL/SQL APIs

In a previous article I discussed the Database File System (DBFS) introduced in Oracle 11g Release 2. This article provides an overview of the PL/SQL APIs available for managing and interacting with the DBFS. Instead of trying to give a lengthy explanation of each package, some of which are lacking documentation, I'll work through some examples of common tasks you may wish to perform using the PL/SQL APIs.

Test Schema
Managing File Systems
Basic File Operations
Displaying File System Information
DBMS_FUSE

Test Schema

The examples in this article require a test tablespace and schema. If you have already created them when working through the previous article ignore this section.

First we must create a tablespace to hold the file system.

CONN / AS SYSDBA

CREATE TABLESPACE dbfs_ts
  DATAFILE '/u01/app/oracle/oradata/DB11G/dbfs01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

Next, we create a user, grant DBFS_ROLE to the user and make sure it has a quota on the tablespace. Trying to create a file system from the SYS user fails, so it must be done via another user.

CONN / AS SYSDBA

CREATE USER dbfs_user IDENTIFIED BY dbfs_user
  DEFAULT TABLESPACE dbfs_ts QUOTA UNLIMITED ON dbfs_ts;

GRANT CONNECT, CREATE TABLE TO dbfs_user;
GRANT dbfs_role TO dbfs_user;

Managing File Systems

In a previous article about DBFS I discussed creating a new file system using the "dbfs_create_filesystem.sql" and "dbfs_create_filesystem_advanced.sql" scripts. The "dbfs_create_filesystem.sql" script simply calls the "dbfs_create_filesystem_advanced.sql" script with the majority of the parameters defaulted. If you look inside the "dbfs_create_filesystem_advanced.sql" script you will see it actually uses a number of PL/SQL APIs. If you rarely need to create or drop file systems, then the scripts are fine. If you need to perform programmatic maintenance, then you will need to know some of the basic API calls.

The CREATEFILESYSTEM procedure in the DBMS_DBFS_SFS package is used to create a new file system. The use of SecureFiles as a storage type allows the file system to use deduplication, encryption and compression. Inclusion of this functionality, as well as partitioning is controlled by a variety of parameters, most of which have default values. The DBMS_DBFS_CONTENT package is then used to register and mount the store. The following code shows how to create a new file system similar to that produced by the "dbfs_create_filesystem.sql" script.

CONN dbfs_user/dbfs_user

DECLARE
  l_volume      VARCHAR2(30) := 'etl_staging';
  l_store_name  VARCHAR2(30) := UPPER('FS_' || l_volume);
  l_table_name  VARCHAR2(30) := UPPER('T_' || l_volume);
  l_provider    VARCHAR2(30) := 'my_provider';
  l_schema      VARCHAR2(30) := 'DBFS_USER';
  l_ts_name     VARCHAR2(30) := 'DBFS_TS';
  l_return      INTEGER;
BEGIN
  DBMS_DBFS_SFS.createfilesystem(
    store_name    => l_store_name,
    schema_name   => l_schema,
    tbl_name      => l_table_name,
    tbl_tbs       => l_ts_name,
    do_partition  => TRUE);

  DBMS_DBFS_CONTENT.registerstore(
    store_name       => l_store_name,
    provider_name    => l_provider,
    provider_package => 'DBMS_DBFS_SFS');

  DBMS_DBFS_CONTENT.mountstore(
    store_name    => l_store_name,
    store_mount   => l_volume);

  -- This line is possibly Linux only.
  l_return := DBMS_FUSE.fs_chmod('/' || l_volume, 16895);

  COMMIT;
END;
/

If you have a Linux system that is configured for mounting with FUSE you can now mount and use the file system.

$ # Mount the file system
$ nohup dbfs_client -o wallet /@DB11G_DBFS_USER /mnt/dbfs &
[1] 4560
nohup: appending output to `nohup.out'
$ 
[1]+  Done                    nohup dbfs_client -o wallet /@DB11G_DBFS_USER /mnt/dbfs
$


$ ls -al /mnt/dbfs
total 8
drwxr-xr-x 4 root root    0 Jan  8 17:10 .
drwxr-xr-x 3 root root 4096 Jan  6 14:18 ..
drwxrwxrwx 3 root root    0 Jan  8 17:09 etl_staging
drwxrwxrwx 4 root root    0 Jan  7 11:41 staging_area
$ ls -al /mnt/dbfs/etl_staging
total 0
drwxrwxrwx 3 root root 0 Jan  8 17:09 .
drwxr-xr-x 4 root root 0 Jan  8 17:10 ..
drwxr-xr-x 7 root root 0 Jan  8 17:09 .sfs
$

The INITFS procedure initializes the specified file system by truncating the supporting table and recreating the root directory. It allows you to quickly clear a whole file system.

EXEC DBMS_DBFS_SFS.INITFS (store_name => 'FS_ETL_STAGING');

To remove a file system, do the reverse of the creation process, unmount, unregister and drop the file system.

DECLARE
  l_volume      VARCHAR2(30) := 'etl_staging';
  l_store_name  VARCHAR2(30) := UPPER('FS_' || l_volume);
BEGIN
  DBMS_DBFS_CONTENT.unmountstore(
    store_name  => l_store_name,
    store_mount => l_volume);

  DBMS_DBFS_CONTENT.unregisterstore(store_name => l_store_name);

  DBMS_DBFS_SFS.dropfilesystem(store_name => l_store_name);
  COMMIT;
END;
/

Basic File Operations

From PL/SQL, interactions with the database file system are done using the DBMS_DBFS_CONTENT package. The the majority of the operations are self explanatory and in some cases there are multiple ways of achieving the same outcome. In this section I will show a few of the possible operations to give you a feel for the API.

Create a Directory

DECLARE
  l_props DBMS_DBFS_CONTENT.PROPERTIES_T;
BEGIN
  DBMS_DBFS_CONTENT.createDirectory (
    path       => '/etl_staging/api_test',
    properties => l_props);
END;
/

SELECT pathname, pathtype
FROM   dbfs_content
WHERE  pathname LIKE '%api_test%';

PATHNAME                                 PATHTYPE
---------------------------------------- --------------------------------
/etl_staging/api_test                    directory

SQL>

Rename a File or Directory

DECLARE
  l_props DBMS_DBFS_CONTENT.PROPERTIES_T;
BEGIN
  DBMS_DBFS_CONTENT.renamePath (
    oldpath    => '/etl_staging/api_test',
    newpath    => '/etl_staging/api_test_dir',
    properties => l_props);
END;
/

SELECT pathname, pathtype
FROM   dbfs_content
WHERE  pathname LIKE '%api_test%';

PATHNAME                                 PATHTYPE
---------------------------------------- --------------------------------
/etl_staging/api_test_dir                directory

SQL>

Delete a Directory

BEGIN
  DBMS_DBFS_CONTENT.deleteDirectory (
    path    => '/etl_staging/api_test_dir',
    recurse => TRUE);
END;
/

SELECT pathname, pathtype
FROM   dbfs_content
WHERE  pathname LIKE '%api_test%';

no rows selected

SQL>

Create a File

A new file can be created with an already populated BLOB, or with a NULL BLOB that is subsequently written to.

DECLARE
  l_props  DBMS_DBFS_CONTENT.PROPERTIES_T;
  l_blob   BLOB;
  l_buffer VARCHAR2(32767);
BEGIN
  l_buffer := 'This is a test.';

  l_blob := UTL_RAW.cast_to_raw(l_buffer);

  DBMS_DBFS_CONTENT.createFile (
    path       => '/etl_staging/test1.txt',
    properties => l_props,
    content    => l_blob);

  l_blob := NULL;

  DBMS_DBFS_CONTENT.createFile (
    path       => '/etl_staging/test2.txt',
    properties => l_props,
    content    => l_blob);

  DBMS_LOB.writeappend(l_blob, LENGTH(l_buffer), UTL_RAW.cast_to_raw(l_buffer));
  COMMIT; -- Needed to release resources when writeappend is used.
END;
/

COLUMN pathname FORMAT A30
COLUMN filedata FORMAT A30

SELECT pathname,
       UTL_RAW.cast_to_varchar2(filedata) AS filedata
FROM   dbfs_content
WHERE  pathname LIKE '/etl_staging/test%.txt';

PATHNAME                       FILEDATA
------------------------------ ------------------------------
/etl_staging/test2.txt         This is a test.
/etl_staging/test1.txt         This is a test.

SQL>

Retrieve File Contents

The previous examples have shown how the file contents can be queried using the DBFS_CONTENT view. The file contents can also be retrieved using the PL/SQL API.

SET SERVEROUTPUT ON
DECLARE
  l_props     DBMS_DBFS_CONTENT.PROPERTIES_T;
  l_blob      BLOB;
  l_item_type INTEGER;
BEGIN
  DBMS_DBFS_CONTENT.getPath (
    path       => '/etl_staging/test1.txt',
    properties => l_props,
    content    => l_blob,
    item_type  => l_item_type);

  DBMS_OUTPUT.put_line(UTL_RAW.cast_to_varchar2(l_blob));
END;
/
This is a test.

PL/SQL procedure successfully completed.

SQL>

Delete a File

BEGIN
  DBMS_DBFS_CONTENT.deleteFile (path => '/etl_staging/test1.txt');

  DBMS_DBFS_CONTENT.deleteFile (path => '/etl_staging/test2.txt');
END;
/

SELECT pathname,
       UTL_RAW.cast_to_varchar2(filedata) AS filedata
FROM   dbfs_content
WHERE  pathname LIKE '/etl_staging/test%.txt';

no rows selected

SQL>

Displaying File System Information

The DBMS_DBFS_SFS package provides some information for file system administration. Information about the contents of the file system should only ever be retrieved using the DBMS_DBFS_CONTENT package and the DBFS_CONTENT and DBFS_CONTENT_PROPERTIES views. The package contains many list* pipelined table function and get* functions for retrieving information. Some examples are shown below.

-- DBMS_DBFS_CONTENT_SFS
SELECT * FROM TABLE(dbms_dbfs_sfs.listTables);
SELECT * FROM TABLE(dbms_dbfs_sfs.listFilesystems);
SELECT * FROM TABLE(dbms_dbfs_sfs.listVolumes);
SELECT * FROM TABLE(dbms_dbfs_sfs.listSnapshots);
 
-- DBMS_DBFS_CONTENT
SELECT * FROM TABLE(dbms_dbfs_content.liststores);
SELECT * FROM TABLE(dbms_dbfs_content.listmounts);
SELECT * FROM TABLE(dbms_dbfs_content.listallcontent);
SELECT * FROM TABLE(dbms_dbfs_content.listallproperties);
SELECT * FROM TABLE(dbms_dbfs_content.list(path => '/', recurse => 1, store_name => 'FS_ETL_STAGING'));

 -- Views
SELECT UTL_RAW.cast_to_varchar2(filedata) AS filedata
FROM   dbfs_content
WHERE  pathtype = 'file';

SELECT * FROM dbfs_content_properties;

DBMS_FUSE

The DBMS_FUSE package is a little mysterious as there is virtually no documentation associated with it. The name implies it relates to the Linux FUSE project, so maybe this is only available on Linux servers. It can be used to perform many of the basic file system operations, with all paths are relative to the base mount point.

DECLARE
  l_return INTEGER;
  l_buffer VARCHAR2(32767);
  l_blob   BLOB;
BEGIN
  l_buffer := 'This is a test.';
 
  -- Create a directory.
  --l_return := DBMS_FUSE.fs_mkdir('/etl_staging/fuse_test');

  -- Create a file.
  l_return := DBMS_FUSE.fs_creat('/etl_staging/fuse_test/test.txt', content => l_blob);
  DBMS_LOB.writeappend(l_blob, LENGTH(l_buffer), UTL_RAW.cast_to_raw(l_buffer));
  COMMIT; -- Needed to release resources when writeappend is used.
END;
/

SELECT UTL_RAW.cast_to_varchar2(filedata) AS filedata
FROM   dbfs_content
WHERE  pathname = '/etl_staging/fuse_test/test.txt';

FILEDATA
--------------------------------------------------------------------------------
This is a test.

SQL> HOST cat /mnt/dbfs/etl_staging/fuse_test/test.txt
This is a test.
SQL>

The following code removes the test file and the test directory.

DECLARE
  l_return INTEGER;
BEGIN
  l_return := DBMS_FUSE.fs_unlink('/etl_staging/fuse_test/test.txt');
  l_return := DBMS_FUSE.fs_rmdir('/etl_staging/fuse_test');
END;
/

If you are planning to mount a database file system using FUSE, you must remember to set the ownership and permissions of files and directories using the FS_CHOWN and FS_CHMOD functions.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.