Monday , September 25 2023

How To Migrate From BasicFiles to SecureFiles

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 …


Below are some examples of these methods.

Example For Second Method

Example For Third Method

Normal Tables:

Partitioned Tables:

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.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *