urgent: more empty blocks after imp.

[ora_inf]

I have query dba_tables to get the blocks and empty_blocks after dropping the objects and importing back.

One of the table has 10 times more empty blocks then blocks.
Also, there is no chained row for this table and table does not contain any LONG or LOB datatypes.

What could be the problem? why so many empty block?

If you have any suggetion, please let me know.

Thanks.

[ora_inf]

I am stuck...I have no idea... what to do here..
Please advise me..

Thanks.

[ocpwannabe]

different storage params maybe?

[ora_inf]

What do you mean by diff. storage param?
Do I need to change next extents or pctfree/pctused for blocks?
I don't understand that after imp, there is no activity on the table then why so many blocks are empty?

Please let me know...

Thanks

[mbaliga]

Hi,

Did the table which you exported had deleted records? If you have used the compress option for export, then while importing the table would be created with one big extent (which is sum of all the extents of exported table) and all the active rows will be imported. If the table had many deleted rows then you will find lot of empty blocks in the newly created table.

Baliga

[lmolnar]

Hi,

before the export and drop your table might have had many blocks, which weren't
"full" (they had a lot of empty place for rows). This can occur when you fill up a table with high amount of data (it extends a few times) and after you delete a lot of rows from it. By this deletion you create "holes" all around in the blocks. This is just a simple example.
The import recreates the table (if you use the "compress=y" then the first extent will be the whole size of the original table; if you use "compress=n" then the first extent will be the same as in case of the original table, but the "next" will be the actual one, which might bee quite big if your table extended many times and the
PCTINCREASE wasn't 0. So in both case your recreated table might use lot of space) and fills it up by data "block by block". After the import the blocks at the "beginning" of your table will be "full" (depending on the PCTFREE parameter),
but the rest of the blocks will be empty. That's why you have so many empty blocks. From this pont of view "import collects he holes and moves them to the end blocks of the table" :-)
If this is problem, as far as I know, you can deallocate space using the "ALTER TABLE ... DEALLOCATE UNUSED ..." statement.

Regards
Laszlo

[ora_inf]

Thanks for your replay...

I tried the following command but did not help..
ALTER TABLE ... DEALLOCATE UNUSED ..

How can I get rid of the empty blocks? I think only way I can get rid of or less empty block, if I resize the next extents..

If you have any other suggetion, please let me know.


Thanks...

[jgmagnus]

the TABLE or the TABLE_SPACE?

thanks,
Magnus