Oracle's Conventional Export (exp) May Cause Problems

Monday May 14th 2018 by David Fitzjarrell

There may be some still using conventional export (exp); with Oracle 12 that may not be a good idea.  Read on to see why.

Sometimes it's desired to move data from production to test or development environments, and if done with the original exp/imp utilities, issues can arise since these were written for database versions older than 9.x. Those utilities don't support features found in newer database versions, which can create performance problems.

Tables with Top-n or hybrid histograms, when exported with exp, won't get those histograms replicated to the destination database; both Top-n and hybrid histograms will be converted to Frequency histograms. Looking at a table in (from an example by Jonathan Lewis) let's see what histograms are present:

COLUMN_NAME              Distinct HISTOGRAM          Buckets
-------------------- ------------ --------------- ----------
FREQUENCY                     100 FREQUENCY              100
TOP_N                         100 TOP-FREQUENCY           95
HYBRID                        100 HYBRID                  50

Using legacy exp the table is exported. Importing this into another database using legacy imp the histogram types have changed:

COLUMN_NAME              Distinct HISTOGRAM          Buckets
-------------------- ------------ --------------- ----------
FREQUENCY                     100 FREQUENCY              100
TOP_N                         100 FREQUENCY               95
HYBRID                        100 FREQUENCY               50

Note that the Oracle release is the same in both databases; it's the exp/imp utilities creating the problem. Using datapump to transfer the data would have preserved the histograms. If there are scripts in use that use these old utilities it's probably time to rewrite them to take advantage of datapump export and import.

It's sometimes easier to use what's already written but in the case of conventional export and import it's time to retire these scripts when using Oracle releases that support datapump.

See all articles by David Fitzjarrell

Mobile Site | Full Site