Oracle9i Automatic Space Management - a "feature"?
Don Burleson
SQL> create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 storage
7 ( pctfree 20 pctused 30 )
8 ;
( pctfree 20 pctused 30 )
*
ERROR at line 7:
ORA-02143: invalid STORAGE option
While Oracle9i rejects the PCTFREE and PCTUSED parameter with locally
managed tablespaces with automatic space management, it does allow you
to enter invalid settings for NEXT and FREELISTS settings:
SQL> create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 storage
7 ( freelists 30 next 5m ) ;
Table created.
This could be a serious issue for the Oracle professional unless they
remember that locally-managed tablespaces with automatic space
management ignore any specified values for NEXT and FREELISTS.
Am I missing something?
Richard Foote
Yes, you are missing something.
Not sure where to start with this so I guess the beginning might be the
place to go.
Firstly, note your first create table is *syntactically* incorrect (as the
error message suggests). PCTFREE and PCTUSED are *not* storage clause
options and need to be specified accordingly. You will get this error
message regardless of the type of tablespace.
Next, the FREELISTS, FREELIST GROUPS and PCTUSED and the only options
*ignored*. Note these parameters are ignored and do not generate errors so
that existing create table scripts and the such are compatible and will work
regardless of the type of tablespace. I don't see this as being an issue to
Oracle professionals as they would presumably have a reasonable
understanding of both the characteristics of a specified tablespace and how
such characteristics apply to objects in the tablespace (if they don't then
I question the "professional" bit of Oracle professional)
PCTFREE is still a valid option and is required for the growth of existing
rows in the blocks. This is just as important as ever for LMT with ASSM.
NEXT is not entirely ignored in that if the MINEXTENTS of the table were to
be 2 or greater, then the NEXT storage clause (in combination with
PCTINCREASE if 3 or more MINEXTENTS) is used to calculate the initial size
of the object and the appropriate number of extents are hence initially
allocated. Once allocated, then yes NEXT becomes irrelevant.
"Your Honour, Automatic Segment Space Management already has a bad enough
reputation in some circles without these unjustified accusations being
levelled against them and I request that these charges be dropped
immediately"
"Not guilty" ;)
Richard
"Don Burleson" <d...@burleson.cc> wrote in message
news:998d28f7.02082...@posting.google.com...
Howard J. Rogers
> This command is using an LMT with automatic space management:
>
> SQL> create table
> 2 test_table
> 3 (c1 number)
> 4 tablespace
> 5 asm_test
> 6 storage
> 7 ( pctfree 20 pctused 30 )
> 8 ;
>
> ( pctfree 20 pctused 30 )
> *
> ERROR at line 7:
> ORA-02143: invalid STORAGE option
>
Yup: PCTFREE and PCTUSED are *not* part of the storage clause at all. Never
have been, never will...
>
> While Oracle9i rejects the PCTFREE and PCTUSED parameter with locally
> managed tablespaces with automatic space management,
It most certainly does *not* "reject" them. It rejected them in this
particular case because your syntax was way up the Swanee. PCTFREE is a
perfectly valid setting for ASSM, just as it was for FLM (free list
managed) segments.
>it does allow you
> to enter invalid settings for NEXT and FREELISTS settings:
>
> SQL> create table
> 2 test_table
> 3 (c1 number)
> 4 tablespace
> 5 asm_test
> 6 storage
> 7 ( freelists 30 next 5m ) ;
Nothing particularly invalid there, either: it's documented that FREELISTS
when specified will be ignored for ASSM segments, otherwise every piece of
software out there would probably break. NEXT has nothing to do with ASSM,
but with the extent sizes allocated to a segment, and if your complaint is
that this is a LMT, and hence NEXT is meaningless... well, you're half way
up the Swanee there too. NEXT *is* "respected but not observed" when a
segment is first created in a LMT. Say you demand INITIAL 1M, NEXT 10M and
then say MINEXTENTS 2... it's obvious you want 11Mb of storage for this
segment, so Oracle divides that by the extent sizes the tablespace can
actually allocate (say, 1Mb) and thus gives you that number of extents (in
this case 11).
It works the same with INITIAL, by the way: you set INITIAL to 10M, in a
1Mb LMT, and you'll be allocated 11 1Mb extents.
>
> Table created.
>
> This could be a serious issue for the Oracle professional unless they
> remember that locally-managed tablespaces with automatic space
> management ignore any specified values for NEXT and FREELISTS.
>
Erm, well, yes, I guess it could be. But on the other hand, these things
are documented a-plenty, and can thus just be considered 'the way things
are', and any Oracle professional worth her salt would (reasonably, I
think) be expected to know that.
It can also equally validly be said that any decent Oracle professional
will want her head examined if she goes about using ASSM when she's not
running a RAC.
:-)
Regards
HJR
> Am I missing something?
Richard Foote
Spooky ;)
Richard
"Howard J. Rogers" <howard...@yahoo.com.au> wrote in message
news:3d6a...@dnews.tpgi.com.au...
Telemachus
Stop it at once ... you're not twins separated at birth..... are you ?
Howard J. Rogers
Nah. Richard things ASSM is OK, and I don't.
Besides, I'm the attractive one.
Regards
HJR
Sybrand Bakker
<howard...@yahoo.com.au> wrote:
>
>Besides, I'm the attractive one.
Or the most like David Bowie ;)
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Richard Foote
news:3d6b...@dnews.tpgi.com.au...
The more I see of ASSM, the less I like ...
>
> Besides, I'm the attractive one.
And this is going into my list of Oracle Myths ;)
You wish
Richard
>
> Regards
> HJR
Richard Foote
Howard and David Bowie have but one thing in common and that is, and that is
.... ummmmm, actually now I think about it, they have absolutely nothing in
common (to the relief of both I'm sure ;)
Regards
Richard
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:anqmmuk9804rblp4i...@4ax.com...
Niall Litchfield
news:MZKa9.16583$g9.5...@newsfeeds.bigpond.com...
> Hi Sybrand,
>
> Howard and David Bowie have but one thing in common and that is, and that
is
> .... ummmmm, actually now I think about it, they have absolutely nothing
in
> common (to the relief of both I'm sure ;)
(shome mishtake shurely)
I believe that should read "and that is, unappreciated by the world in
general never the less their unique talent is widely recognised by those in
the know."
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Pete Sharman
>
>"Richard Foote" <richar...@bigpond.com> wrote in message
>news:MZKa9.16583$g9.5...@newsfeeds.bigpond.com...
>> Hi Sybrand,
>>
>> Howard and David Bowie have but one thing in common and that is, and that
>is
>> .... ummmmm, actually now I think about it, they have absolutely nothing
>in
>> common (to the relief of both I'm sure ;)
>
>(shome mishtake shurely)
>
>I believe that should read "and that is, unappreciated by the world in
>general never the less their unique talent is widely recognised by those in
>the know."
I'd have to say that this probably applies more to Bowie than to HJR! :)
I shall now ensure my back is not turned whenever an outraged Richard is around.
Pete
>
>
>--
>Niall Litchfield
>Oracle DBA
>Audit Commission UK
>*****************************************
>Please include version and platform
>and SQL where applicable
>It makes life easier and increases the
>likelihood of a good answer
>
>******************************************
>
>
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Howard J. Rogers
>> Nah. Richard things ASSM is OK, and I don't.
>
> The more I see of ASSM, the less I like ...
>
>>
>> Besides, I'm the attractive one.
>
> And this is going into my list of Oracle Myths ;)
>
> You wish
>
True, true. Now I come to think of it, that hunch on your back is quite
appealing, in a bell-ringing kind of way. And I wish I had your bald spot,
to help me find the way in the dark.
So yes, I retract.
(By the way, where do you get the bags under your eyes pleated so nicely?)
Cheers
HJR
> Richard
>
>>
>> Regards
>> HJR
Richard Foote
> >
> > The more I see of ASSM, the less I like ...
> >
> >>
> >> Besides, I'm the attractive one.
> >
> > And this is going into my list of Oracle Myths ;)
> >
> > You wish
> >
>
>
> True, true. Now I come to think of it, that hunch on your back is quite
> appealing, in a bell-ringing kind of way. And I wish I had your bald spot,
> to help me find the way in the dark.
As you well know my friend, the rather expensive plastic surgery since my
"ugly years" has me looking a treat, in a Mel Gibson meets Tom Cruise sort
of way.
Incidently, times were rather difficult during my "ugly years" and bell
ringing was about the only job I could do competently (I used to uniquely
head butt the bell to get the perfect chime I was after). However, I soon
tied of people constantly coming up to me in the street saying "Excuse me
mate, I can't recall your name but your face rings a bell ;)
Oh dear ....
Richard
Tupaclives997202
This looks like fun and it's FREE!!! FREE!! FREE!!
I already have earned $100.00.
Just print this letter and follow the directions slowly and carefully.
If you do, you will see that it is not difficult. It takes a little time so be
patient....AND ENJOY those dollars coming into your paypal account.
You don't need to open a paypal account until you get your first letter
from PayPal saying that you have YOUR money there.
www.paypal.com is the world's leading secure money transfer company and
free to join.
This is NOT A CHAIN LETTER TO COLLECT MONEY!
THIS IS TO GIVE MONEY!!!!
Chain Letter collecting money is ILLEGAL!
It looks like a chain letter but it is really like an MLM unilevel
where you can put as many people on your first level as you
are capable of finding, and the company pays you for each
referral you get.
THIS COMPANY IS NOT COLLECTING MONEY;
IT IS GIVING MONEY TO YOU.
This is purely LEGAL!!!!
THIS WILL COST YOU NOTHING!
You get $100 just for joining and the referral potential
earnings are INCREDIBLE!
You will receive an email from paypal when the money arrives at paypal
and paypal shows you how to get the money easily.
Never SEND, Never SPEND, and Never being ASKED for money but stil
you can make THOUSANDS!
I know what you're thinking, "Yeah right..... I've heard that before!..."
Well, this is absolutely different!
!!!!!! IT DOES NOT COST YOU ONE CENT !!!!!!!
As a matter of fact you get paid $100.00 just for signing up (no strings
attached).
With a small amount of effort, in fact you can make THOUSANDS!
JUST FOLLOW 4 STEPS BELOW CAREFULLY:
STEP 1___________________________________
Listed below are three NAMES of email addresses and sign-up URLs.
Sign up at RichMail.com under the Name#1 and you'll get $100 just for joining.
(If click doesn't work, copy paste that complete URL line into your browser and
enter/go)
STEP 2___________________________________
Copy and forward this email to the THIRD person on the list BUT put in the
Subject line:
"I signed up under #1".
This is important for confirmation.
STEP 3__________________________________
Delete NAME#1
Rename NAME#2 to NAME#1
Rename NAME#3 to NAME#2.
Now add your name to the list as NAME#3
(You will receive your URL information from Richmail when you sign up.)
IMPORTANT
Make sure you type this information correctly (copy and paste where possible)
STEP 4_____________________________________
Send this letter to as many people as you can until you receive 5 emails
saying, "I signed up under #1".
____________________________________________
When you receive back 5 responses you have made it.
When your address gets to the position #1, then you will have 125 people
signed up under you! You only need 5 to make this work, but imagine if you had
10 or 15!!
Not bad, but it's ONLY the beginning. A short (very short) time later,
each of those 125 people will have 125 people signed up under them, and so on.
After the 3rd Level is done you are IN THE MONEY! (Rich Mail pays you for
everyone that subscribes under you....and it adds up fast.! They make their
money from their advertisers. (Pure LEGAL business).
Here is the list of names:
_____________________________________________
NAME #1
http://www.RichMails.com/cgi-bin/signup.cgi?r=black...@yahoo.co.nz
contact email: black...@yahoo.co.nz
NAME #2
http://www.richmails.com/cgi-bin/signup.cgi?r=KHy92...@aol.com
contact email: KHy92...@aol.com
NAME #3
http://www.RichMails.com/cgi-bin/signup.cgi?r=tupacliv...@cs.com
contact email: tupacliv...@cs.com
______________________________________________
CAUTION: "IF YOU CHEAT, YOU LOSE."
It must cycle through all 3 levels to work.
NO SPAM !!!!! ZERO TOLERANCE
REMEMBER SPAMMING IS ILLEGAL!!
.
So go now to URL listed under #1 and sign up to get started.
Calculation of earnings:
Your own sign up $100
First Level is 125 x $25 = $3,125
Second Level is 3125 x $25 = $78,125
Third Level is 78125 x $4 = $1,953,125
TOTAL POTENTIAL EARNINGS $ OBSCENE!!
Cost to you: NOTHING!!! but the 15 minutes a day to send your emails!
Remember, "Success is 99% Perspiration and 1% Inspiration.
If you tell yourself you are a winner, you will eventually become one.
Until then.... practice!"
You can just check to confirm it.
Have a great day!