Re: ORA-1654 , Oracle asking for very large extent

Zhu Chao wrote:
> by 8192 is 8k block. Assume your blocksize is 8k, it is trying to
> allcoate a 64M extent;

So? Even if there were no 64MB chunk of free space, Oracle would break this up into subextents and allocate smaller pieces that come to a total of 64MB. With LMT's and AUTOALLOCATE, you are not guaranteed to get an extent the exact size you requested.

> So, run this;
>
> select count(*) from dba_free_space where tbalespace_name='L1 or I1?';
> and you know why it fails;
>
> reaching max extent will give another different error message;
> and max-extent sill apply in lmt tablespace.

MAX_EXTENTS does not apply in a LMT tablespace. The proof is in the pudding....

Verify my tablespace is LMT with AUTOALLOCATE (database is 10.2.0 on RHEL):

SQL> select tablespace_name,extent_management,allocation_type

   2 from user_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
BASIS                          LOCAL      SYSTEM
LANDFIRE                       LOCAL      SYSTEM

Create a table in the LANDFIRE tablespace:

SQL> create table all_objs

   2 tablespace landfire storage (maxextents 2)    3 as select * from all_objects;

Table created.

Verify the max extents for the table:

SQL> select segment_name,extents,max_extents

   2 from user_segments where segment_name='ALL_OBJS';

SEGMENT_NAME                           EXTENTS MAX_EXTENTS
----------------------------------- ---------- -----------
ALL_OBJS                                    20  2147483645

Oh look! Not only does MAX_EXTENTS show a very large number, but my table is 20 extents long! Oracle ignored my MAXEXTENTS value in the STORAGE clause! Just as I said it would.....

Pudding has been provided...proof offered up for all to partake! ;)

Cheers!
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown