在用plsql建立job时,系统老是报“未找到时区区域”这个错误。执行:select * from sys.all_scheduler_jobs;
结果:也是同样的错误执行:select dbtimezone from dual;
结果:+00:00执行:select sessiontimezone from dual;
结果:+08:00我估计是oracle的dbtimezone没有设置,请问一下该如何处理呢?
结果:也是同样的错误执行:select dbtimezone from dual;
结果:+00:00执行:select sessiontimezone from dual;
结果:+08:00我估计是oracle的dbtimezone没有设置,请问一下该如何处理呢?
2 /SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
问题是dbtimezone是“+00:00”
SQL> select dbtimezone from dual;DBTIMEZONE
----------
+00:00SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YYYY-MM-DDHH2'
------------------------------
2011-04-12 11:13:50SQL> alter database set time_zone='+8:00';
alter database set time_zone='+8:00'ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columnsSQL> select u.name||'.'||o.name||'.'||c.name tsltz_column
2 from sys.obj$ o,sys.col$ c,sys.user$ u
3 where c.type#=231 and
4 o.obj#=c.obj# and
5 u.user#=o.owner#;TSLTZ_COLUMN
--------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATESQL> desc oe.orders;
Name Type Nullable Default Comments
------------ --------------------------------- -------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORDER_ID NUMBER(12) PRIMARY KEY column.
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.
ORDER_MODE VARCHAR2(8) Y CHECK constraint.
CUSTOMER_ID NUMBER(6)
ORDER_STATUS NUMBER(2) Y 0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid
ORDER_TOTAL NUMBER(8,2) Y CHECK constraint.
SALES_REP_ID NUMBER(6) Y References hr.employees.employee_id.
PROMOTION_ID NUMBER(6) Y Sales promotion ID. Used in SH schema SQL> alter table oe.orders drop column order_date;
Table alteredSQL> alter database set time_zone='+8:00';
Database altered
SQL> shutdown normal
SQL> startup
SQL> select dbtimezone from dual;DBTIMEZONE
----------
+08:00