Why is the SYSTEM tablespace datafile growing?

Here's a good one.

I 'maintain' a database for a live app. Bit of a black box as it was delivered ready built. All I did was create some tablespaces and datafiles for the dev team to run scripts in.

Its 8i Standard edition, by the way.

The SYSTEM tablespace was created with a datafile of SIZE 100Mb.

I've noticed that the datafile size is growing. The actual size of the SYSTEM data isn't - just the file size. Over the last few weeks the size of the SYSTEM data has remained constant at 52Mb - But the filesize itself has grown from 138Mb (when I first noticed) in Dec '02 to 191 Mb as of today.

Using :
select file_name, (bytes/1024/1024)
from dba_data_files

I can see the SYSTEM.dbf being 191 Mb in size.

So if the data isn't being added to (ie) no new objects being created, how can the filesize increase?

Check to make sure that none of you users have the SYSTEM tablespaced designated as temporary tablespace.

Regards

I'd just thought of that!
Thanks Jovery I'll look

All of the users that the app creates in the DB have SYSTEM TBS as the DEFAULT AND TEMPORARY tablespace. Its the app itself that creates the DB Oracle users.

One of the first things in the post db creation is to push everyone out of that system tablespace as their default tblspace. Except the sys user.
That way you're better off..so to speak;)

Agreed.
Like I said, my involvement ended with creating the database and the tablespaces/datafiles.

Developers eh?

Tarry,
Would that include the SYSTEM user? I suppose it would, wouldn't it. I have SYS using XXX_TEMP TBS but SYSTEM uses SYSTEM as its TEMP TBS.

I know....
Tom(kyte)'s new book is kinda dedicated to trying to bring this two obnoxios folk together.

Tough job there, Tom. (Although I've pre-ordered that book, I hope it's more than just that..meaning guys please work together written over and over till the 600 pages..)
:D

Yep.
That includes system as well. Put him in tools or anyother that you made(or intend to make) for him/(her..eeps )

Okay Guys, so I move all users etc.

now my SYSTEM TBS shows as having 52Mb used. This should therefore be my data dictionary right? The datafile for this was originally 100Mb. I tried to resize the datafile (remember there's only 52Mb in there) and this happened:

SQL>ALTER DATABASE
2 DATAFILE
3 'E:\ORACLE\SPR1\SPR1_SYSTEM01.DBF'
4 RESIZE 100M
5 /
ALTER DATABASE
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

So what's happening? Has the DD been fragmented across more than 100 contigious Mb worth of blocks or what?

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

Code:

---

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

Code:

---


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

Thanks,