I have a TIMESTAMP(6) field in Oracle db. Value of this field is in format
DD/MM/YYYY HH:MM:SS.000000000 PM
How to update this value to the current timestamp?
[a link to a similar question:] update date value in oracle
I followed this link, but following query is taking very long time to execute.
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'
A timestamp is a point in time, it has no format. To update such a field to the current timestamp, use SYSTIMESTAMP
or CURRENT_TIMESTAMP
(respectively the date/time of the server and the date/time of the session):
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 mostly likely causes are:
Why you don't just
update table_name
set start_date = systimestamp
where column='Q'
If you suspect there are locks on the table, there are some tables to check: dba_locks
, v$session
, v$session_blockers
etc. These are useful when a user blocked something with an accidental update
without a commit
or rollback
, but you should be able to see if can exists blocking locks from the architecture of your application. You should just simulate on paper all the scenarios.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With