-
I would like to create a script to update each row that has a "NONAME" value in the LNAME field. I would like to modify this value to be blank. The name of the table is MASTNAME and it is an Oracle 8.1.6 database. Any suggestions? Should I use a cursor? Could someone provide me an example?
-
Code:
UPDATE MASTNAME
SET LNAME = ''
WHERE LNAME = 'NONAME'
Jeff Hunter
-
Try the example shown by marist89 with
NOLOGGIN and parallel hints to get more performance, and
if the table is too big then u may want to use a big rollback segmet with optional commit and also could go for cursor program... ( again consider the rollback segment size..if not use freequent commits ).
Thanx
Sanjay
-
Originally posted by sanju_dba
Try the example shown by marist89 with
NOLOGGIN ....
Of course the NOLOGGING will simply be ignored by Oracle, so it wouldn't do any harm.....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I had to use a cursor on the same database for a similiar data change. I'd imagine that I will have to use one for this operation as well. Thanks fellas.
Chester Ney
CODY Computer Services
System DBA
-
Can you explain why would you want to go with far more complicated and far less efficient way (using PL/SQL with cursor loop) when such a trivial SQL UPDATE statement can do the job?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Maybe some people still get paid by the line of code they write?
Jeff Hunter
-
I would use a cursor loop with a commit because the database has several million records and it is running on rather antiquated hardware and I am concerned that I might run out of rollback space.
Chester Ney
CODY Computer Services
System DBA
-
Then don't be surprised if you get "ORA-1555 Snapshot to old" error - what you are intending to do is a perfect, out-of-the-manuals example of how you should not do this kind of mas updates on table. Serch for "fetch accross commit" and ORA-1555 in this forums or in other Oracle related sites to find an explanation why you should not do this updates inside a cursor loop with periodocal commits.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I don't know if I have enough hard drive space to do it all at once. It would be really close and I can't take that chance. I've done an export of the database so I am not entirely worried. But given the scanario that I couldn't do an export, is there another solution without incuring this error as a result of using cursors and periodic commits.
Chester Ney
CODY Computer Services
System DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|