12 Replies Latest reply: Jan 19, 2013 5:49 AM by Osama_Mustafa RSS

    Free space requirement in Oracle database

    868690
      In my oraganization SAP application running with oracle as database. now our database is 1300 GB and right now 90 GB space free in database (92 % used). since System implementation we kept this free space upto 90GB by adding datafiles every week. I am newbie and want to know that how much free space oracle requires for better performance. can I stop adding datafile .
        • 1. Re: Free space requirement in Oracle database
          moreajays
          Hi,

          Free space in user tablespace has nothing to do with performance of the database
          You can keep upto 10% free space in tablespace , or may be lesser than that till you can avoid unable to extend tablespace error

          Thanks,
          Ajay More
          • 2. Re: Free space requirement in Oracle database
            Girish Sharma
            Your answer is Automatic Segment Space Management.
            Automatic segment space management simplifies space administration tasks, thus reducing the chance of human error. An added benefit is the elimination of performance tuning related to space management. It facilitates management of free space within objects such as tables or indexes, improves space utilization, and provides significantly better performance and scalability with simplified administration. The automatic segment space management feature is enabled by default for all tablespaces created using default attributes.
            http://docs.oracle.com/cd/E11882_01/server.112/e10803/config_db.htm

            In addition to that please read below link too, where similar discussion has been made and answered by Tom Kyte :
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1172174200346425760

            Regards
            Girish Sharma
            • 3. Re: Free space requirement in Oracle database
              868690
              Is there any oracle recommendation for how much free space should be kept?
              • 4. Re: Free space requirement in Oracle database
                Girish Sharma
                Is there any oracle recommendation for how much free space should be kept?
                You can read below link :

                Segment Advisor Recommendation Details in Oracle Enterprise Manager:

                http://docs.oracle.com/cd/B16276_01/doc/server.102/b14196/storage002.htm

                Regards
                Girish Sharma
                • 5. Re: Free space requirement in Oracle database
                  868690
                  But in SAP application,SAP has its own intelligency for database monitoring. we only add datafile in database for its space management.Now there is no space in our file system to add any of the datafile. In database 90GB out of 1300GB free. so what happen if database growth furher? database will get hang?
                  • 6. Re: Free space requirement in Oracle database
                    868690
                    Thanks you sir for your reply but pls guide me further.
                    • 7. Re: Free space requirement in Oracle database
                      moreajays
                      Hi,

                      No Specific Recommendation as such from oracle but being DBA we should keep sufficient free space in datafiles of the tablespace so that every segment held by the tablespace should be able to extent upto its next_extent bytes.
                      Main aim would be to avoid "Unable to extent error" , which can be still alleviated by adding the resumable_timeout setting to give some time for DBA's to add space in datafile or add datafile without failing the transaction (transaction will go in hang for the time specified in minute & till space gets added )

                      Thanks,
                      Ajay More
                      • 8. Re: Free space requirement in Oracle database
                        Aman....
                        user8694422 wrote:
                        But in SAP application,SAP has its own intelligency for database monitoring. we only add datafile in database for its space management.Now there is no space in our file system to add any of the datafile. In database 90GB out of 1300GB free. so what happen if database growth furher? database will get hang?
                        I am not sure at all what SAP recommends but there isn't any thing that says that X amount of space should be left free. Oracle has come out with the alert mechanism that can help you to get the alert when the free space goes below the specified limit that you mention but more than the performance, it's the availability IMO that's going to be effected. For example, if you have run out of space, you won't become more slow or faster but you won't have space to do any further writing and that's going to cause the operations to be halted as long as you won't solve the issue by adding more space or by adding new files. So , saying that database would hang would be incorrect, not operational, yes!

                        Just my 2 cents
                        Aman....
                        • 9. Re: Free space requirement in Oracle database
                          rukbat
                          Moderator Comment:

                          @ O.P.,
                          Your duplicate identical cross-posts to the MDM and the Solaris 9 forums have been removed.
                          You have been a member of these OTN forums long enough to know better.

                          Do NOT ever multi-post.
                          • 10. Re: Free space requirement in Oracle database
                            Girish Sharma
                            user8694422 wrote:
                            But in SAP application,SAP has its own intelligency for database monitoring. we only add datafile in database for its space management.Now there is no space in our file system to add any of the datafile. In database 90GB out of 1300GB free. so what happen if database growth furher? database will get hang?
                            I am not sure about SAP application, but you may check below link which is from SAP itself, just in continue to what Aman has posted :

                            Space Management with ORACLE
                            http://help.sap.com/saphelp_40b/helpdata/pt/08/5742084ae611d1894f0000e829fbbd/content.htm

                            Regards
                            Girish Sharma
                            • 11. Re: Free space requirement in Oracle database
                              Aman....
                              I think this statement,
                              Space management may not at first seem to be an area of relevance for high availability. However, it can, if neglected, lead to downtime
                              Says it all :) .

                              Regards

                              Aman....
                              • 12. Re: Free space requirement in Oracle database
                                Osama_Mustafa
                                you can start with Oracle Documentation to understand performance tuning concept
                                http://docs.oracle.com/cd/B19306_01/server.102/b14211/toc.htm