8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

SecureFiles : Large Object (LOB) Enhancements in Oracle Database 12c (12.1 and 12.2)

This article provides and overview of the enhancements to the SecureFiles Large Object (LOB) functionality in Oracle Database 12c (12.1 and 12.2). Several of the sections link to separate articles that give more details about the functionality.

12c Release 1 (12.1)
12c Release 2 (12.2)

12c Release 1 (12.1)

Default SecureFiles

If the COMPATIBLE parameter is set to "12.0.0.0" or higher, the default value for the DB_SECUREFILE parameter is "PREFERRED", which means all LOBs will be created as SecureFiles, unless they are explicitly created as BasicFiles or their tablespace uses Manual Segment Space Management.

Parallel DML (PDML) Support Enhancements

Prior to 12cR1 Parallel DML (PDML) had a number of restrictions when used against a table containing LOBs (BasicFile or SecureFile). PDML was only supported for the following operations and only if the table containing the LOB was partitioned.

INSERT INTO ... AS SELECT ...
CREATE TABLE ... AS SELECT ...
DELETE
UPDATE
MERGE
Multitable INSERT

From Oracle Database 12c Release 1 (12.1) onward Oracle supports more parallel operations against partitioned tables containing LOBs (SecureFile and BasicFile).

INSERT
INSERT INTO ... AS SELECT ...
CREATE TABLE ... AS SELECT ...
DELETE
UPDATE
MERGE
Multitable INSERT
SQL*Loader
Import/Export

In addition, it supports the same parallel operations for non-partitioned tables that use SecureFile LOBs. It will also support direct loads against SecureFiles LOB columns which have a context index defined on them.

Data Pump LOB Support

The LOB_STORAGE clause of the TRANSFORM parameter allows the LOB storage characteristics of table columns in a non-transportable import to be altered on the fly. This functionality is described here.

Database File Systems (DBFS) Enhancements (12.1)

The Oracle Database File System (DBFS) functionality was introduced in Oracle 11g. The major downside to this feature was real file system style access was only available on the Linux platform due to the reliance on the FUSE project. All other platforms were limited to using the client tool to access the file system.

Oracle 12cR1 introduced HTTP/HTTPS, FTP and WebDAV access to DBFS via the "/dbfs" virtual directory in the XML DB repository. This functionality is described here.

Extended Data Types

Oracle Database 12c Release 1 introduced extended data types, optionally allowing VARCHAR2, NVARCHAR2 and RAW types up to 32K in size. In terms of usage they feel like conventional data types, but in reality these extended data types are using LOBs under the surface. This functionality is described here.

12c Release 2 (12.2)

Collation of LOB Data

Oracle Database 12c Release 2 (12.2) lets you specify the collation used for columns that hold string data, allowing you to easily perform case insensitive queries, as well as control the output order of queried data. The only supported collation for CLOB and NCLOB columns is the USING_NLS_COMP pseudo-collation. This functionality is described here.

LOB Conversion Functions

The following functions now include additional overloads to allow new conversions.

TO_BLOB : In addition to converting LONG RAW and RAW data types to a BLOB, the TO_BLOB function now has an overload to convert a BFILE value to a BLOB.
TO_CLOB : In addition to converting CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB to a CLOB, the TO_CLOB function now has an overload to convert a BFILE or BLOB to a CLOB value based on the specified character set. You can optionally set the mime type.
TO_CHAR : In addition to converting NCHAR, NVARCHAR2, CLOB, or NCLOB to a VARCHAR2, the TO_CHAR function now has an overload to convert a BFILE or BLOB to a VARCHAR2 value based on the specified character set. If the value is too large it is truncated.

Database File Systems (DBFS) Enhancements (12.2)

There are two main enhancements to Database File Systems (DBFS) in Oracle Database 12.2.

Oracle 12.1 allowed DBFS to be accessed by HTTP, FTP and WebDav. Oracle 12.2 now makes mounting DBFS on the file system using FUSE easier, and allows DBFS to be accessed by NFS. This functionality is part of the new Oracle File System (OFS) feature. You can read about OFS and how it can interact with DBFS here.
In Oracle 12.2 DBFS supports POSIX file locking (full-file locking only) when DBFS is access using the DBFS_CLIENT (included mounts via FUSE) or the PL/SQL APIs. You can read about this support here.

Distributed LOBs

Oracle Database 12.2 includes support for some operations on CLOB, BLOB and XMLTYPE data types over database links. You can read more about this functionality here.

Although the addition of any new functionality that increases the flexibility of the database is welcome, I would question the logic of any system design that requires access to LOBs over a database link. The impact will not be so great when the link is between PDBs in the same CDB instance, since the communication is local and optimized, but accessing LOBs on truly remote databases sounds like a performance nightmare waiting to unravel.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.