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

Home » Articles » 12c » Here

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.

Related articles.

12c Release 1 (12.1)

Default SecureFiles

If the COMPATIBLE parameter is set to "" 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.

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

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.

Database File Systems (DBFS) Enhancements (12.2)

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

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.