Re: alter table move into temporary tablespace.

On Fri, 13 Dec 2002, Sybrand Bakker wrote:

> >alter tablespace temp temporary
> >*
> >ERROR at line 1:
> >ORA-01662: tablespace 'TEMP' is non-empty and cannot be made temporary
> >
> >How can I correct this?

> By moving the permanent objects like tables and indexes out of the
> TEMP tablespace or just drop them.

I tried to be very careful about this, and it appears that I was. Perhaps I have to wait for SMON to clean something up?

SQL> select count(*) from dba_tables where tablespace_name='TEMP';

  COUNT(*)

         0

Elapsed: 00:00:00.94
SQL> c/tables/indexes
  1* select count(*) from dba_indexes where tablespace_name='TEMP' SQL> /   COUNT(*)

         0

Elapsed: 00:00:03.41
SQL> alter tablespace temp temporary;
alter tablespace temp temporary
*
ERROR at line 1:
ORA-01662: tablespace 'TEMP' is non-empty and cannot be made temporary

Elapsed: 00:00:00.08

    / Charles J. Fisher              |"Whoever undertakes to set himself up  /
   /  cfisher_at_rhadmin.org            | as a judge of Truth and Knowledge    /
  /   http://rhadmin.org             | is shipwrecked by the laughter of   /
 /                                   | the gods." -Albert Einstein        /
--------------------------------------------------------------------------