9 Replies Latest reply on May 14, 2010 7:35 PM by 548150

    Why the segment management values set to MANUAL ?

    709798
      I installed the Oracle 11g database use DBCA and use the default configuration options. But I find that the segment management values of all the SYSTEM, UNDOTBS1 and TEMP tablespaces were set to MANUAL.
      Is these configuration the best choice?
      Why Oracle set these default values to MANUAL?
      SQL> select tablespace_name,initial_extent,next_extent,status,pct_increase,segment_space_management,retention,
      bigfile from dba_tablespaces;
      
      TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT STATUS    PCT_INCREASE SEGMEN RETENTION   BIG
      ------------------------------ -------------- ----------- --------- ------------ ------ ----------- ---
      SYSTEM                                  65536             ONLINE                 MANUAL NOT APPLY   NO
      SYSAUX                                  65536             ONLINE                 AUTO   NOT APPLY   NO
      UNDOTBS1                                65536             ONLINE                 MANUAL NOGUARANTEE NO
      TEMP                                  1048576     1048576 ONLINE               0 MANUAL NOT APPLY   NO
      USERS                                   65536             ONLINE                 AUTO   NOT APPLY   NO
      EXAMPLE                                 65536             ONLINE                 AUTO   NOT APPLY   NO
      
      SQL> SELECT * from v$version;
      
      BANNER
      ------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
      PL/SQL Release 11.1.0.6.0 - Production
      CORE    11.1.0.6.0      Production
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      NLSRTL Version 11.1.0.6.0 - Production
        • 1. Re: Why the segment management values set to MANUAL ?
          sb92075
          Is these configuration the best choice?
          Best for who based upon what criteria?
          Why Oracle set these default values to MANUAL?
          It does not matter why. It is what it is.

          If you want to change to AUTO, nobody can stop you.
          • 2. Re: Why the segment management values set to MANUAL ?
            530897
            sb92075 wrote:


            If you want to change to AUTO, nobody can stop you.
            Nobody but the database that is...

            The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace

            http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1154
            • 3. Re: Why the segment management values set to MANUAL ?
              530897
              I want to say that the SYSTEM, UNDO and TEMP tablespaces cannot have segment managment of auto. However I can only find a doc refernence, for CREATE TABLESPACE, that explicitly states segment managment auto is not valid for TEMPORARY tablespaces. I noticed that the same reference does not have a segment management option for UNDO tablespaces.


              Docs.. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

              The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace

              Undo tablespaces are not permanent tablespaces. Do not know why SYSTEM is not auto unless there is some reason why Oracle needs it to be manual.
              • 4. Re: Why the segment management values set to MANUAL ?
                sb92075
                Thanks for the update

                http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7003.htm#i2096553
                Restrictions on Automatic Segment-space Management This clause is subject to the following restrictions:
                    *      You can specify this clause only for a permanent, locally managed tablespace.
                    *      You cannot specify this clause for the SYSTEM tablespace.
                • 5. Re: Why the segment management values set to MANUAL ?
                  709798
                  Best for who based upon what criteria?
                  It seems that in your opinion Oracle choose the default value without any criteria (at random).
                  I don't think so. It's not a random decision by Oracle. There must be some reasons behind it.
                  The Oracle default value should be suitable for general purpose or for most of environment.
                  • 6. Re: Why the segment management values set to MANUAL ?
                    709798
                    Restrictions on Automatic Segment-space Management This clause is subject to the following restrictions:
                        *      You can specify this clause only for a permanent, locally managed tablespace.
                        *      You cannot specify this clause for the SYSTEM tablespace.
                    Why can't I set the Automatic Segment-space Management for non-permanent tablespaces such as UNDOTBS and TEMP tablespace?
                    Why can't I set the Automatic Segment-space Management for the SYSTEM tablespace ?
                    • 7. Re: Why the segment management values set to MANUAL ?
                      Andy Klock
                      This is a great question. I love it when someone asks, if a "feature" is so great why doesn't Oracle use it internally?

                      I used to really enjoy coming up with ways to determine what to set freelists to, that is until ASSM showed up in 9i. No matter what I did I could not beat (though I could get close) ASSM.

                      So why then is Oracle choosing (even in 11g) to still manually manage SYSTEM, TEMP, and UNDO tablespaces? The real reason is these tablespaces are different. Space is not (and does not) need to be allocated the same way permanent tablespaces is. Space is just managed differently with these tablespaces so there isn't a need to search freelists or bitmaps. In fact I think internally, a permanent tablespace set to "MANUAL" means something entirely different than the "MANUAL" you see for SYSTEM, TEMP, and UNDO in the DBA_TABLESPACES view.

                      But something to note, an error isn't thrown when I create a temp tablespace with ASSM:
                       SQL>create temporary tablespace temp2 tempfile 'C:\temp\temp02.dbf' size 50M extent management local segment space management manual;
                      
                      Tablespace created.
                      but,...
                      SQL>select tablespace_name,segment_space_management
                        2  from dba_tablespaces where tablespace_name = 'TEMP2';
                      
                      TABLESPACE_NAME                SEGMEN
                      ------------------------------ ------
                      TEMP2                          MANUAL
                      • 8. Re: Why the segment management values set to MANUAL ?
                        Andy Klock
                        Never mind. I don't read my own code... I thought I found something neat, but I did have it set to MANUAL.
                        SQL>create temporary tablespace temp2 tempfile 'C:\temp\temp02.dbf' size 50M extent management local segment sp
                        ace management auto;
                        create temporary tablespace temp2 tempfile 'C:\temp\temp02.dbf' size 50M extent management local segment space managemen
                        t auto
                        
                          *
                        ERROR at line 1:
                        ORA-30573: AUTO segment space management not valid for this type of tablespace
                        • 9. Re: Why the segment management values set to MANUAL ?
                          548150
                          The reason may be:
                          The BITMAP feature is not suitable for any unstable or fragile objects. For example, it not a right choice to create a bitmap index for a temporary table.
                          1 person found this helpful