How to make exp utility work on Oracle 11gR2

April 30, 2010 by 18 Comments 

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;

Update 11-Oct-2011:

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.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

18 Responses to “How to make exp utility work on Oracle 11gR2”
  1. Tom A says:

    Thanks for this information! My colleague and i have been struggling with this all afternoon, and the ALTER TABLE … ALLOCATE EXTENT command is exactly what we need. We’d been playing around with schemes for importing dummy rows and all sorts of daft things, but this is a genuine solution.

  2. BoBa says:

    Great Stuff – it works – thank you.

  3. Balaji says:

    This is excellent and worked for us.
    Thanks for sharing.

  4. T Baumgarten says:

    This is definitely great stuff.

    One thing I’ve recently discovered is that you can also get ORA-01455 errors when exporting from a database that contains one or more Materialized View Logs — even when utilizing all of the workarounds described above (turning off “Deferring Segment Creation”, specifying “INDEXES=N” / “STATISTICS=NONE”, etc).

    This is really blatant behavior too. You can perform an error-free export of a schema, create a single Materialized View Log, then attempt the exact same schema export again and watch it bomb after the “exporting snapshot logs” line in the log.

    Haven’t been able to discover a workaround as yet, though.

  5. Dsanchez says:

    Simply great!!, Thanks for sharing it

  6. Janan says:

    ALTER TABLE ALLOCATE EXTENT;
    thx alot, so grt

  7. Jim Klimov says:

    Hello, I’d like to thank you for this suggestion – it was on-spot!

    I’d also like to share an SQL snippet to generate extent-allocation commands ready for copy-pasting in an SQLPlus session:


    set autocommit on;
    set line 124;

    select ‘ALTER TABLE ‘,TABLE_NAME,’ ALLOCATE EXTENT;’ from user_tables where SEGMENT_CREATED = ‘NO’;
    select ‘ALTER TABLE ‘,TABLE_NAME,’ ALLOCATE EXTENT;’ from user_lobs where SEGMENT_CREATED = ‘NO’;
    select ‘ALTER INDEX ‘,INDEX_NAME,’ ALLOCATE EXTENT;’ from user_indexes where SEGMENT_CREATED = ‘NO’;

    You might save this into a file such as “.allocate-extents.sql” and then use like this:

    $ for U in USER1 SCOTT TESTUSER; do sqlplus $U/COMMONPASSW0RD@MyOraSID @ .allocate-extents.sql; done

    If you have tables, lobs or indexes without extents, they will be listed – and you’ll have an active SQLPlus session remaining to just copy-paste the SQL command lines and allocate storage for these objects.

    This simple script does not take care of object uniqueness (but it doesn’t matter much), and I couldn’t allocate INDEXes for LOBs:

    SQL> ALTER INDEX SYS_IL0000073659C00038$$ ALLOCATE EXTENT
    *
    ERROR at line 1:
    ORA-22864: cannot ALTER or DROP LOB indexes

    Other than that, export and import now seem consistent again!

    Thanks!
    //Jim Klimov

  8. Suleyman says:

    Just what I needed after wasting 3 hours; thank you! (and thank you Google!)

  9. Akshan says:

    Thanks Kirill for saving my day! Export was always failing due to the empty tables. Here is my dynamic sql:
    DECLARE

    CURSOR cur IS
    SELECT ‘ALTER TABLE ‘ || table_name || ‘ ALLOCATE EXTENT ‘ vSQL
    FROM user_tables
    where SEGMENT_CREATED = ‘NO’;

    BEGIN
    FOR c IN cur LOOP
    BEGIN

    EXECUTE IMMEDIATE c.vSQL;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));
    END;
    END LOOP;
    END;
    /

  10. Kirill Loifman says:

    Here is my dynamic sql:
    DECLARE

    Thanks for the SQL example. It should work but you will not see any valuable output (number of extends allocated, etc)

    I couldn’t allocate INDEXes for LOBs:

    You should not! See my update in the article above.

  11. We had similar problems, i used triggers=no statistics=none in exp utility.
    It Works fine. No error. No missing tables.

  12. Lucian Benca says:

    So for those of you who want to export only some tables and get the same error and none of the above work, make sure you don’t want to export empty tables without FKs on them, as long as they have any kind of FK you don’t get ORA-01455.

    For some reason (didn’t bother to dig that deep), when trying to export empty unreferenced/unrefered tables damn ORA-01455 pops up.

    Cheers.

  13. Ashqar says:

    thanks mate…..

  14. santhana krishnan says:

    Hi Kirill,

    I have got an issue regarding this, my oracle version is 10.1.0.2 in which i scheduled a full exp and for a user table i got this error. whether it is a critical error or a normal thing because this full backup is been made weekly and can u suggest me a soln regarding to this

    Error Details:

    EXP-00008: ORACLE error 1455 encountered
    ORA-01455: converting column overflows integer datatype

    Thanks and regards
    Santhana Krishnan

  15. Kirill Loifman says:

    Hi Santhana
    You problem is not really relevant to my post. However your issue can be probably caused by the fact that
    Export utility tries to convert various statistics values (number of blocks etc.) into an integer. If any of these values are greater than 2^31-1, the error is reported.
    So you can try following workarounds:
    1) Specify INDEXES=N in exp command line and run your export again
    or
    2) Identify a failing index and drop stats for it:
    ANALYZE INDEX DELETE STATISTICS

    The best – use Data Pump :)
    — Kirill

  16. Nice script, thanks //Jim Klimov works fine!

  17. Sushant says:

    Yeah! It worked like a charm
    Thanks a lot,

  18. Sai says:

    Hi…..

    v no need to mention indexes=n, constraints=n if u just mention statistics=none thats fine we are getting error beacuse of statistics

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email