Update Oracle timestamp to current date

Question

I have a TIMESTAMP (6) field in Oracle db. The value of this field is in the format

DD/MM/YYYY HH:MM:SS.000000000 PM 

How to update this value to the current timestamp?

[link to a similar question:] oracle update date value

I followed this link, but the following query takes a very long time.

 update table_name set start_time = to_char(to_date(start_time, 'yyyy/mm/dd-hh:mi:ss:ff3'), '2012/10/10-19:30:00:00') where column='Q' 

Answer 1

A timestamp is a point in time, it has no format. To update this field to the current timestamp, use SYSTIMESTAMP or CURRENT_TIMESTAMP (server date / time and session date / time, respectively):

 UPDATE your_table SET your_column = systimestamp WHERE ... 

If the query takes an abnormal amount of time (much longer than a comparable SELECT with the same WHERE clause), the most likely causes are:

• The rows you are updating are blocked by another session (doing SELECT FOR UPDATE NOWAIT on these lines ensures that you have a lock).
• You have triggers that do extra work,
• You are updating the column referenced by an unindexed foreign key.

Answer 2

Why aren't you just

 update table_name set start_date = systimestamp where column='Q' 

If you suspect that the table has locks, there are several tables for checking: dba_locks , v$session , v$session_blockers , etc. They are useful when the user is blocking something with a random update without commit or rollback , but you should be able to see if there is a possibility of blocking locks from the architecture of your application. You just have to imitate all the scenarios on paper.