Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> alter table move into temporary tablespace.

alter table move into temporary tablespace.

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Fri, 13 Dec 2002 17:56:01 GMT
Message-ID: <Pine.LNX.4.44.0212131150560.27465-100000@galt.rhadmin.org>


I'm trying to shrink a datafile, which has plenty of free space:

SQL> alter database datafile '/u02/oradata/s_amp.dbf' resize 1000M; alter database datafile '/u02/oradata/s_amp.dbf' resize 1000M
*

ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

This tablespace only has a few tables, so I:

alter tablespace temp permanent;

alter table x move tablespace temp (for all tables) alter table x move tablespace s_amp (for all tables) rebuild indexes

Now, not only has the ORA-03297 not gone away, but I can no longer alter TEMP to be a temporary tablespace:

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

How can I correct this?

SQL> select * from dba_segments where tablespace_name='TEMP';

OWNER                          SEGMENT_NAME
------------------------------ 
---------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                
HEADER_FILE HEADER_BLOCK      BYTES        BLOCKS
------------------------------ ------------------ 
------------------------------ ----------- ------------ ---------- 

   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -------

SYS                            14.1042
                               TEMPORARY          TEMP                                    
14         1042  494141440         30160
       464        1064960     1064960           1  2147483645            0          
1               1              14 DEFAULT


    ---------------------------------------------------------------------------
   / Charles J. Fisher              | "A fanatic is one who can't change his /
  /  cfisher_at_rhadmin.org            |  mind and won't change the subject."  /
 /   http://rhadmin.org             |                 -- Winston Churchill /
---------------------------------------------------------------------------
Received on Fri Dec 13 2002 - 11:56:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US