Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Question Unanswered: Problem with export table.

    Hello!

    I have my Oracle XE database and I want to export one database schema. I run "exp" command and during this process I get so error:

    Code:
    . . exporting table                       TABLE_NAME                         
    EXP-00056: ORACLE error 1555 encountered                                   
    ORA-01555: snapshot too old: rollback segment number  with name "" too small
    ORA-22924: snapshot too old
    I found in google many pages with solutions and usually solution was increase size of undo tablespace or "increase undo_retention setting". I think I did it, but that error still exists. Maybe I did it wrong. I don't know? Two important message which can help. In this table exists clob. Also recently I try to decrease and defragmention of many tablespaces, also undo tablespace. Maybe I a little "damage" something? I don't know? How to check it?

    Please help me with my error. This error takes me many hours of suffering. For example in friday I try to fix to 4 a.m hour. Next day similarly. Also I'm new to administrate oracle database (it's no my IT profession) and I don't know how to fix my problem. I'm desperate.

    Please help me. I will provide all information which will be needed.

    Already now I want to acknowledge for all help which I will get.

    Best regards,
    Fryderyk

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    22924, 00000, "snapshot too old"
    // *Cause: The version of the LOB value needed for the consistent read was
    // already overwritten by another writer.
    // *Action: Use a larger version pool.


    The sad reality is that some other process is doing DML against the table being exported.
    This error will not occur when the LOB data is static & not changing.

    The two alternatives
    1) make UNDO larger
    2) prevent LOB data from being changed during export.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    I don't think that some other process is doing DML. My Oracle works on my laptop and I had started database directly before exported database. The same error.

    You wrote:
    The two alternatives
    1) make UNDO larger
    2) prevent LOB data from being changed during export.
    1) How to make UNDO larger?
    I did it so:
    -- get UNDO tablespace name:
    select name,value from v$parameter where name in ('undo_management','undo_tablespace');

    Result:
    undo_management AUTO
    undo_tablespace UNDO

    -- get file name of UNDO tablespace
    select tablespace_name, file_name from dba_data_files where tablespace_name = 'UNDO';

    Result:
    UNDO /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/UNDO.bdf

    alter database datafile '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/UNDO.bdf' resize 1024M;

    Result:
    alter database datafile succeeded.
    Try to export. The same error code. I did it good or you have in mind something else?

    2) How to do it?

    Best regards,
    Fryderyk
    Last edited by dedyk; 06-08-09 at 05:51.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I don't think that some other process is doing DML.
    Since BOTH error messages indicate past [delayed block clean out] or present DML &
    since I believe Oracle is too dumb to lie; I remain convinced that DML is involved.

    Post Operating System (OS) name & version for DB server system.
    Post Oracle version to 4 decimal place of database software.

    Post actual export command line which needs to include LOG=capture.log

    Post content of capture.log
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    My Operation System (OS): Mandriva Linux release 2009.1 (Official) for i586
    DB server system: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    And my two command lines:

    /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/exp wm70_om_orders/wm70_om_orders file=dump_with_consistent_n.dmp log=dump_with_consistent_n.dmp.log tables=om_order consistent=n

    /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/exp wm70_om_orders/wm70_om_orders file=dump_with_consistent_y.dmp log=dump_with_consistent_y.dmp.log tables=om_order consistent=y
    What is impotant, change consistent to 'n' or 'y' doesn't have any influence to error code. Both result of run is:

    Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified tables via Conventional Path ...
    . . exporting table OM_ORDER
    EXP-00056: ORACLE error 1555 encountered
    ORA-01555: snapshot too old: rollback segment number with name "" too small
    ORA-22924: snapshot too old
    Export terminated successfully with warnings.
    I don't know if it's important, but why in this error message segment number and name are empty? Template of this error code is: "ORA-01555: snapshot too old: rollback segment number string with name "string" too small

    Update: I did a small test. I had restored this database from backup from 22th May and I did export with the same command. And this time every thing is good. This error doesn't appear. This means that I did something bad since 22th May. Recently only I wanted to decrease size of my tablespaces. And I created new UNDO tablespace. Maybe here we have a key to success?
    Last edited by dedyk; 06-08-09 at 06:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •