If you need to move data using Oracle Data Pump and have BFILEs in your database, what do you need to be aware of?

What Is a BFILE?

From the documentation:

BFILEs are data objects stored in operating system files, outside the database tablespaces. Data stored in a table column of type BFILE is physically located in an operating system file, not in the database. The BFILE column stores a reference to the operating system file. BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to or update a BFILE from within your application.

They are sometimes referred to as external LOBs.

You can store a BFILE locator in the database and use the locator to access the external data:

To associate an operating system file to a BFILE, first create a DIRECTORY object that is an alias for the full path name to the operating system file. Then, you can initialize an instance of BFILE type, using the BFILENAME function in SQL or PL/SQL …

In short, it is stuff stored outside the database that you can access from inside the database. Clearly, this requires special attention when you want to move your data.

How Do I Move It?

There are three things to consider:

1. The file outside the database – in the operating system.
2. The directory object.
3. The BFILE locator stored in the table.

Table and Schema Mode Export

1. You must copy the file in the operating system. Since a BFILE is read-only, you can copy the file before you perform the actual export.
2. You must create the directory object. Directory objects are system-owned objects and not part of a table or schema mode export.
3. Data Pump exports a BFILE locator together with the table. It exports the BFILE locator just like any other column. On import, Data Pump inserts the BFILE locator but performs no sanity checking. The database will not throw an error if the file is missing in the OS or if the directory is missing or erroneous.

Full Export

1. Like table and schema mode, you must copy the file.
2. Directory objects are part of a full export. On import, Data Pump creates a directory object with the same definition. If you place the external files in a different location in the target system, you must update the directory object.
3. Like table and schema mode. Data Pump exports the BFILE locator as part of the table.

Do I Have BFILEs in My Database?

You can query the data dictionary and check if there are any BFILEs:

SQL> select owner, table_name 
     from dba_tab_cols 
     where data_type='BFILE';

Further Reading

• MOS note: Primary Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)