IMPORT: Strange problem

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.

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.

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.

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,

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';

Hi Mr. Hanky,

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

I am really surprised and concerned about it.

Thanks,

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?

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.

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.