Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Cannot shrink (seeming) emtpty tablespace
Hello all
I have a tablespace in which I don't find any extents:
SQL> select segment_name from dba_extents where tablespace_name='BOVWALLS001';
no rows selected
But I can't shrink the tablespace:
SQL> alter database datafile '/fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf' resize 10M;
alter database datafile '/fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf' resize 10M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Coalescing doesn't help, either (Though I'm not sure, if it should)
SQL> alter tablespace bovwalls001 coalesce;
Tablespace altered.
SQL> alter database datafile '/fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf' resize 10M;
alter database datafile '/fs1/IMSMARE/tbsp/BOVWALLS001_0.tsf' resize 10M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Querying uet$:
SQL> select ext#, block#, length from uet$ where ts#=4 order by block#;
EXT# BLOCK# LENGTH
---------- ---------- ----------
0 1102 20 0 1962 20 0 2042 20 0 6837 20 0 8917 20 0 9277 20 0 9457 20 0 9577 20 0 9597 20
9 rows selected.
Ok, there seems to be something in the tablespace (ts# = 4 is bovwalls001). But what is it and how do I get rid of it? MKReceived on Fri Jun 14 2002 - 04:30:52 CDT