quicky
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: quicky

  1. #1
    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?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Code:
    UPDATE MASTNAME
       SET LNAME = ''
    WHERE LNAME = 'NONAME'
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2002
    Posts
    59
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Maybe some people still get paid by the line of code they write?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    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

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  10. #10
    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
  •  



Click Here to Expand Forum to Full Width