Why is the SYSTEM tablespace datafile growing?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Why is the SYSTEM tablespace datafile growing?

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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?

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Check to make sure that none of you users have the SYSTEM tablespaced designated as temporary tablespace.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    I'd just thought of that!
    Thanks Jovery I'll look

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Guess What

    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.

  5. #5
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Agreed.
    Like I said, my involvement ended with creating the database and the tablespaces/datafiles.

    Developers eh?

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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 Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Yep.
    That includes system as well. Put him in tools or anyother that you made(or intend to make) for him/(her..eeps )
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  10. #10
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width