snapshot too old; rollback segment too small

[mary]

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.

[sreddy]

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]

[hnagia]

get back to Oracle help ( that comes with Oracle Server CD ) it is great in descibing this matters

[irehman]

ORACLE DBA HANDBOOK PAGE 221.

[marist89]

or

[url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display_plain?p_dispid=275215756923[/url]

[mary]

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.

[sreddy]

$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.
--

[jmodic]

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,