How to avoid Blob corruption

Blob corruption

   Oracle database have Basic and Secure blob type. Both store images, but former one have many bugs and development stopped long time ago. So its better to convert existing Basic blob to Secure and avoid corruption.

One of the error you get when Basic blob got corrupted is

ORA-01555: snapshot too old: rollback segment 

The above error usually comes when you execute DML statements, but you'll get this for blob corruption too. This blog describes how you can avoid this. The conversion wont affect your application functionality.

Fresh creation

We can force all new blob creation in the database to secure, by executing 

alter system set db_securefile=always scope=both;

The above statement will force Secure or Basic blob to be created as Secure.

Existing Basic to Secure conversion

 This can be done in few steps using redefinition.

• create a dummy table with same table structure with securefile clause

 create table blob_tab
(eid varchar2(40), details blob, primary key(eid))LOB (details) STORE AS SECUREFILE (tablespace users)  tablespace users;

• Start the redef proces

dbms_redefinition.start_redef_table('scott', 'old_table', 'blob_tab', 'eid eid, details details');

• If you have any primary key in the original table drop it temporarily.

alter table scott.old_table drop primary key;

• Copy all relevant information, declare a variable "error" as number

dbms_redefinition.copy_table_dependents ('scott','old_table', 'blob_tab',1,true, true, true, false, error);

• Again create the primary key which was dropped before

alter table scott.old_table add primary key(eid);

• Move back all the information back to the original table

dbms_redefinition.finish_redef_table ('scott', 'old_table', 'blob_tab')

You can confim this with by,
select securefile from user_lobs where table_name='OLD_TABLE'; 

Rolling back

In case you want to rollback, create a interim table without "securefile" option and follow the same procedure. 

Remember your db_securefile parameter overrides your create table option for blob.

Troubleshooting

You could face few issues.

If you abort/error out in the middle of redef and start, you'll get the below error.

ORA-23539: table "SCOTT"."OLD_TABLE" currently being redefined

You can delete "materialized view log" and drop original table and recreate interim table.

Or, you can simply

dbms_redefinition.abort_redef_table('scott','old_table','blob_tab');
-----------------------------------------------------------------------------------------------------------------------------

If you have primary key you have to drop and recreate it, just like the above steps, else you'll get

ORA-01408: such column list already indexed