The LONG and LONG RAW data types have been deprecated since quite some time. But many a time we find in some customer databases tables still with these data types existing.
The Oracle Database 23ai Data Pump TRANSFORM parameter now includes the new option LONG_TO_LOB to enable us to migrate all LONG and LONG RAW data types as part of the import operation.
Using this transform option provides the ability enables to migrate the table with deprecated LONG and LONG RAW data types by transparently and automatically converting them to CLOB and BLOB data types as part of the Data Pump Import operation.
This parameter changes all LONG data types to CLOB and all LONG RAW data types to BLOB.
The impdp syntax is TRANSFORM = LONG_TO_LOB:[Y|N]. Note the default value is ‘N’.
Let us have a look at an example of this feature.
Create the table with LONG data type in one of the columns and insert some rows into the table.
SQL> create table scott.testtab
(id number, remarks LONG);
Table created.
SQL> insert into scott.testtab
values
(1,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into scott.testtab
values
(2,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
1 row created.
SQL> commit;
Commit complete.
SQL> create directory testdir as '/home/oracle';
Directory created.
Export the table with the LONG data type.
[oracle@sydney data]$ expdp system/xxxxx@sydney:1521/orclpdb1 tables=scott.testtab directory=testdir dumpfile=testtab.dmp
Export: Release 23.0.0.0.0 - Limited Availability on Tue Apr 15 03:52:15 2025
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@sydney:1521/orclpdb1 tables=scott.testtab directory=testdir dumpfile=testtab.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE/LOGREP
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TESTTAB" 5.6 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/testtab.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Apr 15 03:53:24 2025 elapsed 0 00:01:02
We now import the table and note the use of the option TRANSFORM=LONG_TO_LOB:Y
[oracle@sydney data]$ impdp system/xxxx@sydney:1521/orclpdb1 directory=testdir dumpfile=testtab.dmp full=y TRANSFORM=LONG_TO_LOB:Y
Import: Release 23.0.0.0.0 - Limited Availability on Tue Apr 15 03:54:49 2025
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@sydney:1521/orclpdb1 directory=testdir dumpfile=testtab.dmp full=y TRANSFORM=LONG_TO_LOB:Y
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE/LOGREP
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TESTTAB" 5.6 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Apr 15 03:56:02 2025 elapsed 0 00:01:10
After the import has been completed, we can see that the REMARKS column which was earlier LONG data type has been converted to CLOB data type.
[oracle@sydney data]$ sqlplus system/xxxx@sydney:1521/orclpdb1
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Tue Apr 15 03:56:10 2025
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Tue Apr 15 2025 03:54:49 +00:00
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability
Version 23.6.0.24.10
SQL> desc scott.testtab
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
REMARKS CLOB
