Re: create table initial max ?

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:41bc3f6a$0$11904$636a15ce_at_news.free.fr...
>
> <Kenneth Koenraadt> a écrit dans le message de
> news:41bc36e3.205390_at_news.inet.tele.dk...
>>
>> >>
>> >> I agree with Howard, this is a crazy request, but slightly funny, so
>> >> here goes:
>> >>
>> >> 1) Make sure the autoextend is off for all the datafiles of the TBS in
>> >> question.
>> >>
>> >> 2) Run this :
>> >> create table foo(c1 number(10)) tablespace footbs;
>> >> begin
>> >> while true loop
>> >> execute immediate ('alter table foo allocate extent');
>> >> end loop;
>> >> end;
>> >> /
>> >>
>> >> and when it hits ORA-01653, you're done.
>> >>
>> >> - Kenneth Koenraadt
>> >
>> >Not what he asked for, Kenneth. He asked to fill a tablespace by
>> >specifying as big an initial extent size as possible, not for a table to
>> >merely fill up all the available space in a tablespace by extending as
>> >often as possible.
>> >
>> >Regards
>> >HJR
>>
>> The result is the same.
>>
>
> No, it is not.
> In one case you have one big extent, that is the OP question,
> on the other one you have several extents.
> As it is a pure theorical and non-sense question but precise one
> and you want to answer it, reply in a precise way and not with a
> workaround.
> Workarounds are for practical issue.
>
> Btw, if the tablespace is fragmented, there is no answer to the OP
> question (one big initial
> extent).
> Btw, your solution does not work if there are many holes with different
> sizes unless
> you work with one block size extent.
>

Hi Michel

It's actually a little difficult to be precise when the question is so ambiguus.

The OP question again:

"I want to create table and assign it all free space from specified tablespace ? (as big INITIAL value as possible)".

He suggests accomplishing this by "as big INITIAL value as possible", nowhere does it say "one big extent", that's purely your interpretation. It's actually been a long time since Oracle necessarily demands that INITIAL be one big extent, since the invention of LMTs actually. Therefore you're assuming he's dealing with DMT which I would think is less likely than LMT. With LMT, Oracle will create as many extents as necessary in order to satisfy the INITIAL (and indeed NEXT, MINEXTENTS and PCTINCREASE) storage parameters.

So poor Kenneth may not necessarily be that far off the mark with his so-called imprecise interpretation.

In order to answer the question in a precise manner, we kinda need more information such as:

Are we dealing with a DMT or a LMT ?

If DMT, does the OP really mean one large extent or will many extents allocated to the table suffice for the purpose in mind ?

If DMT and many extents will do, does it matter how many extents we actually use or must it be the minimum possible or does it need to have a minimum extent value of some description ?

If LMT, are we using uniform or autoallocate as this will make a difference ?

Do we need to consider tablespace fragmentation or do we assume the tablespace has no fragmentation ?

Until we know the above answers, it's all a little up in the air really and any possible interpretation is as valid as the next I would have thought ?

BTW, your comment "your solution does not work if there are many holes with different sizes unless you work with one block size extent" is incorrect as Kenneth's solution would work fine with a uniform LMT (where the holes may indeed be different sizes, just a multiple of a uniform size).

Precision can be a bugger sometimes ....

Cheers ;)

Richard Received on Tue Dec 14 2004 - 05:48:06 CST