Re: ORA-03217 ALTER TABLESPACE TEMP PERMANENT; 9.2

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 03 Dec 2004 10:16:37 +1100
Message-ID: <41afa24e$0$20859$afc38c87@news.optusnet.com.au>

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_at_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 Received on Thu Dec 02 2004 - 17:16:37 CST