Re: ORA-03217 ALTER TABLESPACE TEMP PERMANENT; 9.2

Howard J. Rogers <hjr_at_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