MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
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 12.1.0.2 (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 12.1.0.2 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.