Securefiles LOB hands-on

Anonymous

Preamble

As a DBA I have always wondered why storing files inside the database. Users are never purging anything and DBAs are left alone managing the hundredth of Gigabytes tablespaces. The typical example is your enterprise portal where people post presentations and documents to share with others. In short I have always been a supporter of standards FileSystems to store files like documents and images…

Tom Kyte is providing a pretty exhaustive list of pros to have those files inside the database:

I know of no advantages to storing data I want to keep for a long time outside of a database.

If it is in the database I can

be sure it is professionally managed

backed up

recoverable (with the rest of the data)

secured

scalable (try putting 100,000 documents in a single directory, now, put them in table – which one ‘scales’ – it is not the directory)

I can undelete (flashback) easily

I have locking

I have read consistency

I honestly cannot think of an advantage to storing anything of importance in the filesystem.

I personally rate the key features are locking, read consistency and secured. In the example of an enterprise portal I think being able to see a file only when it has been checked out after an update is key for information consistence…

So then I have to cope with my bad mood and accept that storing file inside the database should be something common and even suggested as a security best practices…

For Oracle database it all started with LONG data type in release 7 and earlier then LOB (BLOB, CLOB, NCLOB) in Oracle 8 and later and in 11gR1 they introduced SecureFiles LOB:

Securefiles LOB improve performance and also supports compression, deduplication, and encryption features:

With no change to the application, SecureFiles outperformed BasicFiles in both reading (2x) and writing (3-4X) using Content DB.

For reference see this link.

It is also clearly stated that phasing out LONG data type is strongly advised for Oracle 8i and later:

LOB Capacity: LOBs can store much larger amounts of data. LOBs can store 4GB of data or more depending on you system configuration. LONG and LONG RAW types are limited to 2GB of data.
Number of LOB columns in a table: A table can have multiple LOB columns. LOB columns in a table can be of any LOB type. In Oracle Database Release 7.3 and higher, tables are limited to a single LONG or LONG RAW column.
Random piece-wise access: LOBs support random access to data, but LONGs support only sequential access.
LOBs can also be object attributes.

LOBs are implemented as external ones (BFILE External Binary File) but cannot be modified inside the application (read only) and we are back to original discussion or internal ones (BLOB datatype Binary Large Object, CLOB Character Large Object, NCLOB National Character Set Large Object).

To be honest if I had a look to this feature it is because it is part of OCM exam so worth a try… This blog post has been written using Oracle Linux Server release 6.4 and Oracle Database Enterprise Edition 11.2.0.3.

Basicfiles LOBs

To start I transfer to my database server a small picture (44KB) and I link it to a table using a BFILE column (read only). I used this small file:

Anonymous
Anonymous

As SYS I create a directory and grant read write to my own account:/

SQL> CREATE DIRECTORY pictures AS '/pictures';
 
DIRECTORY created.
 
SQL> GRANT read,WRITE ON DIRECTORY pictures TO yjaquier;
 
GRANT succeeded.

Then with my own account (yjaquier) I create a test table:

SQL> CREATE TABLE pictures
(
  id NUMBER,
	picture bfile,
	CONSTRAINT pictures_pk PRIMARY KEY (id) enable
)
TABLESPACE users;
 
TABLE created.
 
SQL> INSERT INTO pictures VALUES(1,BFILENAME('PICTURES','anonymous.png'));
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.

Then I create a basicfiles lob table:

SQL> CREATE TABLE basiclob
(
  id NUMBER,
	pictures blob,
	CONSTRAINT basiclob_pk PRIMARY KEY (id) enable
)
lob (pictures) store AS basicfile picbasiclob (TABLESPACE lobdata
INDEX picbasiclob_idx (TABLESPACE lobdata)
);
 
TABLE created.

And fill it with picture table:

SQL> TRUNCATE TABLE yjaquier.basiclob;
 
TABLE truncated.
 
SQL>
DECLARE
  blob1 blob;
	bfile1 bfile:=BFILENAME('PICTURES','anonymous.png');
	dest_offset NUMBER:=1;
	src_offset NUMBER:=1;
BEGIN
  INSERT INTO basiclob VALUES (1,EMPTY_BLOB()) RETURNING pictures INTO blob1;
  DBMS_LOB.OPEN(blob1,dbms_lob.LOB_READWRITE);
  DBMS_LOB.OPEN(bfile1,dbms_lob.FILE_READONLY);
  DBMS_LOB.LOADBLOBFROMFILE(blob1,bfile1,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
  dbms_lob.CLOSE(blob1);
  dbms_lob.CLOSE(bfile1);
  COMMIT;
END;
/
 
PL/SQL PROCEDURE successfully completed.

Let’s compare disk usage:

SQL> purge recyclebin;
 
Recyclebin purged.
 
SQL> SET lines 180
SQL> SELECT segment_name,segment_type,tablespace_name,extents,bytes/1024 AS "Size KB" FROM dba_segments WHERE owner='YJAQUIER';
 
SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                   EXTENTS    SIZE KB
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ----------
BASICLOB_PK                                                                       INDEX              USERS                                   1         64
PICTURES_PK                                                                       INDEX              USERS                                   1         64
BASICLOB                                                                          TABLE              USERS                                   1         64
PICTURES                                                                          TABLE              USERS                                   1         64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                 2        128
PICBASICLOB_IDX                                                                   LOBINDEX           LOBDATA                                 1         64
 
6 ROWS selected.
 
SQL> SELECT segment_name,segment_type,tablespace_name,bytes/1024 AS "Size KB" FROM dba_extents WHERE owner='YJAQUIER';
 
SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                   SIZE KB
--------------------------------------------------------------------------------- ------------------ ------------------------------ ----------
BASICLOB                                                                          TABLE              USERS                                  64
BASICLOB_PK                                                                       INDEX              USERS                                  64
PICTURES                                                                          TABLE              USERS                                  64
PICTURES_PK                                                                       INDEX              USERS                                  64
PICBASICLOB_IDX                                                                   LOBINDEX           LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
 
7 ROWS selected.

So the 44KB picture does not fit in one extent and Oracle has allocated 2 so the 64*2=128 KB…

Space usage can also be seen with:

variable unf NUMBER; 
variable unfb NUMBER; 
variable fs1 NUMBER; 
variable fs1b NUMBER; 
variable fs2 NUMBER; 
variable fs2b NUMBER; 
variable fs3 NUMBER; 
variable fs3b NUMBER; 
variable fs4 NUMBER; 
variable fs4b NUMBER; 
variable full NUMBER; 
variable fullb NUMBER; 
 
EXEC dbms_space.space_usage('YJAQUIER', 'PICBASICLOB', 'LOB', :unf, :unfb, :fs1, :fs1b, :fs2, :fs2b, :fs3, :fs3b, :fs4, :fs4b, :full, :fullb); 
 
SQL> print unfb ;
 
      UNFB
----------
     49152
 
SQL> print fullb;
 
     FULLB
----------
     49152
 
var total_blocks NUMBER;
var total_bytes NUMBER;
var unused_blocks NUMBER;
var unused_bytes NUMBER;
var lastextf NUMBER;
var last_extb NUMBER;
var lastusedblock NUMBER;
 
EXEC DBMS_SPACE.UNUSED_SPACE('YJAQUIER', 'PICBASICLOB', 'LOB', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> print total_blocks
 
TOTAL_BLOCKS
------------
          16
 
SQL> print unused_blocks
 
UNUSED_BLOCKS
-------------
            0

Or:

SQL> SELECT NVL((SUM(dbms_lob.getlength(pictures))),0) AS bytes FROM basiclob;
 
     BYTES
----------
     44167

The explanation is coming from LOB Storage chapter of Oracle® Database SecureFiles and Large Objects Developer’s Guide:

LOBs segments are different because they need at least 3 blocks in the first extent

Initial extent is 64KB, chunk size is 8KB so 8 blocks, means 5 only remains for real figures i.e. 5*8=40 KB so not enough to store the 44KB image.

You can use SQL*Developer to see that the BLOB column pictures has been well setup with the image of the BFILE:

Lob1
Lob1
Lob2
Lob2

Now we can imagine having an application and modifying the picture to reflect different users. As an example I read the LOB content and write it to a file in a directory:

DECLARE
  blob1 blob;
  len NUMBER;
  offset_start NUMBER:=1;
  buffer1 RAW(32767);
  file1 UTL_FILE.FILE_TYPE;
  amount NUMBER:=32767;
BEGIN
  file1:=UTL_FILE.FOPEN('PICTURES','test.png','wb',amount);
 
  SELECT pictures INTO blob1 FROM basiclob WHERE id=1;
 
  SELECT NVL((SUM(dbms_lob.getlength(pictures))),0) INTO len FROM basiclob WHERE id=1;
 
  WHILE offset_start<len
  LOOP
    dbms_lob.read(blob1,amount,offset_start,buffer1);
    UTL_FILE.PUT_RAW (file1, buffer1,TRUE);
    offset_start:=offset_start+amount;
  END LOOP;
 
  UTL_FILE.FCLOSE(file1);
END;
/

Securefiles LOBs

Available since Oracle database 11gR1, to be able to use them ensure below parameter has correct value:

SQL> show parameter db_securefile
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_securefile                        string      PERMITTED

By default securefiles LOBs are allowed, you can enforce (or forbid) their usage by changing this parameter to ALWAYS (NEVER or IGNORE to forbid with or without an error message).

If no particular keyword is specified then by default basicfiles LOB will be used.

So what do they bring as features, the best part of the official documentation is:

SECUREFILE option: Parameter that specifies SecureFiles LOBs, an architecture that improves performance and also supports compression, deduplication, and encryption features.

About Compression SecureFiles Intelligent Compression, available with the Oracle Advanced Compression Option, seamlessly analyses SecureFiles LOB data and compresses to save disk space. Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Compression. See Oracle Database Licensing Information for more information.
About Deduplication SecureFiles Intelligent Deduplication, available with the Oracle Advanced Compression Option, enables Oracle Database to automatically detect duplicate LOB data within a LOB column or partition, and conserve space by storing only one copy of the data. Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Deduplication. See Oracle Database Licensing Information for more information. Note also that Oracle Streams does not support SecureFiles LOBs that are deduplicated.
About Encryption SecureFiles Intelligent Encryption, available with the Oracle Advanced Security Option, introduces a new encryption facility for LOBs. The data is encrypted using Transparent Data Encryption (TDE), which allows the data to be stored securely, and still allows for random read and write access.Note that you must have a license for the Oracle Advanced Security Option before implementing SecureFiles Intelligent Encryption. See Oracle Database Licensing Information for more information.

So we see that without buying expensive Enterprise Edition options you will not get any added value of securefiles LOB…

Let’s create a securefiles LOB test table (maximum compression and duplicates removed):

CREATE TABLE securelob
(
  id NUMBER,
	pictures blob,
	CONSTRAINT securelob_pk PRIMARY KEY (id) enable
)
lob (pictures) store AS securefile picsecurelob (TABLESPACE lobdata
INDEX picsecurelob_idx (TABLESPACE lobdata)
COMPRESS HIGH
DEDUPLICATE
);

And load it with something like (I insert 50 rows in the securefiles LOB and I load the basicfiles LOB with 50 rows also to be able to compare):

DECLARE
  blob1 blob;
  bfile1 bfile:=BFILENAME('PICTURES','anonymous.png');
  dest_offset NUMBER:=1;
  src_offset NUMBER:=1;
  i NUMBER:=1;
BEGIN
  FOR i IN 1..50 LOOP
    INSERT INTO securelob VALUES (i,EMPTY_BLOB()) RETURNING pictures INTO blob1;
    DBMS_LOB.OPEN(blob1,dbms_lob.LOB_READWRITE);
    DBMS_LOB.OPEN(bfile1,dbms_lob.FILE_READONLY);
    DBMS_LOB.LOADBLOBFROMFILE(blob1,bfile1,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
    dbms_lob.CLOSE(blob1);
    dbms_lob.CLOSE(bfile1);
    dest_offset:=1;
    src_offset:=1;
    COMMIT;
  END LOOP;
END;
/

We can then check disk space usage versus basicfiles LOB with:

SQL> SET lines 180
SQL> SET pages 100
SQL> SELECT segment_name,segment_type,tablespace_name,extents,bytes/1024 AS "Size KB" FROM dba_segments WHERE owner='YJAQUIER';
 
SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                   EXTENTS    SIZE KB
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ----------
BASICLOB_PK                                                                       INDEX              USERS                                   1         64
SECURELOB_PK                                                                      INDEX              USERS                                   1         64
PICTURES_PK                                                                       INDEX              USERS                                   1         64
BASICLOB                                                                          TABLE              USERS                                   1         64
SECURELOB                                                                         TABLE              USERS                                   1         64
PICTURES                                                                          TABLE              USERS                                   1         64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                18       3072
PICSECURELOB                                                                      LOBSEGMENT         LOBDATA                                 3       1216
PICBASICLOB_IDX                                                                   LOBINDEX           LOBDATA                                 1         64
PICSECURELOB_IDX                                                                  LOBINDEX           LOBDATA                                 1         64
 
10 ROWS selected.
 
SQL> SELECT segment_name,segment_type,tablespace_name,bytes/1024 AS "Size KB" FROM dba_extents WHERE owner='YJAQUIER';
 
SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME                   SIZE KB
--------------------------------------------------------------------------------- ------------------ ------------------------------ ----------
BASICLOB                                                                          TABLE              USERS                                  64
BASICLOB_PK                                                                       INDEX              USERS                                  64
PICTURES                                                                          TABLE              USERS                                  64
PICTURES_PK                                                                       INDEX              USERS                                  64
SECURELOB                                                                         TABLE              USERS                                  64
SECURELOB_PK                                                                      INDEX              USERS                                  64
PICBASICLOB_IDX                                                                   LOBINDEX           LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                                64
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                              1024
PICBASICLOB                                                                       LOBSEGMENT         LOBDATA                              1024
PICSECURELOB                                                                      LOBSEGMENT         LOBDATA                               128
PICSECURELOB                                                                      LOBSEGMENT         LOBDATA                                64
PICSECURELOB                                                                      LOBSEGMENT         LOBDATA                              1024
PICSECURELOB_IDX                                                                  LOBINDEX           LOBDATA                                64
 
29 ROWS selected.

1.2MB for securefiles LOB versus 3MB for basicfiles LOB, almost 1/3 compression and remove duplicate ratio in my basic example (same picture inserted 50 times)… Not bad…

Migration from basicfiles LOBs to securefiles LOBs

Oracle documentation is clear on this subject, online redefinition must be used. To test it I will convert my basiclob.pictures basifiles LOB column to a secure file one:

SQL> SELECT segment_name,segment_subtype
FROM dba_segments
WHERE owner='YJAQUIER'
AND segment_type='LOBSEGMENT';
 
SEGMENT_NAME                                                                      SEGMENT_SU
--------------------------------------------------------------------------------- ----------
PICSECURELOB                                                                      SECUREFILE
PICBASICLOB                                                                       ASSM
 
SQL> CREATE TABLE basiclob_int
(
  id NUMBER,
  pictures blob
)
lob (pictures) store AS securefile picbasic_intlob (TABLESPACE lobdata
INDEX picbasiclob_int_idx (TABLESPACE lobdata)
);
 
TABLE created.
 
DECLARE
col_mapping VARCHAR2(1000);
BEGIN
col_mapping :='id id , pictures pictures';
DBMS_REDEFINITION.START_REDEF_TABLE('yjaquier', 'basiclob', 'basiclob_int', col_mapping);
END;
/

I have set ignore_errors to TRUE to avoid PL/SQL block failing because I already add the constraint on the id column (or you avoid adding the constraint on the interim table):

ORA-01408: such COLUMN list already indexed
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1155
ORA-06512: AT "SYS.DBMS_REDEFINITION", line 1885
ORA-06512: AT line 4
SET serveroutput ON
DECLARE
error_count PLS_INTEGER := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('yjaquier', 'basiclob', 'basiclob_int', 1, TRUE,TRUE,TRUE,TRUE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
 
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('yjaquier', 'basiclob', 'basiclob_int');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> DROP TABLE basiclob_int;
 
TABLE dropped.
 
SQL> purge recyclebin;
 
Recyclebin purged.
 
SQL> SELECT segment_name,segment_subtype
FROM dba_segments
WHERE owner='YJAQUIER'
AND segment_type='LOBSEGMENT';
 
SEGMENT_NAME                                                                      SEGMENT_SU
--------------------------------------------------------------------------------- ----------
PICSECURELOB                                                                      SECUREFILE
PICBASIC_INTLOB                                                                   SECUREFILE

Then at the end it is not 100% perfect even if the solution is beautiful, the LOB name is the not the one we would expect (PICBASIC_INTLOB instead of PICBASICLOB) and constraint name has wrong name if you have chosen to add it in interim table creation (transferring it with DBMS_REDEFINITION keeps the correct name). Another flow of DBMS_REDEFINITION commands would be needed to reach perfect solution…

DBFS

DBFS stands for Oracle Database File System, in short it is an extension of LOB where programs not specifically written to use LOB (so in SQL) can access files stored inside the database. In other words we will see below that files stored inside the database can be accessed with OS command. On Linux you can even create a mount point using Filesystem in User Space (FUSE) kernel module and see this database storage like a normal mount point (FileSystem). The database acts in this case like a NFS server…

One example often highlighted by Oracle is an ETL process generating a file or a report that is then stored in a DBFS content and then through a LOB accessed by multiple users in a secure way…

As SYS I create a dedicated tablespace to store the DBFS and grant the needed role to my account:

SQL> CREATE TABLESPACE dbfstbs;
 
TABLESPACE created.
 
SQL> GRANT DBFS_ROLE TO yjaquier;
 
GRANT succeeded.

To create a DBFS SecureFiles Store there is a supplied SQL script. As YJAQUIER:

SQL> @?/rdbms/admin/dbfs_create_filesystem.SQL dbfstbs dbfs_staging
No errors.
--------
CREATE STORE:
BEGIN dbms_dbfs_sfs.createFilesystem(store_name => 'FS_DBFS_STAGING', tbl_name
=> 'T_DBFS_STAGING', tbl_tbs => 'dbfstbs', lob_tbs => 'dbfstbs', do_partition =>
FALSE, partition_key => 1, do_compress => FALSE, compression => '', do_dedup =>
FALSE, do_encrypt => FALSE); END;
--------
REGISTER STORE:
BEGIN dbms_dbfs_content.registerStore(store_name=> 'FS_DBFS_STAGING',
provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); END;
--------
MOUNT STORE:
BEGIN dbms_dbfs_content.mountStore(store_name=>'FS_DBFS_STAGING',
store_mount=>'dbfs_staging'); END;
--------
CHMOD STORE:
DECLARE m INTEGER; BEGIN m := dbms_fuse.fs_chmod('/dbfs_staging', 16895); END;
No errors.

Remark:
It adds T_DBFS_STAGING and SFS$_FSTP_11 tables to your schema (and two views as well from what I have seen):

SQL> SELECT pathname FROM t_dbfs_staging;
 
PATHNAME
--------------------------------------------------------------------------------
/
/.sfs
/.sfs/RECYCLE
/.sfs/attributes
/.sfs/content
/.sfs/snapshots
/.sfs/tools
/anonymous.png
/picture1.png
/picture2.png
 
10 ROWS selected.
 
SQL> SELECT full_pathname FROM dbfs_attributes;
 
FULL_PATHNAME
--------------------------------------------------------------------------------
/dbfs_staging/picture1.png
/dbfs_staging/picture2.png
/dbfs_staging/anonymous.png
/dbfs_staging/.sfs/content
/dbfs_staging/.sfs/RECYCLE
/dbfs_staging/.sfs/snapshots
/dbfs_staging/.sfs/tools
/dbfs_staging/.sfs/attributes
/dbfs_staging/.sfs
/dbfs_staging/
 
10 ROWS selected.

Then you can start listing its content with (dbfs_client.exe on Windows), I obviously first created a server1_orcl TNSNames entry pointing to my database:

[oracle@server1 ~]$ dbfs_client yjaquier@server1_orcl --command ls -l -a dbfs:/dbfs_staging
Password:
drwxr-xr-x              root            root               0    Aug 08 12:38    dbfs:/dbfs_staging/.sfs

From my desktop I copy the anonymous.png picture file to the DBFS content:

C:\Download>dbfs_client.exe yjaquier@server1_orcl --command cp anonymous.png dbfs:/dbfs_staging
Password:
anonymous.png -> dbfs:/dbfs_staging/anonymous.png

That can now be seen from Linux database server as well:

[oracle@server1 ~]$ dbfs_client yjaquier@server1_orcl --command ls -l -a dbfs:/dbfs_staging
Password:
drwxr-xr-x              root            root               0    Aug 08 12:38    dbfs:/dbfs_staging/.sfs
-rw-------              root            root           44167    Aug 09 09:56    dbfs:/dbfs_staging/anonymous.png

The below SQLs can also be used to see DBFS store content:

SELECT * FROM dbfs_content;
SELECT * FROM dbfs_content_properties;
SELECT * FROM TABLE(dbms_dbfs_content.listStores);
SELECT * FROM TABLE(dbms_dbfs_content.listMounts);
SELECT * FROM dbfs_attributes;

Using DBMS_LOB package you can move a securefile LOB in a DBFS content store, I noticed it created another securefile LOB (LOB_SFS$_FST_11) that is using DBFS content store:

DECLARE
  blob1 blob;
BEGIN
  SELECT pictures INTO blob1 FROM securelob WHERE id=1 FOR UPDATE;
 
  DBMS_LOB.MOVE_TO_DBFS_LINK(blob1,'/dbfs_staging/picture1.png');	
END;
/
[oracle@server1 ~]$ dbfs_client yjaquier@server1_orcl --command ls -l -a dbfs:/dbfs_staging
Password:
drwxr-xr-x              root            root               0    Aug 08 12:38    dbfs:/dbfs_staging/.sfs
-rw-------              root            root           44167    Aug 09 09:56    dbfs:/dbfs_staging/anonymous.png
-rw-r--r--              root            root           44167    Aug 09 15:45    dbfs:/dbfs_staging/picture1.png

The beauty here is to have the document (the png picture in my case) store in a securefile LOB column and accessible through the DBFS client interface. You can copy it in a folder of your Windows desktop using something like:

C:\Download>dbfs_client.exe yjaquier@server1_orcl --command cp dbfs:/dbfs_staging/picture1.png c:\download
Password:
dbfs:/dbfs_staging/picture1.png -> c:\download/picture1.png

And open it with you preferred image viewer program…

As a test let’s try to delete the picture:

C:\Download>dbfs_client.exe yjaquier@server1_orcl --command rm dbfs:/dbfs_staging/picture1.png
Password:
unlinking file dbfs:/dbfs_staging/picture1.png

Obviously at SQL level you get an error:

SQL> SELECT * FROM securelob WHERE id=1;
ERROR:
ORA-64002: specified PATH IS invalid
ORA-06512: AT "SYS.DBMS_DBFS_SFS", line 3881
ORA-06512: AT "SYS.DBMS_DBFS_SFS", line 6697
ORA-06512: AT line 1
ORA-06512: AT "SYS.DBMS_DBFS_CONTENT", line 3686
ORA-06512: AT "SYS.DBMS_LOB_AM_PRIVATE", line 65
ORA-06512: AT line 1

Which can, for example, be corrected with:

SQL> UPDATE securelob SET pictures=(SELECT pictures FROM securelob WHERE id=2) WHERE id=1;
SQL> COMMIT;
SQL> SELECT * FROM securelob WHERE id=1;
 
        ID
----------
PICTURES
--------------------------------------------------------------------------------
         1
89504E470D0A1A0A0000000D49484452000002890000032008060000004FB3F8B300002000494441
54789CECDD799C9D657DFFFFD7759F6596CC966DB24D42082920048118AD456B919F5FAA68080189

Then if you wish you have to execute a second time the DBMS_LOB.MOVE_TO_DBFS_LINK procedure.

Note that there is what I call an extension of DBFS that is DBFS Hierarchical Store (DBFS HS)… Which aim to implement what is called storage tiering, currently tape and Amazon S3 (Cloud storage) are supported.

This is quite complex to test as the needed infrastructure is big. Even using the fake tape library of RMAN is not so easy so I prefer to quote an excellent article demonstrating it by example:

DBFS_HS – Database Hierarchical Storage

References

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>