How to make exp utility work on Oracle 11gR2
The fact that Oracle Data Pump does not work from a client installation only still forces many developers and DBAs to work with old Export (exp) and Import (imp) utilities. In spite of Original Export being unsupported for general use as of Oracle database 11g, in reality it’s still available and can be used. However, there are a few issues with exp utility on Oracle database 11gR2 that have to be fixed before using it.
First, you have to install Oracle 11gR2 client to make exp work with the same database version. Otherwise you might get the following error below:
. exporting referential integrity constraints
. exporting triggers
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully
If you must keep your older client version, exp would still work with INDEXES=n STATISTICS=none parameters.
The other issue is that the Export utility (exp) skips empty table segments by default so that they are not exported at all, even the DLL definitions.
This problem is related to the new Oracle 11gR2 feature called deferred segment creation and the fact that empty tables are not listed in dba_segments.
This database feature is controlled by the database parameter deferred_segment_creation. It has default value of TRUE. If you set it to FALSE, any newly created tables after the change will be exported. There are also a few ways to fix that issue for existing empty tables. One of them is below:
ALTER TABLE <empy table> ALLOCATE EXTENT;
Don’t forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.