1 2 Previous Next 15 Replies Latest reply: Apr 12, 2012 11:25 AM by Balazs Papp RSS

    System tablespace is 99% full

    Neo-b
      hello all,

      I created a new Oracle 11gR2 11.2.0.3.

      i am checking the Oracle Enterprise manager, i saw the my system tablespace size is 700 M and it is 99% full.

      Do i have to re size system tablespace datafile?
      does that affect my database, do i have to restart it ?
      99% full of system table space will affect my database performance?
      How can I specify the best size of my system tablespace?

      Regards,
        • 1. Re: System tablespace is 99% full
          sb92075
          NB wrote:
          hello all,

          I created a new Oracle 11gR2 11.2.0.3.

          i am checking the Oracle Enterprise manager, i saw the my system tablespace size is 700 M and it is 99% full.

          Do i have to re size system tablespace datafile?
          does that affect my database, do i have to restart it ?
          99% full of system table space will affect my database performance?
          How can I specify the best size of my system tablespace?

          Regards,
          does datafile for SYSTEM tablespace have AUTOEXTEND enabled?
          • 2. Re: System tablespace is 99% full
            EmaxG
            NB wrote:
            hello all,

            I created a new Oracle 11gR2 11.2.0.3.

            i am checking the Oracle Enterprise manager, i saw the my system tablespace size is 700 M and it is 99% full.

            Do i have to re size system tablespace datafile?
            does that affect my database, do i have to restart it ?
            99% full of system table space will affect my database performance?
            How can I specify the best size of my system tablespace?

            Regards,
            no need to restart the db, you can alter the datafile to resize it

            ALTER DATABASE DATAFILE 'name' SIZE X AUTOEXTEND ON NEXT Xm;

            With this command you can enlarge it a little bit and it will grow automatically when required in the future, taking that you have diskspace to do so.
            • 3. Re: System tablespace is 99% full
              Neo-b
              yes AUTOEXTEND is enabled.

              Should i resize ? or is it better to add a datafile to my system tablespace?

              Regards,
              • 4. Re: System tablespace is 99% full
                CKPT
                NB wrote:
                yes AUTOEXTEND is enabled.

                Should i resize ? or is it better to add a datafile to my system tablespace?

                Regards,
                How much its autoextended? If its reached to maximum datafile size you cannot resize.
                You can add datafile instead of that.
                • 5. Re: System tablespace is 99% full
                  EmaxG
                  NB wrote:
                  yes AUTOEXTEND is enabled.

                  Should i resize ? or is it better to add a datafile to my system tablespace?

                  Regards,
                  If autoextend is enabled and you have diskpace I wouldnt worry much about it.
                  • 6. Re: System tablespace is 99% full
                    878451
                    Hello,
                    if the datafiles from tablespace system are not Autoextend, i suggest you to resize the datafiles [there´s a post above showing the command] or create a new datafile for tablespace system:
                    alter tablespace system add datafile '<path of datafile>' size 100M autoextend on next 100M maxsize 1000M

                    I´ve assumed the size as 100Mb [you should change if want], and when it reaches 100M will raise more 100M, until gets at 1000M.

                    I hope that helps.

                    Regards,
                    • 7. Re: System tablespace is 99% full
                      EmaxG
                      CKPT wrote:
                      NB wrote:
                      yes AUTOEXTEND is enabled.

                      Should i resize ? or is it better to add a datafile to my system tablespace?

                      Regards,
                      How much its autoextended? If its reached to maximum datafile size you cannot resize.
                      You can add datafile instead of that.
                      i doubt this is the case as he states that the TBS size is 700MB and i assume its not maxsized! Most probably autoextend is small

                      Edited by: N K on 11-abr-2012 8:27
                      • 8. Re: System tablespace is 99% full
                        user594143
                        You also need to investigate what included in SYSTEM tablespace. Is any possibility that some application/data objects which do not belong to SYSTEM tablespace causing growth?
                        • 9. Re: System tablespace is 99% full
                          EmaxG
                          user594143 wrote:
                          You also need to investigate what included in SYSTEM tablespace. Is any possibility that some application/data objects which do not belong to SYSTEM tablespace causing growth?
                          Maybe, but i guess by default if nothing was specified they should to go USERS tablespace.
                          • 10. Re: System tablespace is 99% full
                            CKPT
                            i doubt this is the case as he states that the TBS size is 700MB and i assume its not maxsized! Most probably autoextend is small
                            Oh yes, i missed that word..

                            @OP,
                            use
                            SQL> alter database datafile 'file full location' autoextend on next 10m maxsize <value>;
                            • 11. Re: System tablespace is 99% full
                              Neo-b
                              Thank you..


                              It is only 700MB i can resize it, it did not reach it is max size. My question is does this affect negatively my database in any way ?
                              Is it better to resize or add a datafile.

                              What about my SYSAUX tablespace? it is also 99%, and it is AUTOEXTEND should i resize it ?

                              I just created my database it is new.

                              Regards,
                              • 12. Re: System tablespace is 99% full
                                CKPT
                                It is only 700MB i can resize it, it did not reach it is max size. My question is does this affect negatively my database in any way ?
                                Is it better to resize or add a datafile.
                                Any way it is fine, either resize or add datafile, But in autoextend on you are not performing resize you just enabling autoextend.
                                What about my SYSAUX tablespace? it is also 99%, and it is AUTOEXTEND should i resize it ?
                                First look for the option for autoextend if there is no option then think option of adding datafile.

                                same command example SQL> alter database datafile '/u01/app/oradata/prod/system01.dbf' autoextend on next 10m maxsize 32000m;
                                • 13. Re: System tablespace is 99% full
                                  UweHesse
                                  As others have said already: There is (most likely) no concern with those 99% full tablespaces.

                                  It is very important that you know some basic Oracle Database Admin concepts, though.

                                  My advice: go to tahiti.oracle.com and make yourself familiar with our very good Online Documentation.
                                  Look at the Administrators Guide for your version there.

                                  Your particular case is documented here:

                                  Oracle® Database Administrator's Guide
                                  11g Release 2 (11.2)

                                  15 Managing Datafiles and Tempfiles
                                  http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#i1106090

                                  Kind regards
                                  Uwe Hesse

                                  "Don't believe it, test it!"
                                  http://uhesse.com
                                  • 14. Re: System tablespace is 99% full
                                    jgarry
                                    That does seem to be the size system tablespace you get with a new db. After a couple of years, mine is still that size, though it appears something has come and gone, but I don't really care.

                                    Recent versions separate out various things into the sysaux tablespace. You do have to watch out that it doesn't start growing. dbconsole has an interesting tablespace map (and there are command lines ways also) to see what segments are in tablespaces. Don't worry too much about any apparent "fragmentation" (and I use that word loosely, and probably shouldn't) you see in the mapping of segments, but it is useful to be aware [url http://docs.oracle.com/cd/B28359_01/server.111/b28310/create004.htm#i1011308]what is in sysaux and why it might start growing. Autoextend is perfectly fine for these tablespaces.

                                    Edit: Auditing is special, and may cause the system tablespace to grow under certain circumstances.

                                    Edited by: jgarry on Apr 11, 2012 3:27 PM
                                    1 2 Previous Next