This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 6, 2013 5:00 AM by 846231 RSS

UNDO_TABLESPACE

846231 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    ......................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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points