Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-1654 , Oracle asking for very large extent

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

From: Zhu Chao <zhuchao_at_gmail.com>
Date: 31 Jul 2006 12:30:34 -0700
Message-ID: <1154374234.148444.14530@i3g2000cwc.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US