When you haven’t access to SO and just have to alter database date…
# Fix Date:
ALTER SYSTEM SET fixed_date = '2016-04-05-12:00:00';
# Unfix Date:
ALTER SYSTEM SET fixed_date = NONE;
OBS: Just to make it clear: The date will be really “fixed”. The time will “stop”. Seconds, minutes will not advance…
Do you know the difference?
current_date(): Only give you the date.
now(): Datetime when the statement,procedure etc… started.
sysdate(): Current datetime.
Take a look between the functions now() and sysdate() after executing sleep of 5 seconds…:
SQL> select current_date(),now(),sysdate(),SLEEP(5),now(),sysdate();
"2016-03-24";"2016-03-24 16:00:43";"2016-03-24 16:00:43";"2016-03-24 16:00:43";"2016-03-24 16:00:48"
Don’t know what is this? Oh boy, I suggest you take a look…
It can sound a little crazy, but it’s about an universal time adjustment of atomic time. Something like that. To understand, take a look on:
But what about Oracle Database adjustment? Good news: Nothing to do! 😀
In Oracle words: “The Oracle RDBMS needs no patches and has no problem with the leap second changes on OS level.”
If your application uses timestamp or sysdate, verify the adjust of the OS Level. If it consists on a “60” second, it can result on “ORA-01852 seen 60 seconds is a illegal value for the date or timestamp dataype.”
(Insert leap seconds into a timestamp column fails with ORA-01852 (Doc ID 1553906.1))
Another possibilities is documented on these notes:
NTP leap second event causing Oracle Clusterware node reboot (Doc ID 759143.1)
(Oracle VM and RHEL 4.4 to 6.2): Leap Second Hang – CPU Can Be Seen at 100% (Doc ID 1472421.1)
(OEM on Linux): Enterprise Manager Management Agent or OMS CPU Use Is Excessive near Leap Second Additions on Linux (Doc ID 1472651.1)
So, pay attention! 🙂
Here other Oracle notes that I recommend to take a look:
Leap seconds (extra second in a year) and impact on the Oracle database. (Doc ID 730795.1)
Leap Second Time Adjustment (e.g. on June 30, 2015 at 23:59:59 UTC) and Its Impact on Exadata Database Machine (Doc ID 1986986.1)
How Leap Second Affects The OS Clock on Linux and Oracle VM (Doc ID 1453523.1)
NOTE:1461363.1 – What Leap Second Affects Occur In Tuxedo?
NOTE:1553906.1 – Insert leap seconds into a timestamp column fails with ORA-01852
NOTE:412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
NOTE:1453523.1 – How Leap Second Affects The OS Clock on Linux and Oracle VM
NOTE:1019692.1 – Leap Second Handling in Solaris – NTPv3 and NTPv4
NOTE:1444354.1 – Strftime(3c) Does Not Show The Leap Second As 23:59:60
NOTE:1461606.1 – Any Effect of Leap Seconds to MessageQ?