Very Computer

alter database datafile '/oracle/u01/oradata/wapdb/system01.rbf' resize
800M;

But the following error message was returned:

ORA-03297: file contains used data beyond requested RESIZE value

The same kind of error also occurs when I try to shrink file "rbs01.dbf",
which holds tablespace "rbs".

If you managed to avoid that one, have you ever switched on auditing?  If
so, audit records are written to AUD$ -which just happens to be located in
SYSTEM (but can, and should, be moved).

The trouble with any of that ever happening (apart from horrible performance
implications) is that the settings for things like initial, next and pctfree
are positively disgusting on SYSTEM.  The thing fragments just by looking at
it.  And what that means is that you have bits of good information scattered
throughout the tablespace amongst huge swathes of empty space.
Unfortunately, when you resize a file downwards, the slightest
partially-filled block in the way will halt the shrink.

What you would normally do for non-SYSTEM tablespace is do a re-org.:
Export everything, drop everything, re-import.  Guess what you can't do with
SYSTEM tablespace??

You can perhaps try and see which objects are where (DBA_EXTENTS, read
carefully, can tell you that), and perhaps if it's old bits of AUD$ you
could do it, but otherwise, I suspect you are stuck with a large SYSTEM
tablespace.

Regards
HJR

Quote:> After checking dba_free_space, I found that "system" tablespace has 1.67G
> free bytes. Since the data file for "system" tablespace is about 2.1G, I
try
> to use the following command to resize file system01.rbf to about 800M:

> alter database datafile '/oracle/u01/oradata/wapdb/system01.rbf' resize
> 800M;

> But the following error message was returned:

> ORA-03297: file contains used data beyond requested RESIZE value

> The same kind of error also occurs when I try to shrink file "rbs01.dbf",
> which holds tablespace "rbs".

Quote:> alter database datafile '/oracle/u01/oradata/wapdb/system01.rbf'
resize 800M;

> But the following error message was returned:

> ORA-03297: file contains used data beyond requested RESIZE value

For the Rollback, you can clean this up by dropping the rollback
segments which are at the end of your file, then shrinking your file,
then creating your rollback segments again.

The following query will produce a pretty map of where things are in
your tablespace :

Select owner segment_owner, Segment_name, partition_name, Block_ID,
File_id, Blocks, segment_type
from sys.DBA_Extents
where tablespace_name = 'your_tablespace'
union
Select null, 'Free Space', null, Block_ID, File_id, Blocks, null
from sys.DBA_FREE_SPACE
where tablespace_name ='your_tablespace'
order by 5,4

Sent via Deja.com
http://www.deja.com/

Try
 alter tablespace system coalesce;
This will coalesces into larger continuous extents and
then  you might be able to resize.

Regards
Asko