Why is the SYSTEM tablespace datafile growing?

[Tarry]

You can only shrink it to the top of high water mark...

try these scripts to see hor far you can go to resize your datafiles....

set verify off
appdev@NICK817>column file_name format a50 word_wrapped
appdev@NICK817>column smallest format 999,990 appdev@NICK817>heading "Smallest|Size|Poss."
appdev@NICK817>column currsize format 999,990 heading "Current|Size"
appdev@NICK817>column savings  format 999,990 heading "Poss.|Savings"
appdev@NICK817>break on report
appdev@NICK817>compute sum of savings on report

column value new_val blksize

appdev@NICK817> select file_name,
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  3         ceil( blocks*&&blksize/1024/1024) currsize,
  4         ceil( blocks*&&blksize/1024/1024) -
  5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7       ( select file_id, max(block_id+blocks-1) hwm
  8           from dba_extents
  9          group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF                   55      264      209
H:\ORACLE\ORADATA\NICK817\RBS01.DBF                      30      200      170
F:\ORACLE\ORADATA\NICK817\TEMP01.DBF                      1       72       71
E:\ORACLE\ORADATA\NICK817\INDX01.DBF                      1       58       57
F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF                     1       12       11
G:\ORACLE\ORADATA\NICK817\USERS01.DBF                     1      108      107
                                                                     --------
sum                                                                       625

6 rows selected.

Then run this to resize them..

appdev@NICK817> column cmd format a75 word_wrapped
appdev@NICK817> 
appdev@NICK817> select 'alter database datafile '''||file_name||''' resize ' ||
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
  3  from dba_data_files a,
  4       ( select file_id, max(block_id+blocks-1) hwm
  5           from dba_extents
  6          group by file_id ) b
  7  where a.file_id = b.file_id(+)
  8    and ceil( blocks*&&blksize/1024/1024) -
  9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  /

CMD
---------------------------------------------------------------------------
alter database datafile 'E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF' resize
55m;

alter database datafile 'H:\ORACLE\ORADATA\NICK817\RBS01.DBF' resize 30m;
alter database datafile 'F:\ORACLE\ORADATA\NICK817\TEMP01.DBF' resize 1m;
alter database datafile 'E:\ORACLE\ORADATA\NICK817\INDX01.DBF' resize 1m;
alter database datafile 'F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF' resize 1m;
alter database datafile 'G:\ORACLE\ORADATA\NICK817\USERS01.DBF' resize 1m;

6 rows selected.

HTH

[JMac]

Thanks,