Re: Next Extents

jordan_at_pt5026.pto.ford.com (Dave Jordan) writes:
|> Scott Johnson wrote:
|>
|> >I just exported a table with 2 million rows and had the extent
|> >compressed. When I imported the table, the data was in 3 extents.
|> >I thought that all the data would be placed in one extent. Is there
|> >a limit to the size of an extent?
|>
|> Check to see that you don't have a MINEXTENTS value of 3. That would
|> account for the extra extents even if they were left empty after the
|> compression.
|>
|> David Jordan

The other thing to check is PCTFREE and PCTUSED on the table and whether the data had been subject to much updating after initial load. Not sure how to explain this clearly, but if PCTFREE is high then the data will initially be loaded to leave some space for future updates.

Assume many rows were updated over time and fill up the space reserved by PCTFREE. When the table is exported with COMPRESS=Y, it adds up all the space allocated by the extents for the table (not actual space used) and generates a CREATE TABLE statement in the export file with an INITIAL extent of that total size (but preserves the original PCTFREE). On import, every row is treated as an insert and so PCTFREE is again reserved in every data block for future updates to all the rows. So that could cause the new table to take up more datablocks than export calculated.

Hope this makes sense.

Roderick Manalac
Oracle Corporation Received on Sat Dec 10 1994 - 03:24:27 CET