Optimize space usage for the LOB data

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Implement securefile LOB
[2] Create and manage LOB segments
[3] Lob Space Management using DBMS_SPACE
[4] Shrink a LOB
[5] Oracle Datapump supports SecureFiles as Default
[6] Parallel DML

[1] Implement securefile LOB

SecureFiles LOBs can only be created in a tablespace managed with 
Automatic Segment Space Management (ASSM).

SecureFiles is the default storage mechanism for LOBs starting with 
Oracle Database 12c, and oracle strongly recommends SecureFiles for 
storing and managing LOBs, rather then BasicFiles. BasicFiles will be 
deprecated in a future release.

Large Objects are used to hold large amounts of data inside Oracle 
Database, SecureFiles provides performance equal to or better than file
system performance when using Oracle Database to store and manage Large
Objects, and DBFS provides file system access to files stored in Oracle
Database


-------------------------
Checking Default Behavior
-------------------------
With Oracle 12c, LOBs are now stored as SecureFiles per default, 
and the init parameter DB_SECUREFILE has changed:

The new PREFERRED value is now the default, if the COMPATIBLE init 
parameter is set to 12.0.0.0 or higher. PREFERRED makes LOBs to be 
stored as SecureFiles per default, unless the BASICFILE clause is 
explicitely used when creating the table, or the tablespace is not 
ASSM. The ALWAYS value still forces the storage as SecureFiles, 
however, LOBs are stored as BasicFile if the tablespace is not ASSM, 
instead of raising an error. BasicFile is not banned.


SQL> show parameter db_secure

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PREFERRED
SQL>
SQL> show parameter compat

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
plsql_v2_compatibility boolean FALSE

SQL> connect test/test@celvpvm13281.us.oracle.com:1522/pdb1
Connected.
SQL>
SQL> create table tbl_lob_test (id number,lob_data clob);

Table created.

SQL> select securefile from user_lobs where table_name='TBL_LOB_TEST';

SEC
---
YES

---------------------------------------
Test with DB_SECUREFILE NEVER / ALWAYS 
---------------------------------------
SQL> ALTER SESSION SET DB_SECUREFILE=NEVER;

Session altered.

SQL> CREATE TABLE PRINT_MEDIA_TEST2 (
PRODUCT_ID NUMBER (6),
AD_ID NUMBER (6) ,
AD_COMPOSITE BLOB,
AD_SOURCETEXT CLOB );
2 3 4 5
Table created.

SQL> ALTER SESSION SET DB_SECUREFILE=ALWAYS;

Session altered.

SQL> CREATE TABLE PRINT_MEDIA_TEST3(
PRODUCT_ID NUMBER (6),
AD_ID NUMBER (6) ,
AD_COMPOSITE BLOB,
AD_SOURCETEXT CLOB ); 2 3 4 5

Table created.

SQL> COL COLUMN_NAME FORMAT A20 ;
SELECT COLUMN_NAME , SecureFile , ENCRYPT , DEDUPLICATION , 
COMPRESSION FROM USER_LOBS WHERE TABLE_NAME = 'PRINT_MEDIA_TEST2';

COLUMN_NAME SEC ENCR DEDUPLICATION COMPRE
-------------------- --- ---- --------------- ------
AD_COMPOSITE NO NONE NONE NONE
AD_SOURCETEXT NO NONE NONE NONE

SQL> SELECT COLUMN_NAME , SecureFile , ENCRYPT , DEDUPLICATION , 
COMPRESSION FROM USER_LOBS WHERE TABLE_NAME = 'PRINT_MEDIA_TEST3';

COLUMN_NAME SEC ENCR DEDUPLICATION COMPRE
-------------------- --- ---- --------------- ------
AD_COMPOSITE YES NO NO NO
AD_SOURCETEXT YES NO NO NO

----------------
Playing with LOB
----------------

SQL> create table tbl_basicfile_lob (id number,lob_data clob) 
     lob(lob_data) store as basicfile;

Table created.

SQL> r
1 create table tbl_securefile_lob (id number,lob_data clob) 
2 lob(lob_data) store as securefile
3* tablespace example

Table created.

SQL> desc tbl_basicfile_lob
Name Null? Type
----------------------------------------- -------- 
ID NUMBER
LOB_DATA CLOB

SQL> desc tbl_securefile_lob
Name Null? Type
----------------------------------------- -------- 
ID NUMBER
LOB_DATA CLOB

SQL> insert into tbl_basicfile_lob values(1,'This is my long text');

1 row created.

SQL> c/basic/secure
1* insert into tbl_securefile_lob values(1,'This is my long text')
SQL> r
1* insert into tbl_securefile_lob values(1,'This is my long text')

1 row created.

SQL> commit;

Commit complete.

set linesize 150
col column_name format a10
select table_name,column_name,segment_name,securefile from user_lobs 
where table_name like 'TBL%';

TABLE_NAME COLUMN_NAM SEGMENT_NAME SEC
------------ --------------------------------------------------------
TBL_BASICFILE_LOB LOB_DATA SYS_LOB0000092743C00002$ NO
TBL_SECUREFILE_LOB LOB_DATA SYS_LOB0000092749C00002$ YES

--------
Compress
--------
This feature is used to compress LOB Segment.
A table compression doesn't affect the LOB compression.
It means that if table is compressed, the LOB Segement will not be 
compressed automatically.

Compression level
MEDIUM and HIGH (Onwards 11gR1)
LOW (Onwards 11gR2)

SQL> create table tbl_lob_nocompress (id number,lob_data clob) 
    lob(lob_data) store as securefile (tablespace example nocompress);

Table created.

SQL> r
1* create table tbl_lob_compress (id number,lob_data clob) 
   lob(lob_data) store as securefile (tablespace example compress)

Table created.


SQL> alter table tbl_lob_compress modify lob(lob_data)(compress high);

Table altered.

SQL> r
1 INSERT INTO TBL_LOB_NOCOMPRESS
2* SELECT OBJECT_ID,RPAD(' ',5000,OBJECT_NAME) FROM 
USER_OBJECTS WHERE ROWNUM < 5000

34 rows created.

SQL> r
1 INSERT INTO TBL_LOB_COMPRESS
2* SELECT OBJECT_ID,RPAD(' ',5000,OBJECT_NAME) 
FROM USER_OBJECTS WHERE ROWNUM < 5000

34 rows created.

SQL> commit;

Commit complete.

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');

PL/SQL procedure successfully completed.

SQL> select table_name,segment_name,securefile,compression 
from user_lobs where table_name like '%COMPRESS%';

TABLE_NAME SEGMENT_NAME SEC COMPRE
-------------------------------------------------------- --- ------
TBL_LOB_COMPRESS SYS_LOB0000092756C00002$ YES HIGH
TBL_LOB_NOCOMPRESS SYS_LOB0000092753C00002$ YES NO


SQL> column segement_name format a30
SQL> select segment_name,bytes from user_segments where segment_name 
IN ('SYS_LOB0000092756C00002

Thank you for visiting this blog :)
,'SYS_LOB0000092753C00002

Thank you for visiting this blog :)
);

SEGMENT_NAME BYTES
----------------------------- ----------
SYS_LOB0000092753C00002$ 1245184
SYS_LOB0000092756C00002$ 131072

SQL>

-----------
Encryption
-----------
You can enable block level encryption for LOB segments using ENCRYPT 
USING 'encryption_algorithm' syntaxt.
If you want to prevent the encryption explicitly, use DECRYPT option.

Securefile supports 4 encryption algorithms
3DES168,AES128,AES192,AES256.


-bash-4.1$ ls /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/
ewallet_2019090404252964_elite123.p12 ewallet_2019090404391794.p12 ewallet.p12
-bash-4.1$
-bash-4.1$ cat /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/wallets/$ORACLE_SID)))


-bash-4.1$ sql
SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS
------------------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
CLOSED
/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> SHOW PDBS

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 READ WRITE NO
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY 
     oracle_4U container=ALL;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

SQL> alter session set container=pdb2;

Session altered.

SQL> connect PM/PM@racnode2/pdb2
Connected.
SQL> create table tbl_lob_encrypt (id number,lob_data clob) 
lob(lob_data) store as securefile (tablespace example encrypt using 
'AES128');
create table tbl_lob_encrypt (id number,lob_data clob) lob(lob_data) store as securefile (tablespace example encrypt using 'AES128')
*
ERROR at line 1:
ORA-28361: master key not yet set


SQL> !oerr ora 28361
28361, 0000, "master key not yet set"
// *Cause: The master key for the instance was not set.
// *Action: Execute the ALTER SYSTEM SET KEY command to set a 
   master key
// for the database instance.


SQL> connect sys/oracle_4U@racnode2/pdb2 as sysdba
Connected.
SQL> select key_id,con_id from v$encryption_keys;

no rows selected

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle_4U 
     WITH BACKUP;

keystore altered.

SQL> select key_id,con_id,CREATOR_PDBNAME from v$encryption_keys;

KEY_ID CON_ID CREATOR_PDBNAME
---------- ----------------------------------------------------------------
AatnbuJAJU92vyFZKfs22qAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 0 PDB2

SQL> connect PM/PM@racnode2/pdb2
Connected.
SQL> create table tbl_lob_encrypt (id number,lob_data clob) 
lob(lob_data) store as securefile (tablespace example 
encrypt using 'AES128');

Table created.

SQL> desc tbl_lob_encrypt
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
LOB_DATA CLOB ENCRYPT

------------- 
Deduplication
-------------
Using deduplication feature, you eliminate multiple copies of securefiles 
data. When you enable this feature, Oracle will automatically detect 
identical Securefile data and will store only one copy. Use deduplicate 
clause to turn on this feature.


SQL>
SQL> create table tbl_lob_duplicate (id number,lob_data clob) 
lob(lob_data) store as securefile (tablespace example keep_duplicates);

Table created.

SQL> create table tbl_lob_deduplicate( id number,lob_data clob) 
lob(lob_data) store as securefile (tablespace example deduplicate);

Table created.

SQL> insert into tbl_lob_duplicate
2 select object_id,rpad(' ',5000,'This is test ') 
from user_objects where rownum <3000;

2999 rows created.

SQL> r
1 insert into tbl_lob_deduplicate
2* select object_id,rpad(' ',5000,'This is test ') 
from user_objects where rownum <3000

2999 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(USER,'TBL_LOB_DUPLICATE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(USER,'TBL_LOB_DEDUPLICATE');

PL/SQL procedure successfully completed.

SQL> r
1* select table_name,segment_name,securefile,deduplication 
from user_lobs where table_name like '%DUPLICATE%'

TABLE_NAME SEGMENT_NAME SEC DEDUPLICATION
--- ----------------------------------------------------------------
TBL_LOB_DEDUPLICATE SYS_LOB0000092768C00002$ YES LOB
TBL_LOB_DUPLICATE SYS_LOB0000092765C00002$ YES NO


SQL> column segment_name format a30
SQL> select segment_name,bytes from user_segments 
where segment_name 
in ('SYS_LOB0000092768C00002

Thank you for visiting this blog :)
,'SYS_LOB0000092765C00002

Thank you for visiting this blog :)
);

SEGMENT_NAME BYTES
------------------------------ ----------
SYS_LOB0000092765C00002$ 28508160
SYS_LOB0000092768C00002$ 1245184

SQL>

-------
Caching
-------
Witch caching feature you can bring the LOB segment to the buffer cache.
You have to think twice before enabling caching option for LOB segements,
because it can flush necessary block from the buffer cache and can affect
the performance of the database.

To put LOB Segement to the cache, use the CACHE clause as follows:

SQL> create table tbl_lob_cache ( lob_data clob ) lob(lob_data) store as
 securefile (tablespace example cache);

Table created.

SQL> alter table tbl_lob_cache modify lob (lob_data) (nocache);

Table altered.

SQL> r
1* alter table tbl_lob_cache modify lob (lob_data) (cache)

------- 
Logging
------- 
Using a logging feature, you can specify how the LOBs are recorded to the 
redo log stream. 
The default value is LOGGING which means that all insert in the LOB will 
be logged to the redo log file.
To disable it, use NOLOGGING clause. You can create a LOB Segment with 
NOLOGGING option as follows:

SQL> create table tbl_lob_nologging (id number,clob_data clob) 
lob (clob_data) store as securefile (tablespace example nocache nologging);

Table created.

SQL> desc tbl_lob_nologging
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
CLOB_DATA CLOB

#Migration of Securefiles Lob to basicfile Lob.

SQL> show user
USER is "TEST"

SQL> CREATE TABLE TBL_BASIC_LOB (ID NUMBER,LOB_DATA CLOB) 
lob(lob_data) store as basicfile;

Table created.

SQL> select securefile from user_lobs where table_name='TBL_BASIC_LOB';

SEC
---
NO

SQL> connect / as sysdba
Connected.

SQL> alter session set container=pdb1;

Session altered.

SQL> INSERT INTo test.TBL_BASIC_LOB
2 SELECT OBJECT_ID,RPAD(' ',5000,'This is test ') 
from dba_objects where rownum < 3000;

2999 rows created.

SQL> commit;

Commit complete.

SQL> connect test/test@celvpvm13281.us.oracle.com:1522/pdb1
Connected.
SQL>
SQL> CREATE TABLE TBL_SECUREFILE_LOB (ID NUMBER,LOB_DATA CLOB) ;

Table created.

SQL> SELECT SECUREFILE FROM USER_LOBS 
WHERE TABLE_NAME='TBL_SECUREFILE_LOB';

SEC
---
YES

SQL> select count(*) from tbl_securefile_lob;

COUNT(*)
----------
0

SQL> select count(*) from tbl_basic_lob;

COUNT(*)
----------
2999

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TBL_BASIC_LOB',2);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('TEST','TBL_BASIC_LOB',
'TBL_SECUREFILE_LOB',NULL,2);

PL/SQL procedure successfully completed.


SQL> run
1 DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => 'TEST',
6 orig_table => 'TBL_BASIC_LOB',
7 int_table => 'TBL_SECUREFILE_LOB',
8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
9 copy_triggers => TRUE,
10 copy_constraints => TRUE,
11 copy_privileges => TRUE,
12 ignore_errors => TRUE,
13 num_errors => num_errors);
14* END;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST','TBL_BASIC_LOB',
'TBL_SECUREFILE_LOB');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TBL_BASIC_LOB',
'TBL_SECUREFILE_LOB');

PL/SQL procedure successfully completed.

SQL> show user
USER is "TEST"
SQL>
SQL> SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME='TBL_SECUREFILE_LOB';

SEC
---
NO

SQL> SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME='TBL_BASIC_LOB';

SEC
---
YES

SQL> SELECT COUNT(*) FROM TBL_BASIC_LOB;

COUNT(*)
----------
2999

SQL> SELECT COUNT(*) FROM TBL_SECUREFILE_LOB;

COUNT(*)
----------
2999

SQL> ALTER TABLE TBL_BASIC_LOB MODIFY LOB (LOB_DATA) 
(CACHE COMPRESS HIGH DEDUPLICATE);

Table altered.

You can also use DataPump to migrate the Securelob to basiclob.
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9536876500346658226


[2] Create and manage LOB segments

A LOB can be internal or external. In addition, an internal LOB may be 
persistent or temporary.

-> Internal Persistent LOB: A LOB instance that exists within a row in a 
   table
-> Internal Temporary LOB: A LOB instance that exists only in the context
   of our application
-> External LOB: LOB objects that are stored outside the database to 
   which is accessed through a BFILE

We also introduce two concepts that are the locator (Locator) and value 
VALUE) of a LOB. The locator is a reference to the physical location of 
the LOB. While its value is the data set having said LOB. This is useful 
when using the procedures in DBMS_LOB package.

Data types that can handle at the table level are as follows:

· BLOB: LOB stored in binary format
· CLOB: LOB containing characters in the character set of the database
· NCLOB: LOB storing characters from national character set of the database
· BFILE: external binary file read-only in our application

The states that can have a column LOB (BLOB,CLOB or NCLOB) are as follows

· NULL: There is a record in the database but the field has LOB locator
· EMPTY: A locator in the register but has no value (data)
· Populated (Town): There is a locator and a value in the register

To work with LOB fields there are several APIs that allow us to perform 
operations on them. 
We will be using the DBMS_LOB package in PL/SQL but there is an API in 
OCI and Java to manipulate.

Checking default LOB parameters in 12.1.0.2

SQL> CREATE TABLE TBL_LOB_TABLE (ID NUMBER,TEXT CLOB,PHOTO BLOB,
INTERN_FILE BFILE);

Table created.

SQL> set long 100000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE') from dual;

CREATE TABLE "TEST"."TBL_LOB_TABLE"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS SECUREFILE (
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )
LOB ("PHOTO") STORE AS SECUREFILE (
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )

SQL> CREATE TABLE TBL_LOB_TABLE2 (ID NUMBER,TEXT CLOB,PHOTO BLOB,
     INTERN_FILE BFILE) LOB(TEXT,PHOTO) STORE AS BASICFILE;

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE2') from dual;

CREATE TABLE "TEST"."TBL_LOB_TABLE2"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS BASICFILE (
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )
LOB ("PHOTO") STORE AS BASICFILE (
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))


create table tbl_lob_table_2
(id number,text clob,photo blob,intern_file bfile) lob (text) 
 store as text_lob_segment 
(tablespace users 
chunk 4096 
cache logging 
storage (initial 100 K NEXT 100 K PCTINCREASE 0) PCTVERSION 10 FREEPOOLS 5)
lob (photo) store as photo_lob_segment ( 
tablespace users 
chunk 4096 
nocache logging 
storage (initial 50 K NEXT 50 K PCTINCREASE 5) PCTVERSION 20 FREEPOOLS 20);

SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE_2') FROM DUAL;

CREATE TABLE "TEST"."TBL_LOB_TABLE_2"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS SECUREFILE "TEXT_LOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
CACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE( INITIAL 106496 NEXT 106496
PCTINCREASE 0))
LOB ("PHOTO") STORE AS SECUREFILE "PHOTO_LOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE( INITIAL 57344 NEXT 57344
PCTINCREASE 5))

SQL> create table tbl_lob_table_3
(id number,text clob,photo blob,intern_file bfile) lob (text) 
 store as basicfile text_basiclob_segment
(tablespace users
chunk 4096
cache logging
storage (initial 100 K NEXT 100 K PCTINCREASE 0) PCTVERSION 10 FREEPOOLS 5)
lob (photo) store as basicfile photo_basiclob_segment (
tablespace users
chunk 4096
nocache logging
storage (initial 50 K NEXT 50 K PCTINCREASE 5) PCTVERSION 20 FREEPOOLS 20); 4 5 6 7 8 9 10 11

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE_3') FROM DUAL;

CREATE TABLE "TEST"."TBL_LOB_TABLE_3"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS BASICFILE "TEXT_BASICLOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE( INITIAL 106496 NEXT 106496
PCTINCREASE 0))
LOB ("PHOTO") STORE AS BASICFILE "PHOTO_BASICLOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 57344 NEXT 57344
PCTINCREASE 5))

OR

SQL> alter session set db_securefile=never;

Session altered.

SQL> create table tbl_lob_table_5
(id number,text clob,photo blob,intern_file bfile) lob (text) store as 
 text_lob_segment
(tablespace users
chunk 4096
cache logging
storage (initial 100 K NEXT 100 K PCTINCREASE 0) PCTVERSION 10 FREEPOOLS 5)
lob (photo) store as photo_lob_segment (
tablespace users
chunk 4096
nocache logging
storage (initial 50 K NEXT 50 K PCTINCREASE 5) PCTVERSION 20 FREEPOOLS 20);
2 3 4 5 6 7 8 9 10 11
Table created.

SQL> set long 10000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE_5') FROM DUAL;

CREATE TABLE "PM"."TBL_LOB_TABLE_5"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "EXAMPLE"
LOB ("TEXT") STORE AS BASICFILE "TEXT_LOB_S
EGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 81
92 PCTVERSION 10
CACHE
STORAGE( INITIAL 106496 NEXT 106496
PCTINCREASE 0))
LOB ("PHOTO") STORE AS BASICFILE "PHOTO_LOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW
CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 57344 NEXT 57344
PCTINCREASE 5))


#Modify Lob parameters 
SQL> alter table tbl_lob_table_3 modify lob(text)
( nocache pctversion 20 freepools 10);

Table altered.

SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE_3') FROM DUAL;

CREATE TABLE "TEST"."TBL_LOB_TABLE_3"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS BASICFILE "TEXT_BASICLOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 106496 NEXT 106496
PCTINCREASE 0))
LOB ("PHOTO") STORE AS BASICFILE "PHOTO_BASICLOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 57344 NEXT 57344
PCTINCREASE 5))

#To move a lob segment to the different tablespace

SQL> alter table test.tbl_lob_table_3 move lob(photo) 
    store as (tablespace TEST);

Table altered.

SQL> select dbms_metadata.get_ddl('TABLE','TBL_LOB_TABLE_3') FROM DUAL;

CREATE TABLE "TEST"."TBL_LOB_TABLE_3"
( "ID" NUMBER,
"TEXT" CLOB,
"PHOTO" BLOB,
"INTERN_FILE" BFILE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST"
LOB ("TEXT") STORE AS BASICFILE "TEXT_BASICLOB_SEGMENT"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 106496 NEXT 106496
PCTINCREASE 0))
LOB ("PHOTO") STORE AS BASICFILE "PHOTO_BASICLOB_SEGMENT"(
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 20
NOCACHE LOGGING
STORAGE( INITIAL 57344 NEXT 57344
PCTINCREASE 5))

#Inserting data into LOB Column.

SQL> desc tbl_lob_table_2
Name Null? Type
----------------------------------------- --------
ID NUMBER
TEXT CLOB
PHOTO BLOB
INTERN_FILE BINARY FILE LOB

SQL> INSERT INTO TBL_LOB_TABLE_2 (ID ,TEXT) VALUES(1,'This isthe LOB text');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tbl_lob_table_2;
1
This isthe LOB text

bfilename(NULL)

SQL> create table tbl_lob_test_4
2 (id number,
3 text clob default empty_clob(),
4 photo blob default empty_blob()
5 );

Table created.

SQL> desc tbl_lob_test_4
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TEXT CLOB
PHOTO BLOB

SQL> INSERT INTO TBL_LOB_TEST_4
2 VALUES(1,'This is test',EMPTY_BLOB());

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TBL_LOB_TEST_4;
1
This is test


==================================
Using DBMS_LOB package subprograms
==================================
Documentation:
Database SecureFiles and Large Objects Developer's Guide 
-->22 Using LOB APIs
-->Loading a CLOB and NCLOB with Data from a BFILE

Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
==> 82 DBMS_LOB

Database SecureFiles and Large Objects Developer's Guide
-> A LOB Demonstration Files

lldclobf.sql
Using DBMS_LOB.LOADCLOBFROMFILE to load a CLOB or NCLOB with data from 
a BFILE

SQL> CREATE TABLE TBL_LOB_TEST03
2 (ID NUMBER,TEXT CLOB DEFAULT EMPTY_CLOB(),PHOTO BLOB DEFAULT 
   EMPTY_BLOB());

Table created.

SQL> INSERT INTO TBL_LOB_TEST03
2 VALUES (1,'This is text by Hitesh',EMPTY_BLOB());

1 row created.

SQL> COMMIT;

Commit complete.

SQL> !2.sql
/bin/ksh: 2.sql: not found

SQL> !pwd
/refresh/home/lob_Demo

SQL> !ls
2.sql lldclobf.sql lob.dat

SQL> !cat 2.sql
DECLARE
dst_loc clob;
src_loc bfile := bfilename('MY_DIR','lob.dat');
amt number := dbms_lob.lobmaxsize;
src_offset number := 1;
dst_offset number := 1;
lang_ctx number := dbms_lob.default_lang_ctx;
warning number;
BEGIN
DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------');
dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
dbms_lob.createtemporary(dst_loc,true);

/* The default_csid can be used when the BFILE encoding is in the same 
charset as destination CLOB/NCLOB charset */

dbms_lob.LOADCLOBFROMFILE(dst_loc, src_loc, amt, dst_offset,src_offset,
dbms_lob.default_csid, lang_ctx, warning);

dbms_output.put_line(' Amount specified ' || amt ) ;
dbms_output.put_line(' Number of bytes read from source: ' ||
(src_offset-1));
dbms_output.put_line(' Number of characters written to destination: '||
(dst_offset-1) );
if (warning = dbms_lob.warn_inconvertible_char)
then
dbms_output.put_line('Warning: Inconvertible character');
end if;

insert into tbl_lob_test03 values(1,dst_loc,empty_blob());

if (warning = dbms_lob.warn_inconvertible_char)
then
dbms_output.put_line('Warning: Inconvertible character');
end if;

dbms_lob.filecloseall() ;

END;
/

SQL> SET SERVEROUTPUT ON
SQL> @2.sql
------------ LOB LOADCLOBFORMFILE EXAMPLE ------------
Amount specified 18446744073709551615
Number of bytes read from source: 70
Number of characters written to destination: 70

PL/SQL procedure successfully completed.

SQL> select * from TBL_LOB_TEST03;

ID TEXT PHOTO
--------------------------------------------------------------------------------
1 This is text by Hitesh
1 This is the first line 
This is the second line
This is the third line

==============
DBMS_LOB.INSTR
==============
INSTR function is used to return a position of the nth occurrence of the 
pattern in the LOB.

SQL> !vi 3.sql

SQL> !cat 3.sql
DECLARE
dst_loc clob;
src_loc bfile := bfilename('MY_DIR','lob.dat');
amt number := dbms_lob.lobmaxsize;
src_offset number := 1;
dst_offset number := 1;
lang_ctx number := dbms_lob.default_lang_ctx;
warning number;
position number;
BEGIN
dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
dbms_lob.createtemporary(dst_loc,true);

dbms_lob.LOADCLOBFROMFILE(dst_loc, src_loc, amt, dst_offset, src_offset,
dbms_lob.default_csid, lang_ctx, warning);

dbms_output.put_line(dst_loc);
position:=dbms_lob.instr(dst_loc,'second',1,1);
dbms_output.put_line(position);

dbms_lob.filecloseall() ;

END;
/

SQL> @3.sql
This is the first line
This is the second line
This is the third line

36

PL/SQL procedure successfully completed.

===============
DBMS_LOB.SUBSTR
===============
It is used to get substring from the LOB data.


SQL> !cat lob-substr.sql'
DECLARE
dst_loc clob;
src_loc bfile := bfilename('MY_DIR','lob.dat');
amt number := dbms_lob.lobmaxsize;
src_offset number := 1;
dst_offset number := 1;
lang_ctx number := dbms_lob.default_lang_ctx;
warning number;
substring varchar2(100);
BEGIN
dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
dbms_lob.createtemporary(dst_loc,true);

dbms_lob.LOADCLOBFROMFILE(dst_loc, src_loc, amt, dst_offset, src_offset,
dbms_lob.default_csid, lang_ctx, warning);

dbms_output.put_line(dst_loc);
substring:=dbms_lob.substr(dst_loc,5,13);
dbms_output.put_line(substring);

dbms_lob.filecloseall() ;

END;
/

SQL> @lob-substr.sql
This is the first line
This is the second line
This is the third line

first

PL/SQL procedure successfully completed.


================
DBMS_LOB.COMPARE
================
This function is used to compare two LOBs.
If matched it returns o else nonzero

SQL> !vi lob-compare.sql

SQL> !cat lob-compare.sql
DECLARE
v_clob1 clob := 'This is a clob data';
v_clob2 clob := 'This is a clob data';
amt number := DBMS_LOB.LOBMAXSIZE;
result NUMBER;
BEGIN
result := DBMS_LOB.COMPARE(v_clob1,v_clob2,5,1,1);
if result = 0 then
dbms_output.put_line('Both lobs are same');
else
dbms_output.put_line('Contents of lobs are different');
end if;
END;
/

SQL> @lob-compare.sql
Both lobs are same

PL/SQL procedure successfully completed.

==================
DBMS_LOB.GETLENGTH
==================
This function returns the length of the LOB.

SQL> !vi lobgetlen.sql

SQL> !cat lobgetlen.sql
DECLARE
v_clob1 clob := 'This is a CLOB data';
result number;
BEGIN
result := DBMS_LOB.getlength(v_clob1);
DBMS_OUTPUT.PUT_LINE(result);
END;
/

SQL> @lobgetlen.sql
19

PL/SQL procedure successfully completed.

=====================
DBMS_LOB.LOADFROMFILE
=====================
LOADFROMFILE procedure loads a data from BFILE to internal LOB Segment.

=========================================
DBMS_LOB.ISOPEN and DBMS_LOB.FILECLOSEALL
=========================================
Use OPEN procedure to open a LOB in either read-only or read-write mode.
FILECLOSEALL procedure is used to close an opened BFILE.

==============================
DBMS_LOB.WRITE and WRITEAPPEND 
==============================
To write specific amount of data to the LOB
write procedure is used.
To write a data to the end of the LOB,use WRITEAPPEND procedure.

==================================================
DBMS_LOB.CREATETEMPORARY,FREETEMPORARY,ISTEMPORARY 
==================================================
CREATETEMPORARY function is used to create a temporary LOB in the temporary tablespace.
As a value for duration paramter you can set either SESSION or CALL to cleanup the LOB
segment at the end of the session or at the end of the call. SESSION is the default value.

Use FREETEMPORARY procedure to free the temporary CLOB and the lob locator associated 
with the temporary LOB will be marked as invalid.

ISTEMPORARY function is used to check if a lob locator points to the temporary or persistent LOB.

v$temporary_lobs : To monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

SQL> !cat lob-temp.sql
DECLARE
temp_lob clob;
v_text VARCHAR2(100):='This is a test message';
v_text2 VARCHAR2(100):='This is a second message';
BEGIN

/* 1. Create a temporary LOB using CREATETEMPORARY proc
2. Check if it is temporary LOB using ISTEMPORARY fun
3. Write a data in it using both WRITE and WRITEAPPEND procs
4. Use FREETEMPORARY proc to free up the the temporary LOB */

DBMS_LOB.createtemporary(temp_lob,true,dbms_lob.session);

IF DBMS_LOB.istemporary(temp_lob) = 1
THEN
DBMS_OUTPUT.put_line('This LOB is temporary');
ELSE
DBMS_OUTPUT.put_line('This LOB is NOT temporary');
END IF;

DBMS_LOB.OPEN(temp_lob,DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITE(temp_lob,length(v_text),1,v_text);

DBMS_LOB.writeappend(temp_lob,length(v_text2),v_text2);
DBMS_OUTPUT.put_line(temp_lob);
DBMS_OUTPUT.put_line('The length of the temporary LOB is :'|| DBMS_LOB.getlength(temp_lob));
DBMS_LOB.close(temp_lob);
DBMS_LOB.freetemporary(temp_lob);
END;
/

SQL> @lob-temp.sql
This LOB is temporary
This is a test messageThis is a second message
The length of the temporary LOB is :46

PL/SQL procedure successfully completed.

=============
DBMS_LOB.COPY
=============
This procedure copies all, or a part of, a source internal LOB to a destination internal LOB. 
You can specify the offsets for both the source and destination LOBs, and the number of bytes 
or characters to copy.


connect PM/PM@racnode2/pdb2

CREATE TABLE PRINT_MEDIA_TEST (
PRODUCT_ID NUMBER (6),
AD_ID NUMBER (6),
AD_COMPOSITE BLOB,
AD_SOURCETEXT CLOB)
TABLESPACE USERS
LOB (AD_COMPOSITE) STORE AS SecureFile (
CACHE
ENCRYPT
COMPRESS
DEDUPLICATE)
LOB (AD_SOURCETEXT) STORE AS SecureFile (
CACHE
ENCRYPT
COMPRESS
DEDUPLICATE);

- We can change the attributes of the SecureFile LOB columns after table creation
ALTER TABLE PRINT_MEDIA_TEST MODIFY LOB (AD_COMPOSITE) (KEEP_DUPLICATES);
ALTER TABLE PRINT_MEDIA_TEST MODIFY LOB (AD_COMPOSITE) (nocompress);
ALTER TABLE PRINT_MEDIA_TEST MODIFY LOB (AD_COMPOSITE) (DECRYPT);

SQL> TRUNCATE TABLE PRINT_MEDIA_TEST;

Table truncated.

SQL> INSERT INTO PRINT_MEDIA_TEST VALUES (2056, 12001, EMPTY_BLOB (), EMPTY_CLOB ());
COMMIT;

1 row created.

SQL>
Commit complete.

SQL> SELECT AD_SOURCETEXT FROM PRINT_MEDIA_TEST WHERE PRODUCT_ID = 2056 AND AD_ID = 12001;

AD_SOURCETEXT
--------------------------------------------------------------------------------

SQL> DECLARE
ad_sourcetext_src CLOB;
ad_sourcetext_dest CLOB;
BEGIN
SELECT AD_SOURCETEXT INTO ad_sourcetext_src FROM print_media
WHERE product_id = 2056 and ad_id = 12001;
SELECT AD_SOURCETEXT INTO ad_sourcetext_dest FROM Print_media_test
WHERE product_id = 2056 and ad_id = 12001 FOR UPDATE;
DBMS_LOB.COPY (
ad_sourcetext_dest,
ad_sourcetext_src,
length (ad_sourcetext_src),
1,
1);
COMMIT;
END;
/
14 15 16 17
PL/SQL procedure successfully completed.

SQL> SELECT AD_SOURCETEXT FROM PRINT_MEDIA_TEST WHERE PRODUCT_ID = 2056 AND AD_ID = 12001;

AD_SOURCETEXT
--------------------------------------------------------------------------------
TIGER2 Mousepad
Product Number: 2056 Price: $8 Today's
Sale Price! $4

Thi

[3] Lob Space Management using DBMS_SPACE

------------------------------------------------------------------------------------
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 
[Release 11.2]
------------------------------------------------------------------------------------
How to determine the actual size of the LOB segments and how to free the 
deleted/unused space above/below the HWM (Doc ID 386341.1)

Some hints about how to check the actual size of a LOB segment including 
the free and available space, above and below the HWM, including the 
commands to free the space above the HWM.
------------------------------------------------------------------------------------

The size of the LOB segment can be found by querying dba_segments, as 
follows:

SELECT TABLE_NAME,SEGEMENT_NAME FROM USER_LOBS 
WHERE TABLE_NAME='<TABLE_NAME>';

SELECT BYTES FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME ='<LOB SEGMENT NAME>' 
AND OWNER ='<TABLE OWNER>';

To get the details of the table to which this LOB segment belong to:

SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS 
WHERE OWNER = '<owner>' AND SEGMENT_NAME= '<lob segment name>' ;

The first thing to do would be to check the space (in bytes) that is 
actually allocated to the LOB data.

SELECT SUM(DBMS_LOB.GETLENGTH (<LOB COLUMN NAME>)) FROM <TABLE_NAME>;

P.S: When using dbms_lob.getlength, the output is in characters for 
CLOBs and NCLOBs,and in bytes for BLOBS and BFILES.

Please note that the UNDO data for a LOB segment is kept within the 
LOB segment space.
The above query result is merely the still active LOB data.
The rest of allocated data is undo space.

The undo space can vary quite a lot, from being very small in size 
(when LOBs are only inserted)to being very large (when many LOBs are 
deleted) and is largely depending on the PCTVERSION 
LOB parameter or the RETENTION parameter.

Hence, The difference between these two is free space and/or undo space. 
It is not possible to assess the actual empty space using the queries 
above alone, because of the UNDO segment size, which is virtually
impossible to assess. Furthermore, even when there is free space in the 
LOB, this does not mean this space can be released to the tablespace, 
it could be under the HWM. To find the freeable space, use the 
UNUSED_SPACE procedure as shown below.

Check the "free" space within a LOB segment.
First, remember that a lob can have 3 states: "inuse", "deleted" and 
"free".

There are no statistics for the LOB segment, the DBMS_SPACE package is 
the only tool that could give an idea about it. As well, there is no
view showing the deleted space within the LOB.

The deleted space can be converted into free by rebuilding the freelist 
or by rebuilding the LOB segment itself, but this is not always possible.

Note:
LOB Partition sizing is not supported in DBMS_SPACE package until 10g.

One can get an idea about how much space is actually used and what could 
be deallocated as follows:
1. Determine the unused space within the LOB segment, above the HWM. 
using the UNUSED_SPACE procedure.

This procedure returns information about unused space in an object 
(table, index, or cluster).

SET SERVEROUTPUT ON

declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;

begin
dbms_space.unused_space('<owner>','<lob segment name>','LOB',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);

dbms_output.put_line('SEGMENT_NAME = <LOB SEGMENT NAME>');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);

end;
/

This is the only space that could be deallocated using the 
ALTER TABLE ... DEALLOCATE UNUSED command as seen below.

2.2 SPACE_USAGE procedure could be used for ASSM segments instead:

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('<owner>', '<lob segment name>', 'LOB', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

3. The command used to deallocate the lob free space is:

alter table <table name> modify lob (<lob column name>) 
(deallocate unused);

This is not very useful in most circumstances. There is probably very 
little space above 
the high water mark. On top of this, the deleted space from inside the 
the lob segment is not even shown by the procedures above. This is the 
expected behaviour and, unfortunately, currently there is no procedure/
view to show the deleted space. Having such an option is the current 
subject of an enhancement request.

- 10.2 introduced an even better option, it extended the SHRINK SPACE 
command to LOBs. 
As such, one can remove the deleted and free space altogether from the 
LOB segment and LOB index:

alter table <table name> modify lob(<lob column name>) (shrink space 
[cascade]);

[4] Shrink a LOB
-> How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? 
(Doc ID 1451124.1)

4.2 Shrink a BASICFILE LOB.

1) ALTER TABLE ... SHRINK SPACE

The most effective method is to store the data in an ASSM (Automatic 
Segment Space Managed) tablespace and use the SHRINK SPACE command

This command can either shrink just the LOB itself .. or can shrink the 
LOB and Table and Indexes at the same time

PROs: This method is simple and very effective
This method does not require additional storage space

CONs: This method locks the table and will cause an outage
This method is restricted as defined in the Oracle® Database SQL Language
 Reference ... ALTER TABLE command
SECUREFILE LOBS may not use this method

4.2 Shrink a SECUREFILE LOB using DBMS_REDEFINITION

2) DBMS_REDEFINITION

Another very effective method is to recreate the table using 
DBMS_REDEFINITION to recreate the table while it is online

Examples of this process can be seen in
How to Shrink a SECUREFILE LOB Using Online Redefinition 
(DBMS_REDEFINITION)? (Document 1394613.1)
*** NOTE .. this process may also be used for BASICFILE lobs ***

HOW TO SHRINK A TABLE USING ONLINE REDEFINITION (Document 1357878.1)

PROs: This method leaves the table online except for a short period 
during DBMS_REDEFINITION.FINISH_REDEF_TABLE

CONs: This method will require additional space not required by other
 methods (up to double the size of the size of the original table .. to 
store the interim table) This method is restricted as defined in the 
online documentation for Restrictions on DBMS_REDEFINITION This method 
is complicated

From MOS (Doc ID 1357878.1) 
-> How to Shrink a SECUREFILE Table

create table ORIGINAL (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));

Table created.

SQL> declare
v_out varchar2(1000);
begin
v_out := null;
for i in 1..1000 loop
v_out := v_out||'A';
end loop;
for i in 1..10000 loop
insert into ORIGINAL values (i,v_out,v_out,v_out);
if i/10000 = trunc(i/1000) then
commit;
end if;
end loop;
commit;
end;
/ 
PL/SQL procedure successfully completed.

SQL> delete from ORIGINAL where (COL1/3) <> trunc(COL1/3);

6667 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*), SEGMENT_NAME FROM USER_EXTENTS GROUP BY SEGMENT_NAME;

COUNT(*) SEGMENT_NAME
--------------------------------------------------------------------------------
40 ORIGINAL


SQL> SET SERVEROUTPUT ON
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END; 
/

PL/SQL procedure successfully completed.

SQL>
create table INTERIM (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));
SQL> 
Table created.

SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST',
orig_table => 'ORIGINAL',
int_table => 'INTERIM',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

SQL> DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST', 'ORIGINAL', 'INTERIM',
 dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/ 
errors := 0

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'ORIGINAL', 'INTERIM');
END;
/

PL/SQL procedure successfully completed.

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','ORIGINAL',
'INTERIM');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*), SEGMENT_NAME FROM USER_EXTENTS GROUP BY SEGMENT_NAME;

COUNT(*) SEGMENT_NAME
--------------------------------------------------------------------------------
14 ORIGINAL
40 INTERIM


3) ALTER TABLE ... MOVE

Another effective method to shrink space in a LOB is to move the table

The following note demonstrates this
How To Use ALTER TABLE ... MOVE To Shrink A Table (Including BASICFILE / 
SECUREFILE LOBs) (Document 1396120.1)

PROs: This method is simple and very effective
CONs: This method locks the table and will cause an outage
This method will require additional space not required by other methods
(up to double the size of the size of the table)
This method is complicated

4) EXPORT / DROP THE TABLE / IMPORT

An export (including DataPump - expdp) followed by a drop of the table 
containing the lob followed by an import (including Datapump - impdp) 
will recreate the table and thus reinsert the rows within ... thus 
eliminating 'sparseness'

PROs: Export/Import are a well established method of reorganizing

CONs: This method requires an outage beginning with the start of the 
export and ending with the end of the import (and adding of FK If Foreign 
Key constraints (FK) exist TO the table to be dropped .. those 
constraints must be dropped before the table can be dropped ... then 
later added again after the table is imported

5) Add / Drop columns

A creative but complicated method of shrinking a LOB is to

* Add a temporary column to the table of the same datatype as the LOB 
  column to be shrunk (CLOB or BLOB)
* Copy the data from the original LOB column to the new column
* Drop the original column
* Add a new column of the same name as the original column to the table 
  of the same datatype as the LOB column 
  to be shrunk (CLOB or BLOB)
* Copy the data from the temporary column to the new original colunn
* Drop the temporary column

For an example of this method see:
How to release unused space (SHRINK) occupied by a LOB segment by adding 
/ dropping columns? (Document 1417697.1)

[5] Oracle Datapump supports SecureFiles as Default

Oracle 12c stores LOBs as SecureFiles per default. In the case where a 
table or a database using BasicFiles was exported using EXPDP, if you 
want to import the dump file into a 12c database, Data Pump will try to
recreate LOBs exactly as they were stored in the old database. It was not
possible to recreate LOBs directly as SecureFiles.

I think you have guessed, Oracle 12c offers a new clause for the impdp 
tool, in order to convert LOBs stored as BasicFiles to SecureFiles on 
the fly: TRANSFORM=LOB_STORAGE:SECUREFILE.


sqlplus sys/oracle_4U@racnode2/pdb2 as sysdba

SQL> create or replace directory test as '/home/oracle';
SQL> grant read,write on directory test to test,hr;

sqlplus test/test@racnode2/pdb2

SQL> create table t1 (a BLOB) lob(a) store as basicfile;

SQL> insert into t1 values( hextoraw('453d7a34'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

A
--------------------------------------------------------------------------------
453D7A34

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
A BLOB

SQL> select securefile from user_lobs where table_name='T1';

SEC
---
NO


-bash-4.1$ expdp test/test@racnode2/pdb2 tables=t1 dumpfile=t1.exp 
directory=test

Export: Release 12.1.0.2.0 - Production on Sun Sep 8 16:55:01 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions
Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@racnode2/pdb2 tables=t1 dumpfile=t1.exp directory=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1" 5.062 KB 1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t1.exp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 8 16:55:57 2019 elapsed 0 00:00:49


-bash-4.1$ cat params.txt
directory=test
dumpfile=t1.exp
remap_tablespace=TEST:EXAMPLE
remap_schema=TEST:HR
remap_table=TEST.T1:T1

-bash-4.1$ impdp HR/HR@racnode2/pdb2 parfile=params.txt

Import: Release 12.1.0.2.0 - Production on Sun Sep 8 17:16:08 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": HR/********@racnode2/pdb2 parfile=params.txt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."T1" 5.062 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 8 17:16:43 2019 elapsed 0 00:00:33

-bash-4.1$ sqlplus HR/HR@racnode2/pdb2

SQL> select securefile from user_lobs where table_name='T1';

SEC
---
NO

SQL> select count(*) from T1;

COUNT(*)
----------
1

-bash-4.1$ cat params.txt
directory=test
dumpfile=t1.exp
remap_tablespace=TEST:EXAMPLE
remap_schema=TEST:HR
remap_table=TEST.T1:T1
TRANSFORM=LOB_STORAGE:SECUREFILE
TABLE_EXISTS_ACTION=REPLACE

-bash-4.1$ impdp HR/HR@racnode2/pdb2 parfile=params.txt

Import: Release 12.1.0.2.0 - Production on Sun Sep 8 17:18:52 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": HR/********@racnode2/pdb2 parfile=params.txt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."T1" 5.062 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 8 17:19:30 2019 elapsed 0 00:00:37

-bash-4.1$ sqlplus HR/HR@racnode2/pdb2

SQL> select securefile from user_lobs where table_name='T1';

SEC
---
YES

SQL> select count(*) from T1;

COUNT(*)
----------
1

[6] Parallel DML 
Parallel DML supported for LOB stored as SecureFiles on a non-partitioned 
table With Oracle 11g, parallel DML is already supported with LOB columns
,no matter if it is stored as BasicFile or SecureFile. But it is only 
supported for partitioned tables.

Oracle 12c offers SecureFiles LOB support enhancements, since it is now 
possible to perform statements in parallel with LOB columns stored as 
SecureFiles on a non partitioned table.

For LOB stored as BasicFile, the statement still runs in Serial mode, 
even if the parallel clause is specified.

To demonstrate this new feature, I have created a table T1 on two 11g 
and 12c databases.


======================
Patch to Documentation.
======================
Application Development
-> Database SecureFiles and Large Objects Developer's Guide

Why is no space released after an ALTER TABLE ... SHRINK? 
(Doc ID 820043.1)

Attempt to reduce the size of a LOB segment after the table / LOB 
becomes sparse -> How to Shrink (make less sparse) a LOB (BASICFILE or 
SECUREFILE)? (Doc ID 1451124.1)

How LOB columns can be compressed and storage savings can be gained by 
using Oracle 11g Advanced Compression features.
11g Advanced Compression - How to Check Space Occupied by LOB 
Compression (Doc ID 861344.1)

Thank you for visiting this blog 🙂