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

I was wrong about the max-extent, but right on next_extent; not much usage to dig that detail, but it is that;

--build tablespace a with blocksize 8k, 101m. create table a as select * from dual;
create table b as select * from dual;
for i in 1..10 loop execute immediate 'alter table a, b allocate extent';
while true loop execute immediate alter table a allocate extent; ...then it fails when run out of space;

now I drop table b, there is free space big enough for next_extent a need, it is 5177k. next_extent for a is 4m. but it fails.

SQL> SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='A'; SUM(BYTES)

   5177344

SQL> SELECT 512*8192 FROM DUAL;   512*8192

   4194304

SQL> ALTER TABLE A ALLOCATE EXTENT;
ALTER TABLE A ALLOCATE EXTENT
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.A by 512 in tablespace A

Sybrand Bakker wrote:
> On 31 Jul 2006 12:30:34 -0700, "Zhu Chao" <zhuchao_at_gmail.com> wrote:
>
> >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.
> >
>
>
> max extents never applied to lmt's.
> >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.
>
> You definitely describe DMT policies
> >
> >Thanks
> >
>
> Thanks for top-posting and including everything.
> No other posters have to do the cut-and-paste operations that were too
> much asked from you.
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Mon Jul 31 2006 - 15:29:10 CDT