blobFS – Imported an Oracle dump from a BLOB using a FUSE filesystem

For those who have not been following, I reported previously that I had created a query-based filesystem with node.js, fuse4js, FUSE, and Oracle. This filesystem is mountable by any OS user and uses queries against Oracle tables as its source instead of disk. In this specific case, a table containing filenames and BLOBs was used as the source; the FUSE filesystem then presented the BLOB contents as a file to the OS. No actual files were written.

Curiosity led me to try a new test. What if I:

1. Created a table and populated it
2. Exported the table with ‘exp’
3. Dropped the new table
4. Loaded the exp dump file into my BLOB table
5. Mounted the BLOB table as a filesystem
6. Imported the table from the BLOB filesystem

I executed the test this morning, and it went incredibly smoothly. The exp dump file was easily stored as a BLOB, and ‘imp’ was able to read the file without any issue when it was translated to filesystem from the select query.

Set It Up

Table Creation

SQL> create table test_import as select * from dba_users;

Table created.

SQL> select count(*) from test_import;

   COUNT(*)
----------
        13

Exporting the Table

steve@UbuntuVM:/var/www/blobFS$ exp steve/steve file=test_import.dmp tables=test_import

Export: Release 11.2.0.2.0 - Production on Wed Feb 13 09:47:47 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 
64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                    TEST_IMPORT         13 rows exported
Export terminated successfully without warnings.

Load the Dumpfile Into BLOBTAB then Drop the Exported Table

steve@UbuntuVM:/var/www/blobFS$ php loadfile.php test_import.dmp

SQL> select * from blobtab;

FILENAME
--------------------------------------------------------------------------------
FILECONTENT
--------------------------------------------------------------------------------
test_import.dmp
0300014558504F52543A5631312E30322E30300A4453544556450A525441424C45530A383139320A
300A37320A300A0001036907D000010000000000000000000F002020202020202020202020202020

SQL> drop table test_import;

Table dropped.

SQL> select count(*) from test_import;
select count(*) from test_import
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

Enter blobFS

Starting the Filesystem

steve@UbuntuVM:/var/www/blobFS$ node jsonFS.js mnt
Mount point: mnt
File system started at mnt
To stop it, type this in another shell: fusermount -u mnt

steve@UbuntuVM:/var/www/blobFS$ cd mnt
steve@UbuntuVM:/var/www/blobFS/mnt$ ls -ltr
total 0
-rwxr-xr-x 0 steve steve 21848 Dec 31  1969 test_import.dmp

Let’s Import!

steve@UbuntuVM:/var/www/blobFS/mnt$ imp steve/steve file=test_import.dmp fromuser=steve touser=steve

Import: Release 11.2.0.2.0 - Production on Wed Feb 13 09:50:31 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights 
reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 
64bit Production

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing STEVE's objects into STEVE
. . importing table                  "TEST_IMPORT"         13 rows imported
Import terminated successfully without warnings.

The Moment of Truth

SQL> select count(*) from test_import;

   COUNT(*)
----------
        13

Conclusion

Even though this test is not much different from my last one in that I loaded files into BLOBs then mounted the FUSE filesystem to manifest them as files to the OS, but it’s awesome that I was able to use it for an Oracle-specific tool. Very meta, as they say.

I wonder if I can run an Oracle instance purely from BLOB-stored datafiles? Hmmm…

Update

Kevin Closson provided some links from his own blog which detail DBFS, Oracle’s implementation of FUSE (Note, I’d like to claim I did it before Oracle). Thanks for the great information, Kevin.

Also adding a test done by Ronald Rood, a full RW database created inside DBFS. Now I definitely want to get this working in my homebrew version.