Simple migration from Oracle multimedia to secure-file blob data type

Support for the Oracle Multimedia data type was ended in the Oracle Database 19c release, so we wanted to provide some guidance for working with this data type from that release onward.

Oracle recommends that you store multimedia content in secure files lob and use third party products such as the APEX multimedia extension (AME).

Current users of the Oracle Multimedia data type planning to upgrade to Oracle Database 19c will need to move their existing multimedia content to secure file lob.

Let’s take a look at few options to manage that transition.

SOLUTION 1: UPDATING COLUMN ON SAME TABLE

AN OVERVIEW OF THE PROCESS:

◉ TAKE BACKUP OF EXISTING TABLE

◉ ADD NEW COLUMN OF DATATYPE BLOB AND SECUREFILE

◉ UPDATE DATA FROM ORDIMAGE COLUMN TO BLOB COLUMN

◉ SET UNSED OLD COLUMN FROM TABLE

◉ RENAME NEW COLUMN TO ORIGINAL COLUMN NAME

◉ DELETE OLD COLUMN NAME

OBJECTS USED IN THE EXAMPLE:

TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE/IMAGE_TABLE_BKP
COLUMN WITH MULTIMEDIA DATATYPE	IMAGE
COLUMN WITH SECURE BLOB DATATYPE	IMAGE_BLOB

BACKUP OF TABLE: TAKE A BACKUP OF THE EXISTING TABLE (OPTIONAL), RECOMMENDED FOR SAFEGUARD OF DATA

CREATE TABLE IMAGE_TABLE_BKP AS SELECT * FROM IMAGE_TABLE;

ADD NEW COLUMN TO REPLACE THE EXISTING MULTIMEDIA COLUMN: CREATE A COLUMN IMAGE_BLOB WHICH WILL HOLD SECUREFILE

SQL> ALTER TABLE IMAGE_TABLE ADD (IMAGE_BLOB BLOB) LOB(IMAGE_BLOB) STORE AS SECUREFILE (TABLESPACE MULTI_SF);

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          PUBLIC.ORDIMAGE

IMAGE_BLOB                              BLOB

UPDATE NEW COLUMN WITH OLD COLUMN DATA:

SQL> UPDATE IMAGE_TABLE I SET I.IMAGE_BLOB=I.IMAGE.SOURCE.LOCALDATA;

2 ROWS UPDATED.

SQL> COMMIT;

COMMIT COMPLETE. 

MARK OLD COLUMN AS UNUSED:

SQL> ALTER TABLE IMAGE_TABLE SET UNUSED (IMAGE);

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE_BLOB                                BLOB

RENAME NEW COLUMN AS OLD COLUMN:

SQL> ALTER TABLE IMAGE_TABLE RENAME COLUMN IMAGE_BLOB TO IMAGE;

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          BLOB

DROP OLD COLUMN:

SQL> ALTER TABLE IMAGE_TABLE DROP UNUSED COLUMNS CHECKPOINT 250;

TABLE ALTERED.

SQL> DESC IMAGE_TABLE

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

IMAGE                                          BLOB 

CHECK DATA IN TABLE:

SQL>  EXEC CHECK_SPACE_SECFILE('IMAGE_TABLE','MULTI_SF');

SEGMENT BLOCKS = 2072,  BYTES = 16973824

USED BLOCKS = 895,  BYTES = 7331840

EXPIRED BLOCKS = 1102,  BYTES = 9027584

UNEXPIRED BLOCKS = 0,  BYTES = 0

=============================================

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SOLUTION 2: MOVE TO A NEW TABLE 

AN OVERVIEW OF THE PROCESS:

◉ CREATE A TABLE IMAGE_TABLE_BLOB SIMILAR TO IMAGE_TABLE BUT WITH COLUMN DATA-TYPE AS BLOB INSTEAD OF ORDIMAGE DATA TYPE

◉ INSERT DATA FROM IMAGE_TABLE TO IMAGE_TABLE_BLOB

OBJECTS USED IN THE EXAMPLE:

TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE_BKP
COLUMN WITH MULTIMEDIA DATATYPE	IMAGE
TABLE WITH MULTIMEDIA DATATYPE	IMAGE_TABLE_BLOB
COLUMN WITH SECURE BLOB DATATYPE	IMAGE

ORIGINAL TABLE:

SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME LIKE 'IMAGE_TABLE_BKP';

OWNER                TABLE_NAME           COLUMN_NAME          DATA_TYPE

-------------------- -------------------- -------------------- --------------------

MULTI_SF             IMAGE_TABLE_BKP      IMAGE                ORDIMAGE

MULTI_SF             IMAGE_TABLE_BKP      ID                   NUMBER 

CREATE BACKUP TABLE:

CREATE TABLE IMAGE_TABLE_BLOB (ID NUMBER,IMAGE BLOB) LOB(IMAGE) STORE AS SECUREFILE (TABLESPACE MULTI_SF)

SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME LIKE 'IMAGE_TABLE_BLOB';

OWNER                TABLE_NAME           COLUMN_NAME          DATA_TYPE

-------------------- -------------------- -------------------- --------------------

MULTI_SF             IMAGE_TABLE_BLOB     ID                   NUMBER

MULTI_SF             IMAGE_TABLE_BLOB     IMAGE                BLOB

INSERT NEW DATA IN TO THE TABLE:

INSERT INTO IMAGE_TABLE_BLOB SELECT I.ID,I.IMAGE.SOURCE.LOCALDATA  FROM IMAGE_TABLE I;

CROSSCHECK DATA IN NEW TABLE:

SQL> EXEC CHECK_SPACE_SECFILE('IMAGE_TABLE_BLOB','MULTI_SF');

SEGMENT BLOCKS = 2072,  BYTES = 16973824

USED BLOCKS = 895,  BYTES = 7331840

EXPIRED BLOCKS = 1102,  BYTES = 9027584

UNEXPIRED BLOCKS = 0,  BYTES = 0

=============================================

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

VALIDATE DATA AND CHECK IF THE MIGRATION IS SUCCESSFUL

You can either drop the old table and rename the new table to OLD TABLE, or continue with the new table name

DROP TABLE IMAGE_TABLE;

RENAME IMAGE_TABLE_BLOB TO IMAGE_TABLE;