I just joined this website. I have run into "snapshot too old" issues in 8i in the past, and I don't have any control over the database on which this problem occurs. This is because I write migrations for a software company, and our clients need to run through the migrations on their own databases that they control. No one else is using the system while the migration is occurring - the system is down, but I'm causing the problem since my code is selecting from the same table that it needs to update, and the tables can vary in size, from client to client. So, I need to have methods in place within the migration code that avoids this problem, while not taking up too much time being overly 'safe'. In the past, when I ran into this problem, I created a regular Oracle table that contained all the rowids of the rows I needed to update. I then dropped that table at the end of the process. This completely avoids the problem, but is not very efficient. I'm curious if I can use the same methodology, but using an Oracle temporary table (which is only visible for that session) of the rowids instead. Since this error is random, I won't know if the temporary table is a good solution or not (until clients start calling). Does anyone have much experience with creating a temporary table of the rowids of the table you need to update, and then looping through until all rows are updated? Any advice would be greatly appreciated! I want to note that although I ran into this issue with 8i with our clients, our software now runs on 9i (and so all our clients have subsequently upgraded), so I'm not sure if there have been improvements regarding this problem.
Carrie
Carrie
