Re: alter storage of tabelspace influences table storage params????

Tables don't have default storage parameters. They have operational storage parameters; the real thing; the definitive statements.

What I mean by that is this: when you create a table with no storage clause at all, the table hares off to the tablespace and looks to see what the tablespace's default storage clause is set to. Those settings are then adopted by the table *as though you'd specified them in a true storage clause*. That's what defaults are for: if I don't specify, what do I fall back on. But once I've fallen back on them, the defaults are now mine.

So what was a tablespace default becomes a segment's own, true storage clause, which you can actually see happening by looking at DBA_TABLES. There's not a regular table listed there that doesn't have its own, independent, values for initial, next, minextents and so forth.

And it therefore follows that you can now play around with the tablespace's default storage clause as much as you like, and not a single existing segment will be aware of the fact: they've already got their own storage clauses, thanks very much.

So, explicitly is the name of the game. Which I then have to tell you is excruciatingly bad DBA practice, since what you want to achieve above all else is consistent extent sizes within a tablespace, and the one thing that monkeying around with storage clauses is guaranteed to do is result in inconsistent extent sizes.

If you are tempted to alter a table's minextents or next extent setting, drop the table and re-create it in a different tablespace (you may want to export the data first, of course!). Wanting to change either indicates that you got the extent sizing wrong in the first place. The table therefore doesn't belong in its existing home, but needs to be moved to somewhere where the tablespace storage defaults is more amenable to its actual growth habits.

Of course, the beauty of locally managed tablespace is that worrying about all this sort of stuff is totally unnecessary (not to say, practically and syntactically impossible). Guess what? In 9i release 2, you can no longer create tablespaces with this sort of storage clause. Everything is locally managed, and specifying initial, next, minextents and pctincrease are a thing of the past. Whoooo-hooo!

Regards
HJR "Martijn Rutte" <opal_at_xs4all.nl> wrote in message news:aa4da990.0205280552.3a09dd1b_at_posting.google.com...
> After I changed the default storage parameters of a tablespace, do the
> default storage params of the tables created on this tablespace change
> too automatically(supposing that I did not specify any storage params
> while creating those tables initially, i.e. silently accepting the
> initial settings of the tablespace for all tables too)?
>
> Or do I have to change the storage params of the tables explicitly?
>
> Martijn
Received on Tue May 28 2002 - 15:57:51 CDT