Re: Compute_statistics PROBLEM

> I'm with Pete on this one. I'd create a third TEMP while doing the repair
work ...
> not screw up another application. Then shift them back and drop the third
TS.
>
> Your comments will be appreciated.

It's a matter of principle. Something which is called a "default" is, as we all know and appreciate, something that gets used when nothing else fits the bill... DBA forgot to specify a temp. tablespace, storage clause wasn't specified for a table, etc etc.

All of a sudden, the database's "DEFAULT" temporary tablespace doesn't mean that at all. It now means "something I'll apply if you omit to mention an alternative, and something that might happen to all those accounts for which you *did* remember to specify something, but I don't care because that's the way I work".

That's not right. A default should be something to fall back on, not something which, by changing, you alter the details for (potentially) hundreds of users you've already carefully set up.

As a for example (and I realise this is distinctly old hat now), but when you used to set up dictionary managed tablespaces, and specified a default storage clause (let's say 'INITIAL 64K NEXT 64K')... suppose you then created a table (call it STATESOFAUSTRALIA) with a STORAGE cluase of (INITIAL 48K NEXT 48K). Our table has a perfectly proper STORAGE clause, so it makes no use whatsoever of the DEFAULT STORAGE clause at the tablespace level, and thus gets 48K extents.

Now as we all know, you can change the default storage clause for a tablespace at any time, and it has *no effect* on any segments already created (which have already picked up the extant default). But if it behaved like the new default temporary tablespace, then:

alter tablespace BLAH default storage (initial 48K next 48K);

...followed by:

alter tablespace BLAH default storage (initial 1M next 1M);

...followed by

alter tablespace BLAH default storage (initial 64M next 64M);

...then all of a sudden your original table, STATESOFAUSTRALIA, would have acquired a 64M next extent clause (and, god bless 'em, there are only 8 entries in this table [if you include territories]!).

Now, thank God, that *doesn't* happen with tables. So why the hell should it happen with default temporary tablespace assignments??

What of course has happened is that convenience has ridden roughshod over integrity (and consistency of behavior). You would only be altering the default temporary tablespace, so the theory (I imagine) goes, if you were about to drop it. But you can't drop the default temporary tablespace if it is actually *specified* as the default for any actual users. So rather than throw an error and make you go to the effort of altering the details of 500 users, I'll tell you what we'll do: we'll just completely redefine the meaning of the word 'default' and do, in effect, a cascading update. Never mind that we don't do cascading updates anywhere else in the system. Never mind that what you thought of as a 'default' has suddenly taken on a whole different meaning.

Personally, it's the worst thing Oracle have done since they took it into their heads that 'drop table DEPT cascade constraints' wouldn't do any damage to data, but 'drop user PETER cascade' would result in 5000 tables being silently dropped with no second chances. I never understood why the word 'cascade' should have both a totally innocent meaning and a totally devastating one.

But then Oracle invented PCTINCREASE aswell, so why should we be surprised when they throw things in our general direction??!

Regards
HJR Received on Mon Jan 27 2003 - 22:52:29 CST