Re: ORA-03217 ALTER TABLESPACE TEMP PERMANENT; 9.2

Christoph Kukulies wrote:
> I wrote down in my notes from 8.1 and pre 8.1 days that in case I'm getting
> the following error on import:
>
> "S 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) T
> "LESPACE "TEMP""
> IMP-00003: Oracle-Fehler 2195 gefunden
> ORA-02195: Versuch zum Erstellen eines PERMANENT-Objekts in einem TEMPORARY-Tabl
> espace
>
> that an
>
> ALTER TABLESPACE TEMP PERMANENT;
>
> would help.

Sheesh! There is an *awful* lot going on here. Mostly going wrong, I have to say.

ORA-02195 is searchable at tahiti.oracle.com, and yields the following description:

ORA-02195 Attempt to create string object in a string tablespace

     Cause: The object type is inconsistent with a tablespace contents.

     Action: Create an object in a different tablespace, or change the user defaults.

In plain language, it means that you are trying to create a real, permanent object in a temporary tablespace -and, by design and intent, temporary tablespaces can't have real, permanent objects created in them.

The usual cure for such an error would be to re-issue the 'create table' or 'create index' statement, not change the temporary tablespace into being a permanent one.

To understand why, you have to understand why Oracle invented temporary tablespace in the first place. People doing sorts that don't fit in memory have to swap partial sort runs to disk. They do so by allocating extents into which the partially-sorted data can be written. If those extents are allocated in permanent tablespace, then at the end of the sort, those extents are dropped... meaning that the next poor schmuck to run a report that involves a sort has to re-allocate them all over again. This means that when a permanent tablespace is used to house sort runs, the database spends an inordinate amount of time allocating and de-allocating extents. That is not good for performance, it means the data dictionary becomes a bottleneck, and it usually results in heavy tablespace fragmentation.

When proper temporary tablespace is used instead, the first person to sort causes extents to be allocated as before, but at the end of their sort, the extents are not dropped. They are merely marked for re-use. So subsequent sorters do not have to do extent allocations, and performance is better, the data dictionary isn't choked, and fragmentation doesn't happen.

And that is why you *want* a proper, temporary tablespace, and why it would have been utterly crazy, even in 8i, to want to change a temporary tablespace into being a permanent one just so you could house a table or an index in it.

I should also point out that the ability to change a temporary tablespace into being a permanent one, and back again, is only possible if you use the old-fashioned temporary tablespace. In 8i, you should actually have been using the locally-managed temporary tablespace, and that cannot be converted back and forth between temporary and permanent -and perhaps you can now see why that's a thoroughly good thing.

The old-fashioned temporary tablespace was created as follows:

create tablespace TEMP datafile '/xxx/xxx/xxx/temp01.dbf' size 100m temporary;

The "proper" 8i-style temporary tablespace was created like so:

create temporary tablespace temp tempfile 'xxx/xxx/xx/temp01.dbf' size 100m;

You'll notice that one uses regular old data files, and one new, shiny TEMPFILES. And there's your clue that an 8i-style temporary tablespace cannot be converted into a permanent tablespace:

So your notes are bizarre, because it would never have been sensible to convert a temporary tablespace into a permanent one for the reasons you were suggesting to do it. And they are also out-of-date, because in 8i, using locally-managed proper temporary tablespaces, you couldn't have done it anyway.

> It doesn't help me in 9.2. I'm getting:
>
> ORA-03217 ALTER TABLESPACE TEMP PERMANENT; 9.2
As I say, this is not a 9i issue. This is something you would have seen in 8i as well (you can search the Error Messages at tahiti.oracle.com in the 8i documentation for this error number, and you would find it:

ORA-03217 invalid option for alter of TEMPORARY TABLESPACE

     Cause: Invalid option for alter of temporary tablespace was specified.

     Action: Specify one of the valid options: ADD TEMPFILE.

I have to say that this is a classic example of some really bad error message writing. They tell you, correctly, that the error arises from trying to convert the 'create temporary tablespace..tempfile' style of temporary tablespace into permanent tablespace. But the 'Action' should then read: 'Don't be daft! You can't do this sort of thing to a proper temporary tablespace'. Instead, it assumes you actually were trying to add in a new data file, and helpfully points out that you can't add DATAfiles to a temporary tablespace -you have to add TEMPfiles instead,

> Any help appreciated. Side question: Could someone show me up the way
> which documentation I should have got to solve this problem juist by studying
> the docs. Which document should I consult in such a case?

That is actually a difficult question to answer, because your error goes a long way back, and the Error Message help that is searchable at http://tahiti.oracle.com only makes sense to someone who knows the difference between 'create tablespace temp...temporary' and 'create temporary tablespace temp'.

But you could have found a discussion on creating the two different sorts of tablespace in the 8i documentation here:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#4574

You could also have searched for the word 'tempfile' in the 8i documentation and discovered a couple of references in the concepts guide (which I don't think you've read recently, so I recommend it urgently before you go much further). This specific link is mentioned under one such search result:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c03space.htm#3917

In both cases, it is not explicitly clear that you cannot alter tempfile temporary tablespace into being permanent tablespace. Neither is it particularly clear why, in general, it was a daft thing to be doing in the first place even with dictionary-managed temporary tablespace. One has to infer the point that only dictionary-managed tablespace can be converted from the fact that the ability to 'alter tablespace X temporary' (and hence, by implication, 'alter tablespace X permanent') is only discussed, explicitly, in the dictionary-managed temporary tablespace section of the notes. And you similarly have to infer the desirability of having temporary tablespace (and therefore the ill-advisedness of converting it to permanent) from the general discussion of sort segment behaviour. In neither case is it stated in particularly unambiguous terms.

Just because if I don't mention it, someone is bound to point out that I missed something out, be aware that if you were using dictionary-managed temporary tablespace then (as I mentioned) its extents (known as 'sort segments') were not released at the end of sorts. The extents were only cleared, in fact, by SMON at shutdown time. That is good because it means sorters don't waste time allocating and de-allocating extents. But it was potentially bad as well, for two reasons.

First, a rogue query that does lots of sorting could cause huge numbers of extents to be allocated in an ever-growing temporary tablespace -and once allocated, that space stayed allocated. You wouldn't have been able to resize the datafiles back downwards, because extents would have been encountered on the way 'down'.

Second, it could mean that clean shutdowns took forever. SMON was busy de-allocating bazillions of extents in the temporary tablespace, and all of that had to be completed before the shutdown could proceed.

To cure either or both problems, it was common practice to alter the temporary tablespace back to being a permanent one -because this prodded SMON to do its extent de-allocation activities at a time and place of your choosing. With no extents in the temporary tablespace, you could therefore successfully downsize the tablespace. And since SMON had been manually invoked to clean the temporary tablespace out, a shutdown command issued shortly thereafter would have completed in reasonable time. And that is why the 'alter tablespace X permanent' command was invented... though in both cases it was always very swiftly followed up with an 'alter tablespace X temporary', to put the tablespace back into being a temporary tablespace.

With the invention of locally-managed temporary tablespace, the allocation of extents (and hence their de-allocation by SMON at shutdown) is a trivially cheap affair, and hence it should not take SMON too long to clean out the most heavily used temporary tablespace... so that eliminates one reason for the conversion command. And I imagine the idea with the TEMPFILE sort of temporary tablespace is that if it has blown out in size, you don't waste time reducing the size of the tempfiles, you simply create an entirely new temporary tablespace, and drop the original. TEMPFILES are created as sparse files, so their creation takes a second or two at most, no matter how big they are. So the management strategy for the two issues is now quite different, and that is why you don't need to convert 8i/9i TEMPFILE temporary tablespace into being permanent tablespace.

Regards
HJR
> --
> Chris Christoph P. U. Kukulies kukulies (at) rwth-aachen.de
Received on Thu Dec 02 2004 - 10:33:57 CST