Re: create table initial max ?

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

Nothing ambiguous about it, actually.

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

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

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

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

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

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

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

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

Regards
HJR
> Cheers ;)
>
> Richard
>
>
Received on Wed Dec 15 2004 - 11:57:04 CST