Job: TO_CHAR(CURRENT_TIMESTAMP, 'HH24MISSFF') error
問題:
$date -R
Fri, 10 May 2013 14:51:40 +0800
$date -u
Fri May 10 06:51:45 UTC 2013
CREATE TABLE GC.TEST_JOB_TIMESTAMP
( SYST VARCHAR2(50 BYTE),
CURRENT_T VARCHAR2(50 BYTE),
TIMES DATE)
CREATE OR REPLACE procedure GC.TEST_TIMESTAMP is
begin
INSERT INTO GC.TEST_JOB_TIMESTAMP
SELECT TO_CHAR (sysTIMESTAMP, 'YYYYMMDD')
||'T'||SUBSTR (TO_CHAR (sysTIMESTAMP, 'HH24MISSFF'), 1, 9) sysT,
TO_CHAR (CURRENT_TIMESTAMP, 'YYYYMMDD')
||'T'||SUBSTR(TO_CHAR(CURRENT_TIMESTAMP, 'HH24MISSFF'), 1, 9) CURRENT_T,SYSDATE
FROM DUAL;
END;
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'GC.TEST_TIMESTAMP;'
,next_date => to_date('10/05/2013 13:49:29','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+10/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
設定了job執行下面的過程後發現insert到GC.TEST_JOB_TIMESTAMP的資料為
SYST CURRENT_T TIMES
20130510T143935026 20130510T063935026 2013/5/10 下午 02:39:35
TO_CHAR(CURRENT_TIMESTAMP, 'HH24MISSFF'), 1, 9)比正常系統時間的慢了8個小時
實驗證明:
設定
ALTER SESSION SET NLS_LANGUAGE='AMERICAN' ;
ALTER SESSION SET NLS_TERRITORY='AMERICA' ;
ALTER SESSION SET NLS_CURRENCY='$' ;
ALTER SESSION SET NLS_ISO_CURRENCY='AMERICA' ;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' ;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR' ;
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN' ;
ALTER SESSION SET NLS_SORT='BINARY';
時間問題和DBA_JOBS中的NLS_ENV欄位並無關係
後來發現Oracle執行JOB時首先會
alter session set time_zone='UTC';
然後再取時間
SELECT sysTIMESTAMP,CURRENT_TIMESTAMP FROM DUAL
只能用 SYSTIMESTAMP 取DB server的時間和時區
UTC
是世界統計標準時區預設 的 0:0區
所以我們看到的時間都慢了8個小時
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-2146427/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMPMySql
- MySQL中的CURRENT_TIMESTAMPMySql
- error on auto execute of job "SYS"."PURGE_LOG"錯誤分析Error
- job呼叫儲存過程(包含current_timestamp函式)導致插入資料庫後,時間不準確儲存過程函式資料庫
- ORA-12012: error on auto execute of job 8913Error
- ORA-12012: error on auto execute of job 388947Error
- oracle to_char 函式Oracle函式
- oracle to_char函式Oracle函式
- ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"ErrorOracle
- ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_2"ErrorOracle
- Python程式碼解析: job = next(job for job in jobs if job.job_id == job_id)Python
- Job for mysqld.service failed because the control process exited with error code...MySqlAIError
- ORA-12012: error on auto execute of job "ORACLE_OCMErrorOracle
- ORA-39097: Data Pump job encountered unexpected error -12801Error
- ORACLE to_char函式詳解Oracle函式
- oracle to_char函式的使用Oracle函式
- SQL Server 2000 Error 14274 ,不能刪除job解決方法SQLServerError
- Oracle 11g報錯"ORA-12012: error on job ORACLE_OCM.MGMT_CONFIG_JOB_2_1"OracleError
- java.io.IOException: Error opening job jar: hadoop-0.20.2-examples.jarJavaExceptionErrorJARHadoop
- 教你如何使用MySQL中CURRENT_TIMESTAMP時間戳MySql時間戳
- Job for mysqld.service failed because the control process exited with error code錯誤解決MySqlAIError
- Centos httpd模組 Job for httpd.service failed because the control process exited with error code.CentOShttpdAIError
- ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_%"Error
- oracle建立job並執行jobOracle
- to_char函式不要使用hh格式函式
- 使用TO_Char()函式如何取自然周函式
- to_char函式格式轉換參考函式
- to_char取週數的演算法演算法
- 【JOB】Oracle JOB全面學習(DBMS_JOB和DBMS_SCHEDULER)Oracle
- 【ERROR】JOB執行DDL語句報錯ORA-06550 & PLS-00103Error
- 安裝MySQL出現Job for mysqld.service failed because the control process exited with error codeMySqlAIError
- ORACLE查詢JOB資訊及JOB建立Oracle
- oracle jobOracle
- java jobJava
- oracle中函式to_char()的用法介紹Oracle函式
- ORACLE TO_CHAR()函式中日期格式的使用Oracle函式
- Mysql date_format 與 Oracle to_char(date,’format’)MySqlORMOracle
- Oracle中如何停用JOB及如何使用JOBOracle