Home » SQL & PL/SQL » SQL & PL/SQL » set transaction use rollback (oracle 9i)
set transaction use rollback [message #594627] Sat, 31 August 2013 07:00 Go to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Having PL/SQL procedure and calling sys dba procedure to alter rollback segment to online and after that to set the transaction to this rollback.
The rollback altered to online but transaction still running on available rollback and not the one I was meant to.
The user have executing grant on the sys dba procedure.
Re: set transaction use rollback [message #594628 is a reply to message #594627] Sat, 31 August 2013 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forget this stuff ("use rollback"), use undo tablespace and undo segments, they are made to not care about rollback segments.

For your questions, Use SQL*Plus and copy and paste your session, the WHOLE session showing what you say.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: set transaction use rollback [message #594883 is a reply to message #594627] Tue, 03 September 2013 14:05 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 449
Registered: July 2003
Location: WPB, FL
Senior Member
If you are still using "ROLLBACK" segments, as far as I can remember (in 9i), before you can issue the DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT() you need to issue a COMMIT (immediately before).

But better, follow Michel's advice to use "undo" tablespaces.

[Updated on: Tue, 03 September 2013 14:34] by Moderator

Report message to a moderator

Re: set transaction use rollback [message #594886 is a reply to message #594883] Tue, 03 September 2013 14:19 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Thanks to both answer, first , for my big sorrow for some specific projects we are using 9i compatible 8 so can't use undo.
The other answer is correct and after every commit (every 3000 insert commands)I use set transaction command, however, even having big tablespace (5GB) for the rollback segment and commit after every 3000 insert I get ORA-0155 snapshot too old error and this is strange.
Re: set transaction use rollback [message #594887 is a reply to message #594886] Tue, 03 September 2013 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
COMMIT inside LOOP INCREASES the likelihood ORA-01555 will occur!

NEVER issue COMMIT inside LOOP!!!!!!!!!!!!!!!!!!!!!
Re: set transaction use rollback [message #594888 is a reply to message #594886] Tue, 03 September 2013 14:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
doron.simchoni wrote on Wed, 04 September 2013 00:49
however, even having big tablespace (5GB) for the rollback segment and commit after every 3000 insert I get ORA-0155 snapshot too old error and this is strange.


It isn't strange. Actually, there might be more than one session trying to commit and overwrite the rollback information. If Oracle is unable to find the latest data block for the rollback information, it throws ORA-1555 error.

Here is a asktom thread on various cases of ORA-1555 error
Re: set transaction use rollback [message #594890 is a reply to message #594887] Tue, 03 September 2013 14:55 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Hi, again trying to answer both answers.
First, the procedure is using ref cursor where each time it opens for another partition and read something like 400,000 to 500,000 record and in a loop write them to a new table. without the commit I believe it will be worst.
To the other point this rollback segment is in offline status and opened as first command of the procedure and the transaction is set to use it so probably no other sessions use it.
Re: set transaction use rollback [message #594891 is a reply to message #594888] Tue, 03 September 2013 14:57 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Another point here is that the data files for that rollback segment are spread over three different disk as well.
Re: set transaction use rollback [message #594892 is a reply to message #594890] Tue, 03 September 2013 14:58 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>I believe it will be worst.
what is worse than ORA-01555?
Re: set transaction use rollback [message #594894 is a reply to message #594892] Tue, 03 September 2013 15:00 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
t is kind of expression, I don't believe it will be better to commit after 500,000 inserts in place od doing it within the loop.
Re: set transaction use rollback [message #594895 is a reply to message #594890] Tue, 03 September 2013 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
First, the procedure is using ref cursor where each time it opens for another partition and read something like 400,000 to 500,000 record and in a loop write them to a new table. without the commit I believe it will be worst.


What about INSERT SELECT in one shot?

Regards
Michel
Re: set transaction use rollback [message #594896 is a reply to message #594894] Tue, 03 September 2013 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>500,000 inserts
is tiny.

My puny laptop can do 7,000,000 inserts in under 30 seconds.
Re: set transaction use rollback [message #594897 is a reply to message #594895] Tue, 03 September 2013 15:05 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
Haven't try it before, mean in a loop for each partition to insert select in one shot looks terrified, have you try this kind og huge insert before?
Re: set transaction use rollback [message #594898 is a reply to message #594896] Tue, 03 September 2013 15:07 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
with one commit after the 7,000,000?
Re: set transaction use rollback [message #594899 is a reply to message #594898] Tue, 03 September 2013 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>with one commit after the 7,000,000?
yes, COMMIT itself consume next to zero resources.
Re: set transaction use rollback [message #594900 is a reply to message #594899] Tue, 03 September 2013 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/mv/msg/189287/594607/136107/#msg_594607
Re: set transaction use rollback [message #594901 is a reply to message #594899] Tue, 03 September 2013 15:13 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
The issue is not the commit, it is 7,000,000 records saved in rollback and cause the 01555
Re: set transaction use rollback [message #594902 is a reply to message #594901] Tue, 03 September 2013 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
You obviously do NOT understand why ORA-01555 occurs

>it is 7,000,000 records saved in rollback and cause the 01555
records in ROLLBACK segment do NOT cause ORA-01555

ORA-01555 error most often occurs during SELECT because Read Consistent View of the data is no longer available.
Re: set transaction use rollback [message #594904 is a reply to message #594902] Tue, 03 September 2013 15:27 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
guy's lets not stuck with things like that, I do know and use rollback for many years, did not mean physically store data, it is the address of the data in memory in order to roll it back. the point is consistent read of 7,000,000 records using the rollback when trying to roll back is very heavy.
the issue is why having the 1555 and not the way oracle is structured.
Re: set transaction use rollback [message #594905 is a reply to message #594902] Tue, 03 September 2013 15:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
The asktom thread has perfect explanation about the rollback segment error.

And all this discussion about COMMIT inside a loop is just to lessen the context switching since you are using pl/sql.

You earlier said that you commit after 3000 inserts with a 5GB rollback segment space, and still getting ORA-01555. These two are not directly related.
Re: set transaction use rollback [message #594907 is a reply to message #594905] Tue, 03 September 2013 15:33 Go to previous messageGo to next message
doron.simchoni
Messages: 17
Registered: December 2009
Junior Member
It relates since one of the answers was to drop the commit after 3,000 insert within the loop in the PL/SQL procedure and use one insert select shot for all 500,000 records and only then commit.
Re: set transaction use rollback [message #594908 is a reply to message #594907] Tue, 03 September 2013 15:44 Go to previous message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
doron.simchoni wrote on Wed, 04 September 2013 02:03
It relates since one of the answers was to drop the commit after 3,000 insert within the loop in the PL/SQL procedure and use one insert select shot for all 500,000 records and only then commit.


1. Commit within the loop is like welcoming more issues, rather call it side effects.
2. One shot insert select for 500,000 records should be just fine. Then commit at last. Did you try it and see?
Previous Topic: Need help to generate Procedure Execution LOG
Next Topic: Getting error when record type is used.
Goto Forum:
  


Current Time: Thu Dec 18 18:04:48 CST 2014

Total time taken to generate the page: 0.09157 seconds