Simple migration from Oracle multimedia to secure-file blob data type
April 7, 2021 | 3 minute readSupport 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:
|
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:
|
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
Bogapurapu laxmi krishna Rao
Senior Cloud Engineer
Previous Post
Automatically Scaling Exadata Cloud Service Based on Workload (Part 2)
Next Post