{"id":7572,"date":"2016-04-12T19:43:45","date_gmt":"2016-04-12T17:43:45","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/"},"modified":"2016-04-12T19:43:45","modified_gmt":"2016-04-12T17:43:45","slug":"transparently-externalize-blob-to-bfile","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/","title":{"rendered":"Transparently externalize BLOB to BFILE"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nStoring documents within the database is the easiest, especially because you get them consistent with the metadata stored in the database. If you store them externally, then you need to manage their backup, their synchronization to standby site, the consistency in case of flashback or PITR, etc. However, documents grow (in number and in size thanks to better resolution of scan) and you don’t want a database where half of the size are documents in read only. If you have no option (partitioning, compression, etc) then you may choose to store the documents externally. This is usually a complete re-design of the application.
\nIn this blog post, I’ve done a quick test I’ve done to transform some BLOB into External LOB (aka BFILE) and make it transparent to the application.
\n
\nIt’s just a test of concept. Any comments are welcome if you think something is wrong here.<\/p>\n

Some display settings<\/p>\n

\nSQL> set linesize 220 pagesize 1000 echo on\nSQL> column filename format a20\nSQL> column doc format a80 trunc\nSQL> column external_doc format a40 trunc\nSQL> whenever sqlerror exit failure;\nSQL> connect demo\/demo@\/\/localhost\/pdb\nConnected.\n<\/code><\/pre>\n
First, I create a table with BLOB<\/p>\n
\nSQL> create table DEMOTAB ( id number, filename varchar2(255),doc blob );\nTable created.\n<\/code><\/pre>\n
And I will fill it with the content of 3 binary files. Let’s take them in $ORACLE_HOME\/bin just for the fun of it:<\/p>\n
\nSQL> host ls $ORACLE_HOME\/bin | nl | head -3 > \/tmp\/files.txt\nSQL> host cat \/tmp\/files.txt\n     1  acfsroot\n     2  adapters\n     3  adrci\n<\/code><\/pre>\n
I’m using SQL*Loader to load them to the BLOB:<\/p>\n
\nSQL> host echo \"load data infile '\/tmp\/files.txt' into table DEMOTAB fields terminated by '     ' ( id char(10),filename char(255),doc lobfile(filename) terminated by EOF)\" > \/tmp\/sqlldr.ctl\nSQL> host cat \/tmp\/sqlldr.ctl\nload data infile '\/tmp\/files.txt' into table DEMOTAB fields terminated by '     ' ( id char(10),filename char(255),doc lobfile(filename) terminated by EOF)\n \nSQL> host cd $ORACLE_HOME\/bin ; sqlldr demo\/demo@\/\/localhost\/pdb control=\/tmp\/sqlldr.ctl\n \nSQL*Loader: Release 12.1.0.2.0 on Tue Apr 12 21:03:22 2016\nCopyright (c) 1982, 2016, Oracle and\/or its affiliates.  All rights reserved.\n \nPath used:      Conventional\nCommit point reached - logical record count 3\n \nTable DEMOTAB:\n  3 Rows successfully loaded.\n \nCheck the log file:\n  sqlldr.log\nfor more information about the load.\n \n<\/code><\/pre>\n
They are loaded, I can query my table:<\/p>\n
\nSQL> select DEMOTAB.*,dbms_lob.getlength(doc) from DEMOTAB;\n \n        ID FILENAME             DOC                                                                              DBMS_LOB.GETLENGTH(DOC)\n---------- -------------------- -------------------------------------------------------------------------------- -----------------------\n         1 acfsroot             23212F62696E2F7368200A230A230A232061636673726F6F740A23200A2320436F70797269676874                     945\n         2 adapters             3A0A230A2320244865616465723A206E6574776F726B5F7372632F75746C2F61646170746572732E                   13360\n         3 adrci                7F454C4602010100000000000000000002003E000100000000124000000000004000000000000000                   46156\n\n<\/code><\/pre>\n
I’m creating a folder to store the files externally, and create a DIRECTORY for it:<\/p>\n
\nSQL> host rm -rf \/tmp\/files ; mkdir \/tmp\/files\nSQL> create directory DEMODIR as '\/tmp\/files';\nDirectory created.\n<\/code><\/pre>\n
Now I add a BFILE column to my table:<\/p>\n
\nSQL> alter table DEMOTAB add ( external_doc bfile );\nTable altered.\n<\/code><\/pre>\n
My idea is not to move all BLOB to External LOB, but only part of them. For example, old documents can be externalized whereas current ones stay in the database. That helps to control the database size without taking any risk about consistency in case of PITR.<\/p>\n
I’ve there an inline procedure ‘lob_to_file’ that reads a LOB and writes it to a file. In the body of the PL\/SQL block I call the procedure for the 2 first rows of my table, and once the files are externalized, I empty the DOC column (the BLOB) and set the EXTERNAL_DOC one (the BFILE):<\/p>\n
\nSQL> set serveroutput on\nSQL> declare\n    tmp_blob blob default empty_blob();\n    procedure lob_to_file(input_blob in BLOB, file_path in varchar2, file_name in varchar2) as\n     buffer raw(32767);\n     buffer_size number:=32767;\n     amount number;\n     offset number;\n     filehandle utl_file.file_type;\n     blob_size number;\n    begin\n     filehandle := utl_file.fopen(file_path, file_name,'wb', 1024);\n     blob_size:=dbms_lob.getlength(input_blob);\n     offset:=1;\n     amount:=32767;\n     while offset < blob_size loop\n      dbms_lob.read(input_blob, amount, offset, buffer);\n      utl_file.put_raw(filehandle, buffer,true);\n      offset := offset + buffer_size;\n      buffer := null;\n     end loop;\n    exception when others then\n     utl_file.fclose(filehandle);\n     raise;\n    end;\n   begin\n    for c in ( select * from DEMOTAB where id <=2 ) loop\n     lob_to_file (c.doc, 'DEMODIR',c.filename);\n     update DEMOTAB set doc=null,external_doc=bfilename('DEMODIR',c.filename) where id=c.id;\n    end loop;\n   end;\n\/\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n
Note: don’t take my code as an example. I did it quickly. You should know that best place for code examples is Tim Hall www.oracle-base.com<\/a><\/p>\n
I can check that I have the two files in my directory<\/p>\n
\nSQL> host ls -l \/tmp\/files\ntotal 128\n-rw-r--r--. 1 oracle oinstall   945 Apr 12 21:03 acfsroot\n-rw-r--r--. 1 oracle oinstall 13360 Apr 12 21:03 adapters\n<\/code><\/pre>\n
and compare it to the size of original file:<\/p>\n
\nSQL> host ls -l $ORACLE_HOME\/bin | head -4\ntotal 644308\n-rwxr-xr-x. 1 oracle oinstall       945 May 24  2014 acfsroot\n-rwxr-xr-x. 1 oracle oinstall     13360 Mar 23  2015 adapters\n-rwxr-x--x. 1 oracle oinstall     46156 Mar 25 17:20 adrci\n<\/code><\/pre>\n
And here is my table:<\/p>\n
\nSQL> select id,filename,dbms_lob.getlength(doc),external_doc from DEMOTAB;\n \n        ID FILENAME             DBMS_LOB.GETLENGTH(DOC) EXTERNAL_DOC\n---------- -------------------- ----------------------- ----------------------------------------\n         1 acfsroot                                     bfilename('DEMODIR', 'acfsroot')\n         2 adapters                                     bfilename('DEMODIR', 'adapters')\n         3 adrci                                  46156 bfilename(NULL)\n<\/code><\/pre>\n
You see that first two rows have empty BLOB but a BFILE addressing the files in DEMODIR
\nThe third row is untouched.<\/p>\n
Now, my idea is to make it transparent for the application, so I create a view on it which transparently retrieves the External LOB when LOB is null:<\/p>\n
\nSQL> create view DEMOVIEW as select id,filename,nvl(doc,external_doc) doc from DEMOTAB;\nView created.\n<\/code><\/pre>\n
And now time to query. The application does a select into a BLOB so let’s do the same:<\/p>\n
\nSQL> variable doc blob;\nSQL> exec select doc into :doc from DEMOVIEW where id=1;\nPL\/SQL procedure successfully completed.\nSQL> print doc\n \nDOC\n--------------------------------------------------------------------------------\n23212F62696E2F7368200A230A230A232061636673726F6F740A23200A2320436F70797269676874\n<\/code><\/pre>\n
This is the LOB coming from the external file. I get it as a BLOB when I query the view.<\/p>\n
And now querying the one that is still stored in the database:<\/p>\n
\nSQL> exec select doc into :doc from DEMOVIEW where id=3;\nPL\/SQL procedure successfully completed.\nSQL> print doc\n \nDOC\n--------------------------------------------------------------------------------\n7F454C4602010100000000000000000002003E000100000000124000000000004000000000000000\n<\/code><\/pre>\n
Querying the view instead of the table (and you can play with synonyms for that) the application get the document without knowing wheter it comes from the database or the external directory. It seems that externalizing binary documents do not require a re-design of the application.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Storing documents within the database is the easiest, especially because you get them consistent with the metadata stored in the database. If you store them externally, then you need to manage their backup, their synchronization to standby site, the consistency in case of flashback or PITR, etc. However, documents grow (in […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[206,96],"type_dbi":[],"class_list":["post-7572","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-lob","tag-oracle"],"acf":[],"yoast_head":"\nTransparently externalize BLOB to BFILE - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transparently externalize BLOB to BFILE\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Storing documents within the database is the easiest, especially because you get them consistent with the metadata stored in the database. If you store them externally, then you need to manage their backup, their synchronization to standby site, the consistency in case of flashback or PITR, etc. However, documents grow (in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-04-12T17:43:45+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Transparently externalize BLOB to BFILE\",\"datePublished\":\"2016-04-12T17:43:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\"},\"wordCount\":545,\"commentCount\":0,\"keywords\":[\"LOB\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\",\"name\":\"Transparently externalize BLOB to BFILE - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-04-12T17:43:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Transparently externalize BLOB to BFILE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Transparently externalize BLOB to BFILE - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/","og_locale":"en_US","og_type":"article","og_title":"Transparently externalize BLOB to BFILE","og_description":"By Franck Pachot . Storing documents within the database is the easiest, especially because you get them consistent with the metadata stored in the database. If you store them externally, then you need to manage their backup, their synchronization to standby site, the consistency in case of flashback or PITR, etc. However, documents grow (in […]","og_url":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/","og_site_name":"dbi Blog","article_published_time":"2016-04-12T17:43:45+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Transparently externalize BLOB to BFILE","datePublished":"2016-04-12T17:43:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/"},"wordCount":545,"commentCount":0,"keywords":["LOB","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/","url":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/","name":"Transparently externalize BLOB to BFILE - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-04-12T17:43:45+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/transparently-externalize-blob-to-bfile\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Transparently externalize BLOB to BFILE"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7572","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7572"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7572\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7572"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7572"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7572"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7572"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}