Why is the SYSTEM tablespace datafile growing?

[JMac]

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?

[jovery]

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

Regards

[JMac]

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

[JMac]

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.

[Tarry]

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

[JMac]

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

Developers eh?

[JMac]

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.

[Tarry]

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

[Tarry]

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

[JMac]

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?