Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-1654 , Oracle asking for very large extent
Thank you. Seems I was wrong about the max-extent nolonger applies for
lmt tablespace. Is this true for 9i as well? I don't have access ot
unix now so can't test.
Can you do a :
select bytes, count(*) from dba_extents where segment_name='YOUR BIG
SEG' GROUP BY BYTES,
See at which stage, it start to use 512M? The last time I did some test
and I was unable to get to a larger size than 64M, with something like
500gb space; --data could be differnet, it was 3 years ago.
Also, I believe for next_extent, oracle won't break it up. If there is no free extent >= next_extent, oracle fails. if you can test to prove I was wrong, I appreciate it. Can't test now.
Thanks
Brian Peasland wrote:
> 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
Received on Mon Jul 31 2006 - 14:30:34 CDT