11g Shared I/O Pool
for Securefile Lobs
Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015
Oracle 11g New Features Tips
The Cache Option defaults to
NOCACHE. Possible values are as follows:
CACHE: Oracle places LOB data into the
buffer cache which provides fast access. CACHE is
only supported in combination with the logging option.
NOCACHE: LOB values are not placed
into the buffer cache.
CACHE READS: LOB values are placed
into the buffer cache but only for read operations. Write
operations are not cached.
Oracle 11g uses a cache called SHARED I/O
POOL for securefile lobs operations if nocache
is used for the lobs. When lobs are created with the NOCACHE
option, which is the default.
% Oracle can
automatically adjust the size of this cache to up to 4% of
the buffer cache.
% The default size of
shared i/o pool is 0
LUTZ AS SYSDBA @ orcl SQL>
SELECT * FROM v$sgainfo
-------------------------------- ---------- ---
Fixed SGA Size
Buffer Cache Size
Shared Pool Size
Large Pool Size
Java Pool Size
Streams Pool Size
Shared IO Pool Size
Maximum SGA Size
Startup overhead in Shared Pool 46137344 No
Free SGA Memory Available
The shared i/o pool uses
shared memory and thus can be used by large concurrent
operations of securefile lobs. If the memory for the shared i/o
pool is too small for a large securefile lob operation the
server process will temporarily utilize PGA memory
until enough shared memory is available again.
Securefile lobs can be created with different
The default is LOGGING which creates
redo records for the creation of the lob segment as well as for the
Another alternative is
FILESYSTEM_LIKE_LOGGING. This only created redo for the metadata
of the securefile lob. This reduces the mean time to recover
in case of an instance crash. At the same time
full data recoverability is ensured. The drawback here is that data
can change during an online backup which would lead to inconsistency
in. This would be fatal in case of media recovery being necessary.
% Make sure that LOGGING is
enforced for the lob storage during online backups!
% Make sure that all redo
which was generated during the online backup is
Here is the syntax to enforce logging and to
disable it again:
LUTZ AS SYSDBA @ orcl SQL> ALTER
DATABASE FORCE LOGGING;
LUTZ AS SYSDBA @ orcl SQL> ALTER DATABASE NO FORCE LOGGING;
It is also possible to use ALTER TABLE
to modify the properties of securefile lob columns:
ALTER TABLE encrypt_03 MODIFY LOB
ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (DEDUPLICATE LOB);
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc) (DEDUPLICATE
ALTER TABLE encrypt_03 MODIFY LOB
ALTER TABLE encrypt_03 MODIFY LOB (resume_doc) (COMPRESS HIGH);
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc)
ALTER TABLE encrypt_03 MODIFY (resume_doc
CLOB ENCRYPT USING '3DES168');
ALTER TABLE encrypt_03 MODIFY PARTITION p1 LOB (resume_doc) (
ALTER TABLE encrypt_03 MODIFY (resume_doc CLOB ENCRYPT IDENTIFIED BY
% Although it is possible
to use the ALTER TABLE statement to enable encryption,
compression or de-duplication of securefile lobs after the
creation, Oracle recommends to use ONLINE
REDEFINITION for such operations.
The ENCRYPT and DECRYPT keywords
can only be used to encrypt not yet encrypted respectively decrypt
already encrypted lob columns.
% If you try to encrypt an
already encrypted column Oracle will return an error
@ orcl SQL> alter table encrypt_01 modify (resume_pdf CLOB encrypt
alter table encrypt_01 modify (resume_pdf CLOB encrypt using
ERROR at line 1:
ORA-28334: column is already encrypted
[oracle@rhas4 ~]$ oerr ora 28334
28334, 0000, "column is already encrypted"
// *Cause: An attempt was made to encrypt an encrypted column.
Lobs can only be encrypted on a per column
Transparently Encrypted Exports
of securefile lobs are only possible with ORACLE datapump
The same is true for transportable
tablespaces with securefile lobs.
% Table shrinking
is not supported for securefile lobs in Oracle 11g