Oracle Export and Import

Question

Some Background:

Somewhere around Oracle 10, which was about a decade ago (give or take), Oracle added a new method of exporting and importing databases called the Oracle Data Pump. Aside from the silly name, the functionality works mostly the same as the Original Export and Import Utility.

The link to the Original Utility contains the following warning text, which appears to be at least somewhat self-contradictory:

Original export is desupported for general use as of Oracle Database 11g. The only supported use of Original Export in 11g is backward migration of XMLType data to a database version 10g release 2 (10.2) or earlier. Therefore, Oracle recommends that you use the new Data Pump Export and Import utilities, except in the following situations which require Original Export and Import:

• You want to import files that were created using the original Export utility (exp).

• You want to export files that will be imported using the original Import utility (imp). An example of this would be if you wanted to export data from Oracle Database 10g and then import it into an earlier database release.

As far as I can tell, the only reason Exp and Imp would not operate correctly is if the database uses features introduced in 11g onward. Otherwise, it appears that the old Exp and Imp commands should work just fine, and from the above, they do appear to be officially supported.

One of the key differences with "Data Pump" vs. "Original" export - and this is important for my application - is that the data pump operates server-side only, meaning that a user will require at least some degree of permissions to the server to access the file produced by the export. At best, this is inconvenient, and at worst, this results in a file that cannot be accessed by anyone other than the dba.

Issue:

When we upgraded to 12c from 11g, we had an issue using the original export utility. It would run successfully up to the point of exporting the triggers, then produce an error as follows:

EXP:00056 ORACLE error 4063 encountered
ORA-06508: package body XDB.DBMS_XDBUTIL_INT has errors
ORA-06508: PL/SQL: could not find program unit being called:
"XDB.DBMS_XDBUTIL_INT"

The Question:

This issue came up at least a dozen times in different contexts, and we are sort of playing whack-a-mole with it. The most recent attempt at solving it involves recompiling every package on the server, which takes about a half hour.

1. Why does this export issue keep coming up?
2. Are Exp and Imp actually, officially, deprecated, such that we are no longer able to use them reliably?
3. Are there any other straightforward ways to get a client-side export of the database?

Answer 1

1. Why does this export issue keep coming up?

   Since the problem is intermittent I would guess it's caused by deferred segment creation. Since 11g, tables and partitions can be configured to not allocate any space until there is some data. (This can save significant space for tables with many empty partitions.) But Exp doesn't understand this and assumes every table must have a segment. Which means some tables and related features may appear to "randomly" cause problems depending on whether or not they've been populated or truncated lately.

   You can find those tables with this query:

   select * from dba_tables where segment_created = 'NO';
   

   And then force them to have a segment with this statement:

   alter table table_name allocate extent;
   

2. Are Exp and Imp actually, officially, deprecated, such that we are no longer able to use them reliably?

   This is debatable but I'd say yes, the original Exp and Imp are truly "deprecated" now. It does feel like Oracle plays a lot of games with deprecating software. For example, deprecating the free change data capture for the super expensive Goldengate, or deprecating non-container architecture when almost nobody wants to use their expensive containers. But it's been a long time and Exp and Imp don't cut it anymore.

3. Are there any other straightforward ways to get a client-side export of the database?

   Try OCP, Oracle CoPy. You still need to generate the export on the server. But OCP allows you to download files from the server filesystem to the client filesystem without any server filesystem permissions. It's still not as straight-forward as it should be but at least you don't have to give everyone privileges on the server filesystem.