Oracle: SYSDATE

Using SYSDATE in an SQL statement

If used in an SQL statement, the returned value won't change until the statement is finished, even if the execution of the statement takes longer than one second.

In order to demonstrate this, I create a function that returns the value of sysdate:

create or replace function get_sysdate return date
is begin
   return sysdate;
end get_sysdate;
/

I then select both, sysdate and the value returned by get_sysdate for at least three seconds:

select
   sysdate,
   get_sysdate,
   level
from
   dual connect by sysdate + 3/86400 >= get_sysdate;

The result of the query shows that sysdate does not change while get_sysdate returns different values for the period being executed.

See also: forcing an SQL statement to execute for a given amount of time.

Overriding the value that SYSDATE returns

The init parameter fixed_date allows to override the value that sysdate returns.

Unfortunatly, this is only possible system wide, not for a particular session.

SYSDATE@!

The at-sign exclamation suffix applied to sysdate indicates to evaluate sysdate on the executing database, not on the database a database link points to.

select
   sysdate@!,  -- sysdate of «own»  db
   sysdate     -- sysdate of remote db
from
   tab_remote@linked_db;

The @! token can also be found in the filter_predicates column of the plan_table.