quicky

It's also possible to reduce number of updated rows and cut the operation into smaller parts.
I'd use a range of values of primary key for that.

That would be a good idea if the Primary Key wasn't a combination of letters and numbers randomly generated in the application when each record is added to the database. The number is compared to make sure it is unique and if so, the record is posted to the database. I can't really specifiy a specific range. Any other suggestions?

That doesn't matter.

Code:

---

UPDATE MASTNAME
  SET LNAME = ''
WHERE LNAME = 'NONAME'
  AND PK LIKE '0%';
COMMIT;
UPDATE MASTNAME
  SET LNAME = ''
WHERE LNAME = 'NONAME'
  AND PK LIKE '1%';
COMMIT;
...

---

Of course you can find exact margins and use condition
... BETWEEN '0000000000' AND '1111111111'
and so on.

Would that help you?

You are suggesting that I create a copy of this script for each of the possible 10 numbers and 26 letters that my primary keys might start with. If any one of these combinations results in a record set over approximately 220,000 I won’t be able to commit because my roll back segments will be too large and I will run out of hard drive space. I could do a record count first on each possible combination but that seems like a lot of work for something so trivial. Is there a way for me to specify this update to run in sets of 25,000 records? I feel almost claustrophobic working on this machine.

I’ve already freed as much space as I can on the hard drive. I deleted all non-critical programs and files. I am under the impression that if I run out of hard drive space my database will panic and go into recovery mode, not to mention my OS will lock up because it won’t have any room to breath. Is this true? Also, this machine is not physically accessible so I cannot add an additional hard drive.

Quote:

---

Originally posted by sirhaggus
I could do a record count first on each possible combination but that seems like a lot of work for something so trivial.

---

Lot of work, but not for you:

Code:

---

select substr(PK,1,2), count(*)
from T
group by substr(PK,1,2);

---

If this still returns too large numbers you can go further to 3 or 4 letters. Of course it leads to many statements to run. But it shouldn't be a problem to let Oracle to do that job and generate statements for you.

Are there any other columns with indexes in that table? Kind of record-type, date or something like that? You could use such column as well.

Try to find a set of rows which doesn't fail during update and watch rollback state. Then you could try larger sets.

Quote:

---

I am under the impression that if I run out of hard drive space my database will panic and go into recovery mode, not to mention my OS will lock up because it won’t have any room to breath.

---

As I'm not a DBA I can't tell it exactly but it happened once to me and Oracle just claimed a space without crashing.

Good luck.

I am still confused why using a cursor with a count variable and commit would be a problem. Could someone give some insight about this. I've searched through the forums with regards to the "snapshot to old" error and am not quite sure how this applies to my specific scenario. My oracle books give me some examples too. Is it just a performance issue?

Oracle will needs to present you with a consistent view of the data. You open your cursor at timepoint T1. Every N thousand records you commit (T2, T3, ... Tn). When you commit, the changes get applied to the table. In order to get a consistent view of the data, oracle must read entries from the RBS in order to determine what the cursor was supposed to look like at T1. After a while, your rbs will fill up with changes and you will no longer be able to get a consistent view of the data. At this point, the 1555 error will be triggered.

Can I turn off RBS for this transaction?

Absolutely not.

marist89, you seem to have a very well rounded grasp of the situation. What is your opinion of ales proposed solution to my problem?