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.
Related articles.
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;
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; /
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.
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>
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>
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>
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>
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>
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>
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;
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 normal view: https://oracle-base.com/articles/11g/dbfs-apis-11gr2