Re: create table initial max ?

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Wed, 15 Dec 2004 20:51:23 GMT
Message-ID: <ft1wd.74106$K7.48263@news-server.bigpond.net.au>

Final comments embedded

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41c07ae6$0$13948$afc38c87_at_news.optusnet.com.au...
> Richard Foote wrote:

>> "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.
>

> Nothing ambiguous about it, actually.

Hi Howard,

So you think ...

>
>> 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",
>

> Right. And that means poking around inside dba_free_space, finding out
> what the biggest extent is, and creating a table with INITIAL=that. Quite
> what's ambiguous about that, I don't know.

>
>> nowhere does it say "one big extent", that's purely your interpretation.
>

> And it's irrelevant.

No, *I* think it's a rather important point to highlight. Michel thinks INITIAL means one large extent when it doesn't necessarily mean that at all.

>
>> 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.
>

> What a lot of lather... there's no assuming necessary, Richard. The guy
> wants to create a table with as big an initial as possible. That is a
> request that could be made of DMT or LMT.

*Your* opinion. *I* rather thought it a point worth making to Michel.

>
>> With LMT, Oracle will create as many extents as necessary in order to 
>> satisfy the INITIAL (and indeed NEXT, MINEXTENTS and PCTINCREASE) storage 
>> parameters.
>

> Precisely. The *answer* in LMT will not be what he wants. But the question
> is as valid (and as stupid) in LMT as it is in DMT.

And a point *I* thought worth making, that the *answer* *may* not be what he wants.

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

> Come off it. Poor Kenneth was merely creating lots of extents to fill up
> the tablespace until it errored out. Where was the answer to the specific,
> clear, unambiguous request: how can I create a table with as big an
> INITIAL as possible? Do-able as a piece of create table syntax in both DMT
> and LMT, Kenneth's answer didn't even try.

True. But if the results are the same (a table that has consumed all available space in many extents as a LMT would achieve) then perhaps it's a solution worth mentioning and although *you* find it somehow a terrible thing, *others* may find it useful.

>
>> 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 ?
>

> No you don't. You just need to stop faffing around and answer the question
> as it was actually asked. How does he issue a 'create table' statement
> with as big an INITIAL as possible?

Of course you do. Howard, you are in the land of plain *wrong*. With a DMT, INITIAL can only be as large as the largest piece of contiguous free space. With LMT, potentially *all* free space can be consumed by an INITIAL. A rather big difference don't you think ?

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

> I see the silly season has descended early on Canberra. Your question kind
> of ignores the one originally asked.

Does insults really achieve anything ? Howard, read on ...

>
>> If DMT and many extents will do,
>

> And why should it. All he wants is a create table statement that says
> INITIAL <some large number>. Never mind if Oracle pays attention to that
> request or not. How does he write the create table statement so it does
> that?

*Assuming* he means DMT and *assuming* he want to fill the tablespace up (higher priority) than simply use INIITIAL (lower priority as it might *not work) how many extents would be preferrable. A point (that being INITIAL may not work with DMT) you seem not to mention and a point worth making *if* filling up the tablespace is the more important requirement.

>
>> 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 ?
>

> NOT TO ANSWERING THE ACTUAL QUESTION IT WON'T!!
Again "shouting is deemed a little rude and perhaps unnecessary. It may matter again because using INITIAL may *not work* with *autoallocate* if filling the tablespace is the main objective. So *I* thought it worth mentioning. If depends on how badly fragmented the tablespace might be.

>
>
>> Precision can be a bugger sometimes ....
>

> How about trying it sometime? Stop larking about in the land of 'let's see
> if we can think up as many objections and caveats as possible', read words
> with their plain, ordinary meanings. And answer the question that was
> actually asked. That's the only degree of precision actually needed.

And now you conveniently snip the part where Michel is wrong and I correct him. And you call me imprecise but don't raise "your voice" when Michel is wrong (like you were earlier).

I tried to be precise and add to discussion in this thread. I honestly don't think I'll bother posting anymore, it's sadly just not as much fun around here as it once was.

Adios !!

Richard Received on Wed Dec 15 2004 - 14:51:23 CST