How to change SYSDATE to future - FIXED_DATE

How to change SYSDATE to future - FIXED_DATE

In real world application is dependent on the SYSDATE for measuring current date-time for the processing.

Whenever there might be a situation where application want to process a complete data only on the month end and for testing or UAT purpose you need to do the processing at adhoc basis.
In this FIXED_DATE parameter help to set the SYSDATE value to the desired any future date you want to set.

Changing current date to future is an option but there may be some other databases also exist on same server. Changing date on exadata server then its very complex job.

Please find below inputs on the change date to future date–

1. Changing the time on Exadata server is difficult way to achieve the goal as It involve complexity, downtime and If we change the sysdate on server again ntp will try to correct it. We can’t stop ntp as its having dependencies on cell node.

If you want to check it further with this option then need to check with Product Support Team through SR.

2. We can change the SYSDATE on database for testing purpose by setting FIXED_DATE to <future date>
1. FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date.
2. This parameter only change the SYSDATE not the other like LOCALTIMESTAMP, CURRENT_TIMESTAMP etc. Please refer below MOS doc for the further reference and use of this parameter.

Init.ora Parameter "FIXED_DATE" Reference Note (Doc ID 30724.1)  

Clarification CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP, FIXED_DATE (Doc ID 1624595.1)

Test Case Done –

==============

SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;

SESSIONTIMEZONE                                                                                                             CURRENT_D CURRENT_TIMESTAMP

--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------

LOCALTIMESTAMP                                                                                                                              SYSDATE   SYSTIMESTAMP

--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------

+00:00                                                                                                                                       19-JAN-18 19-JAN-18 08.16.14.860064 AM +00:00

19-JAN-18 08.16.14.860064 AM                                                                                    19-JAN-18 19-JAN-18 08.16.14.860061 AM +00:00

SQL>

SQL> Alter System Set Fixed_Date='31-Jan-2018' scope=both ;

System altered.

SQL>

SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;

SESSIONTIMEZONE                                                                                                             CURRENT_D CURRENT_TIMESTAMP

--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------

LOCALTIMESTAMP                                                                                                                              SYSDATE   SYSTIMESTAMP

--------------------------------------------------------------------------- --------- ---------------------------------------------------------------------------

+00:00                                                                                                                                       19-JAN-18 19-JAN-18 08.16.51.732553 AM +00:00

19-JAN-18 08.16.51.732553 AM                                                                                    31-JAN-18 19-JAN-18 08.16.51.732543 AM +00:00

SQL> !date

Fri Jan 19 08:24:02 GMT 2018

SQL> ALTER SYSTEM SET FIXED_DATE=NONE;

System altered.

SQL> select SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP from dual;

SESSIONTIMEZONE                                                                                                             CURRENT_DATE           CURRENT_TIMESTAMP

--------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------

LOCALTIMESTAMP                                                                                                                              SYSDATE                          SYSTIMESTAMP

--------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------

+00:00                                                                                                                                       19-JAN-2018 08:26:53 19-JAN-18 08.26.53.209598 AM +00:00

19*JAN*2018 08:26:53                                                                                                       19-JAN-2018 08:26:53 19-JAN-18 08.26.53.209595 AM +00:00

Thanks. I hope this helps.

script for historical blocking and locking session in the database

Below are the useful script for DBA to get historical blocking and locking session in the database

SELECT  distinct a.sql_id ,a.inst_id, a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module

FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s

where a.sql_id=s.sql_id

and blocking_session is not null

and a.user_id <> 0

and a.sample_time > sysdate-11;

SELECT  distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,

a.user_id,s.sql_text,a.module

FROM  V$ACTIVE_SESSION_HISTORY a, v$sql s

where a.sql_id=s.sql_id

and blocking_session is not null

and a.user_id <> 0

and a.sample_time between to_date('08/01/2018 17:00', 'dd/mm/yyyy hh24:mi')

and to_date('08/01/2018 17:30', 'dd/mm/yyyy hh24:mi');

I hope this helps. 

Thank you!!