Re: extent allocation?

In article <1992Oct28.165739.20340_at_gdstech.grumman.com>, un_at_gdstech.grumman.com (Un Fu) writes:
> When Oracle requests an extent of block size 210, does it
> require this extent to be in contiguous memory? Got the
> following error on one of my sql query that union few tables:
>
> ORA-01547: failed to allocate extent of size 210 in tablespace 'SYSTEM'
>
> Looked into the dba_free_space and saw many blocks of smaller sizes.
> Is there a way to tell Oracle to use multiple extents for the query?
> Also, is there a quick way to consolidate the memory without backup
> of the database?
>
> Thanks!
> --
> ****************************************************************************
> Un Un Fu Internet: un_at_gdstech.grumman.com
> Disclaimer: I am solely responsible for what I am saying here!
> ****************************************************************************

-- 
I saw a lot of wrong answers out there, so I thought I would clarify this a
little.  When Oracle requires a new extent, it looks for the first extent >= to
the size that it needs, breaks it up, if needed, and allocates.  When it can't
find an extent that is large enough, it goes through and starts trying to
allocate smaller, contiguous, extents into one that fits it's needs.  So, it
will use smaller extents to make a large extent.

Recently, I had a job of importing a 250 Meg database on a monthly basis.  Of
course, I would get ORA-1547 every time I ran it, but this was because I had a
large table being imported in after a small table had split the tablespace.  To
get around this, I had to "de-fragment" my tablespace into 1 large extent
again, before I started importing.  To do this, (assuming that my tablespace
was one datafile, exactly 250000 blocks.

CREATE TABLE BIG_TEMP STORAGE (INITIAL 250000 MINEXTENT 1);
-- table created
DROP TABLE BIG_TEMP;

et voile, my tablespace's extents were re-combined into one big extent, and 
my import would always succeed.

P.S. -- You could do the same thing for a TEMP tablespace and not run
into such errors during queries.
================================================================================
Scott Stephens				inet:	sstephen.us.oracle.com
Oracle WorldWide Support                Redwood City, California