Oracle Data Pump Can't Import LONG Columns

If you are using Oracle Data Pump to backup tables containing LONG or LONG RAW columns, then you might be surprised when trying a recovery. Well, you tested it already. Didn't you? ;-)

The Discovery: ORA Errors During Production Migration

Right now I'm in the middle of a production migration. Earlier this week while testing this migration, I noticed couple strange errors during Data Pump import:

ORA-31693: Table data object "OWNER"."TABLE" failed to load/unload and is being skipped due to error: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

At some point during verification of all functionality, users notified me that they can't see Discoverer reports stored in the database. After some poking around we figured that reports are stored in the EUL5_DOCUMENTS table (thanks to Rob Hamel for help) and that table contains a LONG RAW column.

The Technical Reality: Data Pump’s Struggle with LONG Columns

Guess what? This table was empty in the new migrated database. It turned out that Data Pump Import (impdp) doesn't allow importing LONG data into LONG columns. Instead, it requires that LONG and LONG RAW type columns are converted to LOB datatypes (CLOB/BLOB) and only then it is able to import tables with LONG columns. Otherwise, those tables are empty.

One may argue that LONG data types are supposed to be converted to LOBs but, hey, let's get back to the reality—either there is no time for that or no interest to take a risk and touch working applications. In fact, in the HA environment no one will approve a combination of data move and application refactoring to be done at the same time—only one by one to mitigate the risks.

Oracle Data Pump doesn't give a chance for this, and old Export/Import utilities (exp/imp) should be used. Even CTAS (Create Table As Select) over a database link doesn't work, failing with ORA-00997: illegal use of LONG datatype.

The "Screwed" Test: Assessing Your Risk

Now go back to you databases backed up with Data Pump Export and run this query to see if you have hidden risks:

select DECODE(count(*),0,'Not much','I''m screwed '||count(*)||' times') "What's up?"  from dba_tab_columns  where data_type like 'LONG%'  and owner not in ('SYS','SYSTEM','OUTLN','EXFSYS','SYSMAN','WMSYS',  'and whatever other standard schemas where Oracle ignores its own recommendations'); 

A Sunday Perspective: From Databases to Broccoli

Today is Sunday and I don't want to finish on the negative note. So here is a good one... Yesterday, I went to Produce Depot to grab some fruits and vegetables. By the way, if you live in Canada and don't know this shop, I strongly recommend to go find one near you. It's has great selection of quality vegetables and fruits. They are very fresh and prices are surprisingly low.

So back to the story... I picked up couple of broccoli and was looking around for a bag. I probably looked really lost because a man passing by offered me his bag that he prepared in advance... even a second one as my broccoli didn't fit into a single bag and off he went for another two bags.

How cool is that?! I don't remember anything even close to that back in Germany. This is just a single example but you know what? It's amazing how friendly and open people are here in Canada. Well, I can't say that people are unfriendly in Germany but you always feel the distance. It's just cultural and, to be fair, as soon as you get to know someone closer – they are nice and charming people.

PS: In the meantime, the migration has finished. Everything works like a charm. That was a long on-call weekend and I, perhaps, deserved an unscheduled holiday for tomorrow. This is another good news. Life is good!

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?