Locking System Date

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 2
Joined: Thu Sep 26, 2013 8:42 am

Locking System Date

Postby rajakannan » Thu Sep 26, 2013 9:14 am

Dear All,

I am locking system date by below code available in lock_date.sql:

Conn scott/tiger@abc
for i in 1 .. 1000000000
EXECUTE IMMEDIATE 'ALTER SYSTEM SET Fixed_date = ''01-Jun-2016''';
End loop;

But somebody kill my session at this time as date is locked by me. I would not know this unless I am seeing the session.

If anybody kills, I want to login again and should do the date lock automatically.

Can anyone advise, how to login once again when my session killed, in the PL Sql way.


Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Locking System Date

Postby Tim... » Thu Sep 26, 2013 6:52 pm


I really don't understand what you are trying to achieve here.

You only need to run this command once and the SYSDATE value will remain the same until you set the parameter back to NONE. See:

http://docs.oracle.com/cd/E16655_01/ser ... REFRN10062

It looks like you are just running the command loads of times, which will not affect what it does, but will burn database resources. Killing your session will have no affect on the return value of SYSDATE, because as soon as the parameter is set once, it will return the same value for ever.

I think you need to understand what initialisation parameters do before you start messing with them. :)


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests