• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1198
  • Last Modified:

update clob column with varchar column

I added a new column to the existing table (clob column) and i wanted to move the existing data from varchar column to the newly added  clob column. ( The size for the existing varchar is not enough and I don't want to remove that column so i didn't do alter table to change the data type)

UPDATE  schedule_ table
         SET   condition_large_sql = to_clob(condition_sql);


This statement is running for ever.

The table has 360 records.

UPDATE STATEMENT  ALL_ROWSCost: 3  Bytes: 292  Cardinality: 4              
            1 TABLE ACCESS FULL TABLE NEMA.CR_REPORT_SCHEDULE Cost: 3  Bytes: 292  Cardinality: 4  

3 Solutions
you should be able to assign the record directly

UPDATE  schedule_ table
         SET   condition_large_sql = condition_sql;
vishali_vishuAuthor Commented:
it is running forever.
are multiple people updating that table?

your update doesn't have a where clause,  if just one other person is updating just one row you will have to wait until that transaction completes.

if you have multiple people and multiple rows it just gets that much worse
We do run similar sql moving varchar to clob using bind variables.

UPDATE streaminfo SET strm_tag1 = '' , strm_lob_tag2 = :1  WHERE stream_ID=:2

Check if the "enq-HW: contention"  wait event is high. And if yes try  manually add extend to lob segment.

However, still you may see slowdown of overall system performance. Oracle so far couldn't give us any appropriate answer.  

 Good luck.
Try to lock the table before issuing update:
LOCK table schedule_ table in exclusive mode;
and you will know whether any record has been locked by someone else or not.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now