OCM tips – Implement securefile LOB

Reference:
Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)
Application Development -> Database SecureFiles and Large Objects Developer’s Guide -> 4 Using Oracle SecureFiles LOBs

In this section we will talk about SecureFile LOB (Large Object) segments. Detailed information about creating and managing LOB segments is provided in the next chapter.

SecureFiles is a LOB storage architecture that is used to store unstructured data and contains numerous features as deduplication, compression, encryption and etc. If you want to store a LOB as a SecureFile, use STORE AS SECUREFILE clause in the CREATE TABLE statement. If you omit this clause, the LOB will be stored in a regular traditional way which is called BasicFile.

A SecureFile LOB can be created only in a tablespace managed with Automatic Segment Space Management (ASSM). The following error will appear if you attempt to create a SecureFile LOB in a non-ASSM tablespace:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace “SYSTEM”

DB_SECUREFILE parameter is used to control the action of the database with regards to the LOB storage. There are four values for this parameter:

– NEVER is used to make all LOBs that are created as a SecureFile to be created as a BasicFile LOB.
– PERMITTED is a default value that allows LOBs to be created as a SecureFile using STORE AS SECUREFILE clause.
– ALWAYS is used to make all LOBs in a ASSM tablespace be created as a SecureFile LOBs.
– IGNORE is used to ignore store as securefile clause and creation of SecureFile LOBs are permitted.

Note: Make sure the value of parameter DB_SECUREFILE is not set to NEVER and IGNORE if you are asked to create a SecureFile LOB during an exam.

Let’s create a simple table with a SecureFile LOB segment. First, create a table with BasicFile LOB as follows:

SQL> CREATE TABLE tbl_basicfile_lob
2 (id number,
3 lob_data clob)
4 LOB (lob_data) STORE AS BASICFILE;
Table created.
 
SQL> INSERT INTO tbl_basicfile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
 
SQL>

Now create a table with SecureFile LOB data as follows:

SQL> CREATE TABLE tbl_securefile_lob (
2 id number, lob_data clob)
3 LOB (lob_data) STORE AS SECUREFILE (tablespace users);
Table created.
 
SQL> INSERT INTO tbl_securefile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
SQL>

Query DBA_LOBS view to get more information about the lob segments:

SQL> SET LINESIZE 150
SQL> COL column_name FORMAT a10
SQL> SELECT table_name, column_name, segment_name, securefile FROM dba_lobs where table_name like ‘TBL%’;
 
TABLE_NAME COLUMN_NAM SEGMENT_NAME SEC
—————————— ———- —————————— —
TBL_BASICFILE_LOB LOB_DATA SYS_LOB0000096459C00002$$ NO
TBL_SECUREFILE_LOB LOB_DATA SYS_LOB0000096464C00002$$ YES
SQL>

If the LOB segment is stored as a SecureFile, you will be able to use the following features.

  • Compression
  • Encryption
  • Deduplication
  • Caching
  • Logging

In this newsletter, we will cover the compression feature

Compression
This feature is used to compress the LOB segment. A table compression doesn’t affect the LOB compression. It means that if the table is compressed, the LOB segment will not be compressed automatically. Oracle has two levels of compression, i.e 11gR1 – MEDIUM and HIGH. (a new LOW compression level is added at 11gR2). Check the following example to see how to compress the LOB segment and appreciate the difference between compressed
and noncompressed LOBs.

Create a table with non-compressed LOB segment and insert some data:

SQL> CREATE TABLE tbl_lob_nocompress
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users NOCOMPRESS);
Table created.
 
SQL> INSERT INTO tbl_lob_nocompress
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.

Now create a table with high compressed lob segment, insert some data and gather statistics from both tables:

SQL> CREATE TABLE tbl_lob_compress_high
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users COMPRESS HIGH);
 
Table created.
SQL> INSERT INTO TBL_LOB_COMPRESS_HIGH
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_NOCOMPRESS’);
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_COMPRESS_HIGH’);
PL/SQL procedure successfully completed.

Query DBA_LOBS view to find out the status of compression:

SQL> SELECT table_name, segment_name, securefile, compression
FROM dba_lobs
WHERE table_name LIKE ‘%COMPRESS%’;
TABLE_NAME SEGMENT_NAME SEC COMPRE
—————————— —————————— — ——
TBL_LOB_NOCOMPRESS SYS_LOB0000096719C00002$$ YES NO
TBL_LOB_COMPRESS_HIGH SYS_LOB0000096722C00002$$ YES HIGH

Now query USER_SEGMENTS view to see the size difference of compressed and noncompressed lob segments:

SQL> COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN (‘SYS_LOB0000096719C00002$$’,’SYS_LOB0000096722C00002$$’);
 
SEGMENT_NAME BYTES
—————————— ———-
SYS_LOB0000096719C00002$$ 5373952
SYS_LOB0000096722C00002$$ 131072
SQL>

In the next newsletter, we will cover the rest SecureLob features.

If you want to get more information on managing SecureLobs, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>