How to shrink data file

[zhang liang]

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".

[Howard J. Rogers]

I wouldn't want to leap to conclusions, but since an Oracle database can
quite happily saunter along with a 100Mb SYSTEM tablespace, give or take,
the fact that yours has ever once been so big probably means that all sorts
of things were created in it that shouldn't have been (like real tables and
indexes)??

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

"zhang liang" <nov...@263.net> wrote in message
news:95avqf$1mva$1...@news.cz.js.cn...

[jdo...@my-deja.com]

> 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
>

When you resize a datafile, Oracle trims only off the END of the file.
So if you have objects which are located at the end of the file then
you must move them before you can shrink the file down beyond that
point. I do NOT recommend trying to move anything in the SYSTEM
tablespace, unless they are user objects (ie, not owned by SYS or
SYSTEM!)

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/

[N.N.]

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

Regards
Asko

zhang liang wrote in message <95avqf$1mva$1...@news.cz.js.cn>...

[Gordon Cartwright]

Your problem is that you have a lot of free space within the datafile, but it
isn't all at the end. There is a segment towards the back of the file. You
might be able to resize to a slightly larger size, or alternatively rebuild the
segment so it gets relocated. Have a look via enterprise manager to see where
the segment is.