Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: How to prevent oracle from committing a transaction?
Sinardy,
I am afraid that you are under a number of misconceptions, and confusing writing to
disk and commiting. The former is something physical, and the later more logical,
althought it is also accompanied by a physical write.
Whenever a disk flush occurs, the 'state' of your transaction is remembered
(committed/uncommitted). As long as it's not committed, you can always rollback (or
Oracle will do it for you if the machine crashes before you commit).
Concerning the shutdown, in a shutdown normal (hardly anybody uses it) Oracle waits
for all users to have logged off. Since you must (implicitely or explicitely) take a
decision about accepting or rejecting your changes when logging off from Oracle, it
means that everybody has to rollback or commit (usually, the default behaviour is to
rollback when disconnecting. The exception is the SQL*Plus EXIT which commits and logs
off).
With the more widely used shutdown immediate, users are disconnected from Oracle (as
they would be by say an operating system shutdown) and this forced disconnection comes
with a rollback of pending transactions, then a flush of memory to disk.
HTH,
Stephane Faroult
Oriole Corporation
>Hi Ranganath,
>
> There are several situation for oracle to commit.
>
>one of them of course you type 'commit;' manually
>others can be :
>when checkpoint occur
>when switch redo log occur
>when LRU function need to find space in your SGA
>when shutdown normal (am I wrong ?, Oracle rollback or commit ? I think
>oracle rollback uncommited transactions)
>ect...
>
>
>Sinardy
>
>
>-----Original Message-----
>Sent: Tuesday, 5 June 2001 3:30 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Dear DBA Gurus,
>
> Can you tell me how to prevent oracle from committing a transaction unless
>I explicitly commit it. I have observed that when I insert a record into a
>table and type exit from the sql prompt without commiting the transaction
>and again open a new sqlplus session and select from that particular table
>the record is inserted. How do I prevent oracle from inserting into a table
>unless I explicitly specify commit? Please note that I have set my sqlplus
>session as autocommit off. I tried with alter table <tablename> nologging
>but it didn't do what I wanted. Can anybody help me in this regard?
>
>TIA and Regards,
>
>Ranganath
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ranganath K
> INET: ranganathk_at_subexgroup.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: Sinardy Xing
> INET: sinardyxing_at_bcsis.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).
>
-- Diese E-Mail wurde mit http://de.mail-inspector.de verschickt Mail Inspector ist ein kostenloser Service von http://www.is-fun.net Der Absender dieser E-Mail hatte die IP: 195.25.229.12 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: sfaroult_at_oriole.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).Received on Tue Jun 05 2001 - 03:46:36 CDT