Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> alter table move into temporary tablespace.
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