X

All Things Database: Education, Best Practices,
Use Cases & More

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

Bogapurapu laxmi krishna Rao
Senior Cloud Engineer
This is a syndicated post, view the original post here

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). More detailed information is shared in the note: Desupport of Oracle Multimedia Component in Oracle 19c (Doc ID 2555923.1)

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;

 

In the next blog we will discuss how one can utilize the APEX MULTIMEDIA EXTENSION to perform Image RESIZE Operations.

 

References: 

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2555923_1.html

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.