snapshot too old; rollback segment too small
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: snapshot too old; rollback segment too small

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    Anyone here who can take time and explain exactly what happens behind the scene before the error 'shapshot too old; rollback segment too small' is generated?
    I've done some research but still confused..
    I need as detailed explanation as posible.
    Thank you.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Mary,

    Here you go with the metalink doc that gives detailed explaination. If you still have Q`s , post 'em in forum.

    [url]http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=40689.1[/url]

    [Edited by sreddy on 01-04-2001 at 09:29 AM]

  3. #3
    Join Date
    Jun 2000
    Posts
    179

    Talking

    get back to Oracle help ( that comes with Oracle Server CD ) it is great in descibing this matters
    Hisham Nagia
    IT Manager For Development
    Oracle Consultant - OCP

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    ORACLE DBA HANDBOOK PAGE 221.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    or

    [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display_plain?p_dispid=275215756923[/url]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Aug 2000
    Posts
    163
    I found among other corrections I can make is I can EXECUTE DBMS_TRANSACTION.READ_ONLY package.
    It is my understanding this package is created when database is created. Please correct me if I am wrong and let me know which script creates this package and how to find a source code of the package. I naively queried dba_source and of cource it is not there.
    Thank you everyone for your replies.

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    $O_H/rdbms/admin/dbmsutil.sql

    creates this package and first few lines of source of this package is

    create or replace package dbms_transaction is

    ------------
    -- OVERVIEW
    --
    -- This package provides access to SQL transaction statements from
    -- stored procedures.
    -- It also provids functions for monitoring transaction activities
    -- (transaction ids and ordering of steps of transactions )

    ----------------------------
    -- PROCEDURES AND FUNCTIONS
    --
    procedure read_only;
    -- Equivalent to SQL "SET TRANSACTION READ ONLY"
    procedure read_write;
    -- Equivalent to SQL "SET TRANSACTION READ WRITE"
    procedure advise_rollback;
    -- Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK"
    procedure advise_nothing;
    -- Equivalent to SQL "ALTER SESSION ADVISE NOTHING"
    procedure advise_commit;
    -- Equivalent to SQL "ALTER SESSION ADVISE COMMIT"
    procedure use_rollback_segment(rb_name varchar2);
    -- Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>"
    -- Input arguments:
    -- rb_name
    -- Name of rollback segment to use.
    procedure commit_comment(cmnt varchar2);
    -- Equivalent to SQL "COMMIT COMMENT <text>"
    -- Input arguments:
    -- cmnt
    -- Comment to assoicate with this comment.
    procedure commit_force(xid varchar2, scn varchar2 default null);
    -- Equivalent to SQL "COMMIT FORCE <text>, <number>"
    -- Input arguments:
    -- xid
    -- Local or global transaction id.
    -- scn
    -- System change number.
    procedure commit;
    pragma interface (C, commit); -- 1 (see psdicd.c)
    -- Equivalent to SQL "COMMIT". Here for completeness. This is
    -- already implemented as part of PL/SQL.
    procedure savepoint(savept varchar2);
    pragma interface (C, savepoint); -- 2 (see psdicd.c)
    -- Equivalent to SQL "SAVEPOINT <savepoint_name>". Here for
    -- completeness. This is already implemented as part of PL/SQL.
    -- Input arguments:
    -- savept
    -- Savepoint identifier.
    procedure rollback;
    pragma interface (C, rollback); -- 3 (see psdicd.c)
    -- Equivalent to SQL "ROLLBACK". Here for completeness. This is
    -- already implemented as part of PL/SQL.
    procedure rollback_savepoint(savept varchar2);
    pragma interface (C, rollback_savepoint); -- 4 (see psdicd.c)
    -- Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>". Here for
    -- completeness. This is already implemented as part of PL/SQL.
    -- Input arguments:
    -- savept
    -- Savepoint identifier.
    procedure rollback_force(xid varchar2);
    -- Equivalent to SQL "ROLLBACK FORCE <text>"
    -- Input arguments:
    -- xid
    -- Local or global transaction id.
    procedure begin_discrete_transaction;
    pragma interface (C, begin_discrete_transaction); -- 5 (see psdicd.c)
    -- Set "discrete transaction mode" for this transaction.
    -- Exceptions:
    -- ORA-08175 will be generated if a transaction attempts an operation
    -- which cannot be performed as a discrete transaction. If this
    -- exception is encountered, rollback and retry the transaction.

    -- ORA-08176 will be generated if a transaction encounters data changed
    -- by an operation that does not generate rollback data : create index,
    -- direct load or discrete transaction. If this exception is
    -- encountered, retry the operation that received the exception.
    --

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And of course, as with all internal packages only package declaration is published and readable, while the actual code, that is a package body is wrapped and unreadable.

    If you have to manualy install or reinstall any of this packages you must run two files:
    - one for package declaration with the name somethink like dbms*.sql
    - one for package body with the name somethink like prvt*.plb

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width