How to convert LOB data stored as basicfile to securefile ?

SecureFiles LOB storage is one of two storage types used with Oracle databases; the other is BasicFiles LOB storage.

Several advanced features, including compression, data deduplication, and encryption, are available with SecureFiles LOB.

SecureFiles LOB can only be created in a table space managed with Automated Segmentation Space Management (ASSM).

SecureFiles is the default storage mechanism for LOBs starting with version 12c, and Oracle recommends SecureFiles over BasicFiles for storing and managing LOBs. BasicFiles will be deprecated in a future release.

Several methods can be used to convert basicfile LOB fields in your existing tables to securefile LOB fields.

The conversion can be done using the following methods.

1. The table is exported, a new table is created with the same columns and the LOB field is set to secure file, and the data is imported into the new table.
2. A column named `securefile` can be added to the table, and the data in the `basicfile` column can be updated. Afterwards, the `basicfile` column can be dropped, and the new column name can be changed to match the existing one.
3. with the command ALTER TABLE table_name MOVE …

Examples of these methods :

For method 2;

alter table ADURUOZ.LOBTABLE add (FILE1 blob) lob (FILE1) store as securefile (tablespace <TABLESPACE_NAME>);
update ADURUOZ.LOBTABLE set FILE1=FILE;
commit;
ALTER TABLE ADURUOZ.LOBTABLE DROP COLUMN FILE;
ALTER TABLE ADURUOZ.LOBTABLE RENAME COLUMN LOBTABLE.FILE1 TO FILE;

For method 3:

Non-partitioned Tables :

ALTER TABLE ADURUOZ.LOBTABLE MOVE LOB(FILE) STORE AS securefile (TABLESPACE <TABLESPACE_NAME>);

Partitioned Tables:

ALTER TABLE ADURUOZ.LOBTABLE MOVE PARTITION PARTITON_ADI LOB (FILE) STORE AS SECUREFILE (TABLESPACE <TABLESPACE_NAME>);

You can create the command to move all partitions in a table using the following query.

select ‘ALTER TABLE SEMA_ADI.TABLO_ADI MOVE PARTITION ‘||partition_name||’ LOB (<LOB_COLOUMN_NAME>) STORE AS SECUREFILE (TABLESPACE ‘||tablespace_name||’);’ from dba_tab_partitions where table_name=’<TABLE_NAME>’;