IMPORT: Strange problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: IMPORT: Strange problem

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi all,

    Oracle 81730/NT.

    Example: I took export of XYZ schema which belongs to XYZ tablespace. That time, free space in XYZ was 50M. XYZ tablespace size is 100M. Then, I dropped all the objects and coalesced XYZ tablespace. Now, it showed free space of 100M.

    Then, I imported the XYZ schema back. In the middle of import, I started getting errors that unable to allocate next extent, unable to allocate initial extent. Then I checked the free space left in XYZ. It was showing as zero.

    Howcome, there is a free space problem when I am importing the same amount of data back and there was actually free space before I dropped all the objects from XYZ table space.

    Pl. note here that I took export with COMPRESS=N so that is not the problem.

    Pl. help me understand.

    Thanks in Adv.



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Very strange.

    Have you looked at sum of bytes in freespace or looked at individual freespace chunks ?

    There should have been 1 individual chunk of freespace after coalesce.
    if there were many different sized freespace chunks this may have caused the problem.

    If I was you I would investigate dba_segments, dba_freespace when you get the ORA error message and try to work out what happened.

    Either that or your maths may be wrong. I have done a similar thing to what you are doing many times and the only time I encountered this problem was when I forgot to coalesce my tablespace after I dropped all the objects in it and then ran an import.

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Sureshy,

    I'll do it again.

    I have dropped all objects from GLD and GLX tablespace and rightnow it shows foll info in dba_free_space.

    select file_id,bytes from dba_free_space where tablespace_name = 'GLD'

    file_id bytes
    ---------------------------
    39 524279808
    106 524279808
    128 104849408

    select file_id,bytes from dba_free_space where tablespace_name = 'GLX'

    file_id bytes
    ---------------------------
    40 305127424
    107 366993408
    124 419422208

    SELECT * FROM DBA_SEGMENTS WHERE tablespace_name='GLD'

    No rows returned

    SELECT * FROM DBA_SEGMENTS WHERE tablespace_name='GLX'

    No rows returned


    Now, I am giving command

    imp gl/@test parfile=impgl.dat

    I'll post the result after sometime.

    Thanks.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Sureshy,

    I got the same error again during import. Now if I check dba_free_space, it shows no rows found.

    select file_id,bytes from dba_free_space where tablespace_name = 'GLD'

    No rows.

    After than, I checked the biggest table. To my surprise, this table's next extent has got changed and it has become bigger. Its size also has got increased.

    select bytes,extents,initial_extent,next_extent,pct_increase from dba_segments where segment_name = 'GL_JE_LINES'

    493625344 118 16384 4218880 0

    Originally, it was as below.

    450150400 1193 16384 524288 0

    What has caused this change in spite of not giving COMPRESS=Y while export?

    Can you suggest me what to do as I need to import this schema back to start my database.

    Thanks,




    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    What is the pct_increase set to on the table name.

    SQL> Select owner, table_name, pct_increase
    from dba_tables
    where table_name='blah_blah';
    I remember when this place was cool.

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Mr. Hanky,

    I have pasted pct_increase in above query. It is 0.

    I am really surprised and concerned about it.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What is the setting of PCTFREE for those tables?

    Let's say it was set to 60. So after initial inserts all those table blocks were only 40% full. Then you made some updates to those rows which might fill those blocks completely. So your tables occupied 50 megs of space with all their blocks (almost) completely full before you performed an export, although their PCTFREE was set to 60%.

    Now when you import them back, imp filled the blocks only up to 40% full because the PCTFREE is still set to 60 for those tables. That mean that imp will need at least 125 megs of space to import those tables back.

    Could this be an explanation for your problem?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Jurij,

    Sorry for delay, I was away from work.

    In this case, PCTFREE is 20% and it is unchanged between Export and Import. If what you say is correct, how to resolve this problem?

    Purpose of doing Export/Drop/Import was to remove free space fragmentation and save some space. But, if Import will fail like this, I would have to add extra space instead of saving some.

    Pl. guide.

    Thanks all for help so far.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    That blows my theory away. PCTFREE=20 is not enough to happen what I was speculating, it should have been more than 50% for that to happen.

    What you described realy is strange. Have you been fiddeling with XYZ's quotas on other tablespaces? Is it possible that import is trying to create indexes in tablespace XYZ although they were originaly in some other tablespace?

    What I would suggest you to do would be to import once again with ROWS=N - that way you'll only get the structure of the schema without the data. From there you could investigate if something unexpected happened with settings of PCTFREE or PCTINCREASE for imported objects or if there are some other segments created in XYZ tablespace that was not in it before the export.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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