1 2 Previous Next 15 Replies Latest reply: Jan 6, 2013 7:00 AM by 846231 RSS

    UNDO_TABLESPACE

    846231
      Hi all,

      Is it ok if I change my UNDO_TABLESPACE intial_exten, next_extent to 1M by 1M? (maybe equivalent to RBS_BIG) the default is 100k by 65k
      This is intended for long runnings jobs which creates lots of extents when reading large amount of data.


      Thanks a lot,

      Kins
        • 1. Re: UNDO_TABLESPACE
          Fran
          Initial Extent --> Default initial extent size
          Next Extent --> Default incremental extent size

          So, no need to modified Initial Extent value after tablespace is created.
          This is intended for long runnings jobs which creates lots of extents when reading large amount of data.
          the amount of extents you have mean nothing. You only have to set UNDO_TABLESPACE to a enough size and/or set UNDO_RETENTION to a enough value to this jobs.

          Edited by: Fran on 03-ene-2013 23:37
          • 2. Re: UNDO_TABLESPACE
            846231
            This is needed for creation of bigger undo segments right? the undo segments will inherit the initial and next extent of the tablespace owner.
            • 3. Re: UNDO_TABLESPACE
              Fran
              not exactly, it will vary the number of undo tablespace extents.
              for example:
              if you have undo tablespace size to 100M and initial extent in 1M and next extent in 1M, you will have 100 extents in it.
              if you have undo tablespace size to 100M and initial extent in 10M and next extent in 10M, you will have only 10 extents.

              just only less reallocations on disk.

              The undo tablespace is configured with this parameters, if you change any the follow changes will get this new values.
              • 4. Re: UNDO_TABLESPACE
                moreajays
                Hi,

                It is good idea to keep big size extents of UNDO tablespace to support huge transaction & for oracle it will be less overhead while allocation/re-allocation.
                But in Local managed undo tablespace created by oracle (default) will not contain any value specified to NEXT_EXTENT , check if you can re-create it with customized extent value


                Thanks,
                Ajay More
                http://moreajays.blogspot.com
                • 5. Re: UNDO_TABLESPACE
                  JohnWatson
                  It's impossible to specify extent sizes for an undo tablespace:
                  orcl> create undo tablespace u2 uniform extent size 1m;
                  create undo tablespace u2 uniform extent size 1m
                                            *
                  ERROR at line 1:
                  ORA-30024: Invalid specification for CREATE UNDO TABLESPACE
                  
                  orcl> create undo tablespace u2 default storage(initial 1m next 1m);
                  create undo tablespace u2 default storage(initial 1m next 1m)
                                            *
                  ERROR at line 1:
                  ORA-30024: Invalid specification for CREATE UNDO TABLESPACE
                  • 6. Re: UNDO_TABLESPACE
                    Fran
                    the error is clear, this clause is not valid for undo tablespace

                    http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm#i1006309
                    • 7. Re: UNDO_TABLESPACE
                      846231
                      So why do I still get ORA-01555 snapshot too old even if my undo_management is AUTO and by UNDOTBS1 is only half used? :(
                      • 8. Re: UNDO_TABLESPACE
                        JohnWatson
                        KinsaKaUy? wrote:
                        So why do I still get ORA-01555 snapshot too old even if my undo_management is AUTO and by UNDOTBS1 is only half used? :(
                        I gave the exact answer to your original question (though I notice you have not said "thank you"). This, however, is a completely different question. Do you wish to discuss it here, or in a new topic?
                        • 9. Re: UNDO_TABLESPACE
                          Fran
                          told you:
                          You only have to set UNDO_TABLESPACE to a enough size and/or set UNDO_RETENTION to a enough value to this jobs
                          more space or less undo_retention
                          • 10. Re: UNDO_TABLESPACE
                            846231
                            John and Fran Thanks....but I can not understand what you are explaining.


                            My issue is I got ORA-01555 snapshot too old. One way of resolving this is to increase undo_segment chunks to a higher value, but the problem is it not possible as John had demo.

                            Do I need to drop the UNDOTBS1 and recreate it with bigger initial and next? Or do I need to choose and old rbs01, rbs02, rbs_big, manually managed rollbacks so that I could create bigger segment for LONG RUNNING BATCH JOBS WITH BIG DATA TO PROCESS?


                            Thanks
                            • 11. Re: UNDO_TABLESPACE
                              Fran
                              ......................i will do it for you:

                              option 1:

                              ALTER SYSTEM SET UNDO_RETENTION = XXXX;

                              /* correct value can be calculated with:
                              SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
                              SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
                              ROUND ((d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
                              ) "OPTIMAL UNDO RETENTION [Sec]"
                              FROM (SELECT SUM (a.BYTES) undo_size
                              FROM v$datafile a, v$tablespace b, dba_tablespaces c
                              WHERE c.CONTENTS = 'UNDO'
                              AND c.status = 'ONLINE'
                              AND b.NAME = c.tablespace_name
                              AND a.ts# = b.ts#) d,
                              v$parameter e,
                              v$parameter f,
                              (SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
                              ) undo_block_per_sec
                              FROM v$undostat) g
                              WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';
                              */

                              option 2:

                              ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

                              option 3:

                              CREATE TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

                              ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

                              DROP TABLESPACE undotbs_01 INCLUDING CONTENTS AND DATAFILES;
                              • 12. Re: UNDO_TABLESPACE
                                846231
                                Thanks Fran,

                                But I will go for #1 only since the others are not applicable as they are for physical datafiles and not for the logical undo segments.

                                Haissst....why did oracle remove the option to create larger size of rollback segments chunks. :(
                                • 13. Re: UNDO_TABLESPACE
                                  Fran
                                  option 2 is possible:
                                  SQL> select tablespace_name, file_name from dba_data_files where tablespace_name
                                   like '%UNDO%';
                                  
                                  TABLESPACE_NAME
                                  ------------------------------
                                  FILE_NAME
                                  --------------------------------------------------------------------------------
                                  UNDOTBS1
                                  C:\TEST\ORADATA\FRAN\UNDOTBS01.DBF
                                  
                                  SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'C:\TEST\ORADATA\FRAN\UNDOTBS02.DBF' size 2M REUSE AUTOEXTEND ON;
                                  
                                  Tablespace modificado.
                                  
                                  SQL>  select tablespace_name, file_name from dba_data_files where tablespace_nam
                                  e
                                    2   like '%UNDO%';
                                  
                                  TABLESPACE_NAME
                                  ------------------------------
                                  FILE_NAME
                                  --------------------------------------------------------------------------------
                                  
                                  UNDOTBS1
                                  C:\TEST\ORADATA\FRAN\UNDOTBS01.DBF
                                  
                                  UNDOTBS1
                                  C:\TEST\ORADATA\FRAN\UNDOTBS02.DBF
                                  also option 3 is also possible:
                                  SQL> CREATE UNDO TABLESPACE undotbs_02 DATAFILE 'C:\TEST\ORADATA\FRAN\UNDOTBS2_01.DBF' size 2M REUSE AUTOEXTEND ON;
                                  
                                  Tablespace creado.
                                  
                                  SQL> ALTER SYSTEM SET UNDO_TABLESPACE= undotbs_02 scope=BOTH;
                                  
                                  SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
                                  
                                  Tablespace borrado.
                                  
                                  SQL> select tablespace_name, file_name from dba_data_files where tablespace_name
                                  
                                    2   like '%UNDO%';
                                  
                                  TABLESPACE_NAME
                                  ------------------------------
                                  FILE_NAME
                                  --------------------------------------------------------------------------------
                                  
                                  UNDOTBS_02
                                  C:\TEST\ORADATA\FRAN\UNDOTBS2_01.DBF
                                  • 14. Re: UNDO_TABLESPACE
                                    jgarry
                                    >
                                    Haissst....why did oracle remove the option to create larger size of rollback segments chunks. :(
                                    Only Oracle knows, but it could be because it will eventually all be allocated anyways, and it really is doing everything in 64K chunks under the covers. New allocation requests could be a lot more variable in user tablespaces, only DBA's can decide if their data usage patters are going to be helped by preallocating large segments.

                                    Review the causes of ORA-1555. Too often, they are self-inflicted by programmers.

                                    Optimal was worse.
                                    1 2 Previous Next