Import Error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Import Error

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Import Error

    Hi

    We are getting the following error

    IMP-00058: ORACLE error 1653 encountered
    ORA-01653: unable to extend table DATA_TS.RENEWBACKUP by 32789 in tablespace DATA_TS
    Import terminated successfully with warnings.

    But the tablespace has lots of freespace .

    *****************

    SQL> select max(bytes) from dba_free_space where tablespace_name='DATA_TS';

    MAX(BYTES)
    ----------
    210395136
    ************************



    Why is this error occuring ?? Is Adding Datafile the only solution cos there is still ample freespace in the existing datafiles of this tablespace?

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Seems to me like you have a fragmented table that could use a reorg.

    What version?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    Re: Import Error

    Code:
    SQL> select max(bytes) from dba_free_space where tablespace_name='DATA_TS';
    
    MAX(BYTES)
    ----------
    210395136

    Looks to me like you've only got 210megs left in that tablespace. How big is the table you're importing? Are you using LMT's if so are you autoallocating or uniform? If uniform what extent size are you using?

    KenEwald;
    import and export are cures for fragmentation
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Aug 2002
    Posts
    176
    Thanks for your replies.


    We are using Oracle 8.1.7 on Sun solaris(Export) on LMT and Oracle 8.1.7 on windows NT (import) on DMT.

    yeah I too believe Moving the table to another tablespace coul resolve it.


    Actually we are importing the table with ignore =y.
    We are exporting a table from another box importing it here at End of Day and truncate the table exportted table. This imported table keeps growing with new records each day.

    Could there be any other issues other than fragmentation??

    John
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Ok, let me see if I can explain this to you. First of all fragmentation is not causing this error. If a table (within) the database has many extents (for an example if you have a 100 meg table and you have like 300 extents on it) it is considered fragmented and is a canidate for a re-org. There are a couple of ways to solve fragmentation but the most easiest of them is to simply export the table out of the database drop the table within the database and import it back in.

    In your case you have a table that you've exported out of one database and into another. The dump file is nothing but a text file anyway (so how could it contain fragmentation?)
    Basically what has happened is that Oracle could not find a large enough area of free contiguous space in which to fit the next extent.
    If you reference this article on metalink (which is the first thing you should done) you will see how to solve this problem very easily.
    http://metalink.oracle.com/metalink/...&p_id=151994.1

    Word of advice, instead of export,truncate,import why don't you setup a database link between the two database's, create a trigger to update the table in question.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Aug 2002
    Posts
    176
    Thanks

    I did not check the next extent. Actually the Error message
    "unable to extend table DATA_TS.RENEWBACKUP by 32789 in tablespace " mislead me.

    Now everything is fine. Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

Posting Permissions

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



Click Here to Expand Forum to Full Width