RE: RE: Rollback seg for an entire session, not just per tran

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Tue, 03 Apr 2001 11:24:31 -0700
Message-ID: <F001.002E0061.20010403105735@fatcity.com>

I hate to disagree with people, but SYSTEM rollback segment is actually not used at all if there are other rollback segments available. Those "dictionary transactions" are a myth, the same as that oracle is trying to "cache extents". Simply not true.

-----Original Message-----
Sent: Tuesday, April 03, 2001 2:01 PM
To: Multiple recipients of list ORACLE-L

Raj,

OH, I hate to contradict people, but several months ago we had a couple of
very long running jobs that accidentally filled all of the rollback segment space with the exception of SYSTEM. The processes all crashed with an Oracle
error that said the SYSTEM rollback segment was strictly for changes in the data
dictionary. Therefore NO it does not get used for user session transactions outside of data dictionary stuff which we all know does an autocommit.

Dick Goulet

____________________Reply Separator____________________
Author: Raj Gopalan <raj.gopalan_at_netdecisions.co.uk>
Date:       4/3/2001 8:50 AM

Dana

System rollback segment will be used for user session transactions.
The only way I think of assinging a rollback segment is by using SET TRANSACTION COMMAND.
There wont be any implict commits in a PL/SQL procedure and on exit of the PL/SQL will also not commit the data. You need to explicitly specify the commit.

-----Original Message-----
Sent: 03 April 2001 15:55
To: Multiple recipients of list ORACLE-L Implicit commits in PL/SQL?

A colleague wants to do a large load using multiple PL/SQL programs; she typically (has the DBA) turns off all rollback segments except for SYSTEM and one large rollback segment.

Some questions:

Will the rollback segment called SYSTEM in the SYSTEM tablespace never be used for user session transactions?

Is there any way to specify which rollback segment to use for an entire session versus per transaction? I'm not sure if her PL/SQL code performs implicit commits so I'm not sure doing one commit + set transaction before executing the procedure(s) will have the desired effect. Will Oracle ever do an implicit commit inside a PL/SQL procedure or is a commit performed only with the commit command or when exiting a procedure?

Thanks very much.

Dana

Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: dana
INET: danamn2000_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Raj Gopalan
INET: raj.gopalan_at_netdecisions.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------