Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1183
  • 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);

commit;


This statement is running for ever.

The table has 360 records.


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


0
vishali_vishu
Asked:
vishali_vishu
3 Solutions
 
sdstuberCommented:
you should be able to assign the record directly

UPDATE  schedule_ table
         SET   condition_large_sql = condition_sql;
0
 
vishali_vishuAuthor Commented:
it is running forever.
0
 
sdstuberCommented:
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
0
 
sridharv9Commented:
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.
0
 
riazpkCommented:
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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