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.

It doesn't help me in 9.2. I'm getting:

ORA-03217 ALTER TABLESPACE TEMP PERMANENT; 9.2

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?

--
Chris Christoph P. U. Kukulies kukulies (at) rwth-aachen.de

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

Sounds like you have some objects in the database you are migrating
from that are in the TEMP tablespace. Not recommended but I guess
that's where you are currently.

How many objects are in TEMP? Can you move them out of TEMP to a more
"usual" tablespace. That should fix your export/import problem.

In 9.2 an alternate approach that "might" work would be to create a
different temporary tablespace named TEMP2, then ditch TEMP, recreate
TEMP as a permanet tablespace. But that just perpetuates a problem
that should be fixed.

First a big thanks for Howard in his previous very good and
elaborate explanation, possibly too valuable for me mundane
casual Oracle user/dba/whatever you name it. But I begin to understand and
like working on that stuff :-) and I really appreciate.

John Hurley <johnbhurley@sbcglobal.net> wrote:
> Sounds like you have some objects in the database you are migrating
> from that are in the TEMP tablespace. Not recommended but I guess
> that's where you are currently.

> How many objects are in TEMP? Can you move them out of TEMP to a more
> "usual" tablespace. That should fix your export/import problem.

> In 9.2 an alternate approach that "might" work would be to create a
> different temporary tablespace named TEMP2, then ditch TEMP, recreate
> TEMP as a permanet tablespace. But that just perpetuates a problem
> that should be fixed.

The problem is the following:

The remote site, the customer, sends me an export file (.dmp)
and I cannot read it into a freshly created instance for that reason.
I cannot do anything about the way the customer exported the file.
At least not without another day turnaround time.

--
Chris Christoph P. U. Kukulies kukulies (at) rwth-aachen.de

Christoph Kukulies wrote:
> First a big thanks for Howard in his previous very good and
> elaborate explanation, possibly too valuable for me mundane
> casual Oracle user/dba/whatever you name it. But I begin to understand and
> like working on that stuff :-) and I really appreciate.
>
> John Hurley <johnbhurley@sbcglobal.net> wrote:
>
>>Sounds like you have some objects in the database you are migrating
>>from that are in the TEMP tablespace. Not recommended but I guess
>>that's where you are currently.
>
>
>>How many objects are in TEMP? Can you move them out of TEMP to a more
>>"usual" tablespace. That should fix your export/import problem.
>
>
>>In 9.2 an alternate approach that "might" work would be to create a
>>different temporary tablespace named TEMP2, then ditch TEMP, recreate
>>TEMP as a permanet tablespace. But that just perpetuates a problem
>>that should be fixed.
>
>
> The problem is the following:
>
> The remote site, the customer, sends me an export file (.dmp)
> and I cannot read it into a freshly created instance for that reason.
> I cannot do anything about the way the customer exported the file.
> At least not without another day turnaround time.

Oh, OK. It's your customer that needs a good kick in the behind, then! :-)

The only thing you can really do is as John said, I think. Create a new
one that is genuinely temporary but called something like 'REALTEMP',
drop the original TEMP tablespace, and then create a new, permanent,
ordinary tablespace that happens to have the name 'TEMP' (or whatever
tablespace your import is actually trying to create these objects in).

Because you're on 9i, you won't be able to drop TEMP if it's the
database's default temporary tablespace, so you may first have to do:

alter database default temporary tablespace REALTEMP;
drop tablespace TEMP;
create tablespace temp datafile 'xxx/xxxx/xxxx.dbf' size 100M (or
whatever)...
Then do your import.

It is obviously a management disaster to have a tablespace called "TEMP"
which isn't, so you'd then want to whip the objects out of there as
quick as you can into a proper tablespace with a proper name... but
since this is your customer that has stuffed up so badly, it sounds like
you will have to become practised in mental gymnastics and live with a
very, very awkward situation.

Regards
HJR

Howard J. Rogers <hjr@dizwell.com> wrote:
> Christoph Kukulies wrote:
> > The problem is the following:
> >
> > The remote site, the customer, sends me an export file (.dmp)
> > and I cannot read it into a freshly created instance for that reason.
> > I cannot do anything about the way the customer exported the file.
> > At least not without another day turnaround time.

> Oh, OK. It's your customer that needs a good kick in the behind, then! :-)

> The only thing you can really do is as John said, I think. Create a new
> one that is genuinely temporary but called something like 'REALTEMP',
> drop the original TEMP tablespace, and then create a new, permanent,
> ordinary tablespace that happens to have the name 'TEMP' (or whatever
> tablespace your import is actually trying to create these objects in).

> Because you're on 9i, you won't be able to drop TEMP if it's the
> database's default temporary tablespace, so you may first have to do:

> alter database default temporary tablespace REALTEMP;
> drop tablespace TEMP;
> create tablespace temp datafile 'xxx/xxxx/xxxx.dbf' size 100M (or
> whatever)...
> Then do your import.

OK, that would enable me to do the import. What can I ask the customer to do
to give me a picture of his situation.

What is his default tablespace?
What is his temporary table space?
How does he move his objects out of that screwed temp space into a normal one?

> It is obviously a management disaster to have a tablespace called "TEMP"
> which isn't, so you'd then want to whip the objects out of there as
> quick as you can into a proper tablespace with a proper name... but
> since this is your customer that has stuffed up so badly, it sounds like
> you will have to become practised in mental gymnastics and live with a
> very, very awkward situation.

--
Chris Christoph P. U. Kukulies kukulies (at) rwth-aachen.de

On Fri, 03 Dec 2004 10:16:37 +1100, "Howard J. Rogers"
<hjr@dizwell.com> wrote:

>> The problem is the following:
>>
>> The remote site, the customer, sends me an export file (.dmp)
>> and I cannot read it into a freshly created instance for that reason.
>> I cannot do anything about the way the customer exported the file.
>> At least not without another day turnaround time.
>
>Oh, OK. It's your customer that needs a good kick in the behind, then! :-)
>
>The only thing you can really do is as John said, I think. Create a new
>one that is genuinely temporary but called something like 'REALTEMP',
>drop the original TEMP tablespace, and then create a new, permanent,
>ordinary tablespace that happens to have the name 'TEMP' (or whatever
>tablespace your import is actually trying to create these objects in).

Sounds like he is creating a fresh database before import.
In that case the "CREATE DATABASE" statement could just be changed
regarding the name of the "DEFAULT TEMPORARY TABLESPACE" to something
else, and then of course create at PERMANENT tablespace named TEMP
A bit crazy though!

If this is done often it could be a good idea to have a "database
template" i.e. all the datafiles, controlfiles and redofiles before
the import backed up somewhere. Then just restore the template before
next import.

Hans Erik Busk
Denmark

Christoph here's a couple ideas for you.

My first question is what kind of application is designed so that your
customer sends in an export file for you to process? There are many
alternatives and options available these days ... creating an xml
file, using a comma delimited flat file format, etc. Give us some
more information about how and why you are faced with dealing with
importing an export file in the first place.

The second question I have is what specific information does your
application require out of that export file? It appears that you are
doing a full import is that correct? What are the reasons for doing a
full import? Can you just import certain tables or certain schema's?

You yourself can identify what it is that your customer is sending you
that ends up in the temp tablespace. Doing a query against DBA_TABLES
is one approach. It may very well be that for some reason there is
just a small number of things coming in temp that can be deleted from
the source database(s). Identify all the things in temp before the
import (better be none), identify all the things after the import, and
work on eliminating all of them.

Did you ask how to move things out of the temp tablespace? I am
hoping that anyone doing database imports knows how to create tables
and other oracle objects. What specific questions if any do you have
in this area?