-
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.
-
Mary,
Here you go with the metalink doc that gives detailed explaination. If you still have Q`s , post 'em in forum.
http://metalink.oracle.com/metalink/...T&p_id=40689.1
[Edited by sreddy on 01-04-2001 at 09:29 AM]
-
get back to Oracle help ( that comes with Oracle Server CD ) it is great in descibing this matters
-
ORACLE DBA HANDBOOK PAGE 221.
-
-
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.
-
$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 "
-- Input arguments:
-- rb_name
-- Name of rollback segment to use.
procedure commit_comment(cmnt varchar2);
-- Equivalent to SQL "COMMIT COMMENT "
-- Input arguments:
-- cmnt
-- Comment to assoicate with this comment.
procedure commit_force(xid varchar2, scn varchar2 default null);
-- Equivalent to SQL "COMMIT FORCE , "
-- 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 ". 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 ". 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 "
-- 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.
--
-
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,