How To Migrate From BasicFiles to SecureFiles

dbtut December 2, 2019 ORACLE

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

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

SecureFiles LOB can only be created in a tablespace managed by Automated Segment Space Management (ASSM).

SecureFiles is the default storage mechanism for LOBs since version 12c, and Oracle recommends SecureFiles instead of BasicFiles to store and manage LOBs. BasicFiles will be deprecated in a future release.

Migrate From Basicfile to Securefile

Various methods can be used to migrate from basicfile to securefile. The following methods can be used.

1) Export the table, create a new table with the same columns with the secure file lob and import the data into the new table

2)Add securefile column to table, update with data in basicfile column. Then drop the basicfile column and change the new column name to the existing column name.

3)With the command ALTER TABLE table_name MOVE …

Examples

Below are some examples of these methods.

Example For Second Method

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

SQL> alter table ADURUOZ.LOBTABLE add (FILE1 blob) lob (FILE1) store as securefile (tablespace TABLESPACE_NAME);

SQL> update ADURUOZ.LOBTABLE set FILE1=FILE;

SQL> commit;

SQL> ALTER TABLE ADURUOZ.LOBTABLE DROP COLUMN FILE;

SQL> ALTER TABLE ADURUOZ.LOBTABLE RENAME COLUMN LOBTABLE.FILE1 TO FILE;

Example For Third Method

Normal Tables:

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

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

Partitioned Tables:

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

1	SQL> ALTER TABLE ADURUOZ.LOBTABLE MOVE PARTITION PARTITON_NAME LOB (FILE) STORE AS SECUREFILE (TABLESPACE TABLESPACE_NAME);

Move All Partitioned Tables To Migrate From Basicfile to SecureFile

With the following query, you can create the command to move all partitions in a table.

select ‘ALTER TABLE SCHEMA_NAME.TABLE_NAME MOVE PARTITION ‘||partition_name||’ LOB (LOB_COLUMN_NAME) STORE AS SECUREFILE (TABLESPACE ‘||tablespace_name||’);’ from dba_tab_partitions where table_name=’TABLO_NAME’;

1	select ‘ALTER TABLE SCHEMA_NAME.TABLE_NAME MOVE PARTITION ‘\|\|partition_name\|\|’ LOB (LOB_COLUMN_NAME) STORE AS SECUREFILE (TABLESPACE ‘\|\|tablespace_name\|\|’);’ from dba_tab_partitions where table_name=’TABLO_NAME’;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux