Re: Changing TEMP tablespace from type PERMANENT to type TEMPORARY

<dmnwork_at_my-deja.com> wrote in message news:968rko$1dp$1_at_nnrp1.deja.com...
>
> > On an Oracle 8.0.5. database, someone made the TEMP tablespace to be
> > type PERMANENT. I'd like to change it to type TEMPORARY. Would I need
> > to take the database down and put it into mount mode to issue an alter
> > tablespace command on it?
>
>
> Thanks Howard and others.
>
> Turns out that you can't alter the tablepspace type to TEMPORARY unless
> it is empty; at the very least, Oracle 8.0.5.0.1 won't let you do it.

No, that makes sense (and I would imagine it would be true for *all* versions -the hall mark of a temporary tablespace is that it cannot house permanent objects.

>
> Any way to safely drop the objects in our temp tablespace while the DB
> is up? Or should we wait till off-hours, take the database down, bring
> it up in restricted mode, drop the objects in the tablespace, and then
> alter tablespace?
>
> I imagine seconds after dropping the objects, new objects might be
> created - although thankfully, we're doing most of our sorts in memory.
> Is it possible to alter tablespace type while a tablespace is OFFLINE?

It certainly is possible to a drop whilst the tablespace is offline.

Hence there is no need to take the database down to do the deed.

To drop a tablespace that actually has permanent objects, you can issue the command 'drop tablespace blah including contents'. If someone's been mad enough to stick foreign key constraints on other tables which reference the objects in that tablespace, then the full syntax would have to be 'drop tablespace blah including contents cascade constraints'.

Since you'd be dropping the entire tablespace, there's no possibility of new objects finding their way there whilst you're doing maintenance, and hence even taking the tablespace offline in the first place wouldn't be strictly necessary.

You'd better be certain that the objects in that tablespace really are surplus to requirements, though: there's no rollback on DDL commands like this!

Regards
HJR
>
>
> Thanks.
>
> - Dana
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Feb 13 2001 - 01:07:51 CST