4 Replies Latest reply: Mar 28, 2013 2:35 PM by 999390 RSS

    Q: Impact of use_current_session parameter of dbms_scheduler.run_job

    999390
      On the 11g db that I have access to, I'm seeing different systimestamp value while running plsql block using
      dbms_scheduler.run_job with use_current_session = true and false.

      My job looks like following.
      dbms_scheduler.create_job(
      job_name => 'tk2timestamp_test',
      job_type => 'PLSQL_BLOCK',
      job_action => 'begin insert into tktest values (systimestamp, to_char(sysdate, ''DD-MON-YYYY HH24:MI:SS''), current_timestamp, localtimestamp, dbtimezone, sessiontimezone); end;',
      enabled => true,
      auto_drop => false);


      I see that UTC time was returned as systimestamp if it is called with true and PST if it is called with false.
      In the same session, I get following result with select statement.


      select systimestamp, current_timestamp, localtimestamp, dbtimezone, sessiontimezone from dual;

      SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIM
      27-MAR-13 04.05.59.914647 PM +00:00 27-MAR-13 09.05.59.914650 AM -07:00 27-MAR-13 09.05.59.914650 AM +00:00 -07:00

      Can any one let me know why this is happening? Why systimestamp value is not PST if use_current_session = true?
        • 1. Re: Q: Impact of use_current_session parameter of dbms_scheduler.run_job
          damorgan
          What is the timezone of the server?

          What is the timezone of the client?
          • 2. Re: Q: Impact of use_current_session parameter of dbms_scheduler.run_job
            999390
            Thank you for the response. Appreciate it.

            DB timezone is UTC and session timezone is PDT.


            While I'm not clearing understanding (I somehow feel above is not the information you are looking for) the question, let me add following information.
            TZ variable when db was started is most likely UTC (Sorry, I cannot be sure as this is done automatically but I was told so).
            I'm not sure the TZ variable where the listener was started.
            TZ variable where I started the sql session I performed above is PST8PDT.

            Please let me know if you need any additional information or clarification.
            thx
            toru
            • 3. Re: Q: Impact of use_current_session parameter of dbms_scheduler.run_job
              damorgan
              Some activities get the timezone information from the server and some from the client.

              Please post the output from running these SQL statements:
              SELECT SYSTIMESTAMP FROM dual;
              
              SELECT SYSTIMESTAMP AT TIME ZONE 'PDB' FROM dual;
              
              SELECT SESSIONTIMEZONE,, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;
              • 4. Re: Q: Impact of use_current_session parameter of dbms_scheduler.run_job
                999390
                Thank you again for your response.

                I've got following result.

                SQL> SELECT SYSTIMESTAMP FROM dual;

                SYSTIMESTAMP
                ----------------------------------------
                28-MAR-13 07.13.08.556079 PM +00:00

                SQL> SELECT SYSTIMESTAMP AT TIME ZONE 'PDB' FROM dual;
                SELECT SYSTIMESTAMP AT TIME ZONE 'PDB' FROM dual
                *
                ERROR at line 1:
                ORA-01882: timezone region not found


                SQL> SELECT SESSIONTIMEZONE,CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

                SESSIONTIM CURRENT_TIMESTAMP LOCALTIMESTAMP
                ---------- ---------------------------------------- ----------------------------------------
                -07:00 28-MAR-13 12.13.41.196653 PM -07:00 28-MAR-13 12.13.41.196653 PM



                BTW, I have two instances returns the result like this if this information helps.

                I've created following table in order to help proving something is changing whether I pass true or false to use_current_session parameter when calling dbms_scheduler.run_job.
                --
                create table tktest (tksystimestamp timestamp(6), tktocharsysdate varchar2(100), tkcurrent_timestamp timestamp(6), tklocaltimestamp timestamp(6), tldbtimezone varchar2(100), tksessiontimezone varchar2(100));
                --

                Then I created job as following. This job inserts systimestamp,sysdate,current_timestamp,localtimestamp,dbtimezone, and sessiontimezone to above table.
                --
                begin
                dbms_scheduler.create_job(
                job_name => 'tk2timestamp_test',
                job_type => 'PLSQL_BLOCK',
                job_action => 'begin insert into tktest values (systimestamp, to_char(sysdate, ''DD-MON-YYYY HH24:MI:SS''), current_timestamp, localtimestamp, dbtimezone, sessiontimezone); end;',
                enabled => true,
                auto_drop => false);
                exception
                when others then
                null;
                end;
                --
                Run the job using run_job procedure.
                --
                exec dbms_scheduler.run_job(job_name => 'tk2timestamp_test',use_current_session => true);
                --
                Above inserts following record in tktest.
                ----
                SYSTIMESTAMP SYSDATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTZ SESSIONTZ
                - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
                28-MAR-13 07.04.58.227616 PM 28-MAR-2013 19:04:58 28-MAR-13 07.04.58.227617 PM 28-MAR-13 07.04.58.227617 PM +00:00 +00:00

                --
                Run the job using run_job procedure again but use 'use_current_session => false' this time.
                --
                Above inserts following record in tktest.
                ----
                SYSTIMESTAMP SYSDATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTZ SESSIONTZ
                - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
                28-MAR-13 12.05.24.150817 PM 28-MAR-2013 12:05:24 28-MAR-13 12.05.24.150819 PM 28-MAR-13 12.05.24.150819 PM +00:00 -07:00


                I also run following from the same session.

                SQL> select systimestamp, current_timestamp, localtimestamp, dbtimezone, sessiontimezone from dual;

                SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTZ SESSIONTZ
                - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
                28-MAR-13 07.05.40.640499 PM +00:00 28-MAR-13 12.05.40.640503 PM -07:00 28-MAR-13 12.05.40.640503 PM +00:00 -07:00

                So why executing dbms_scheduler.run_job with 'use_current_session => false' causes SEESSIONTIMEZONE to be PST8PDT?

                I have an another instance pointing all the time based on UTC and my question is why only above instance is showing result like above and what causes it.

                thx.