Re: How to shrink data file

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" <novazl_at_263.net> wrote in message news:95avqf$1mva$1_at_news.cz.js.cn...
> 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".
>
>
>
>
>
Received on Thu Feb 01 2001 - 14:46:31 CST