Oracle11g命令行修改时区

检查时区

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

尝试更改时区

SQL> alter database set time_zone='+8:00';
alter database set time_zone='+8:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

查看TIMESTAMP WITH LOCAL TIME ZONE字段的表

SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn from sys.obj$ o, sys.col$ c, sys.user$ u where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

TSLTZCOLUMN
--------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE

查看表结构

SQL> desc oe.orders;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID				   NOT NULL NUMBER(12)
 ORDER_DATE				   NOT NULL TIMESTAMP(6) WITH LOCAL TIME
						     ZONE
 ORDER_MODE					    VARCHAR2(8)
 CUSTOMER_ID				   NOT NULL NUMBER(6)
 ORDER_STATUS					    NUMBER(2)
 ORDER_TOTAL					    NUMBER(8,2)
 SALES_REP_ID					    NUMBER(6)
 PROMOTION_ID					    NUMBER(6)

这里发现需要调整ORDER_DATE字段的类型

修改表字段语句（尽可能保留原有字段）

# 新增临时字段
ALTER TABLE OE.ORDERS ADD TMP_COL TIMESTAMP(6);
# 复制原字段内容到临时字段
UPDATE OE.ORDERS SET TMP_COL = ORDER_DATE;
# 删掉原字段
ALTER TABLE OE.ORDERS DROP COLUMN ORDER_DATE;
# 新增新字段并调整类型
ALTER TABLE OE.ORDERS ADD ORDER_DATE TIMESTAMP(6);
# 复制临时字段内容到新字段
UPDATE OE.ORDERS SET ORDER_DATE = TMP_COL;
# 删掉临时字段
ALTER TABLE OE.ORDERS DROP COLUMN TMP_COL;
# 将新字段内容设置为NOT NULL与原字段保持一致
ALTER TABLE OE.ORDERS modify ORDER_DATE TIMESTAMP(6) NOT NULL;

修改时区

SQL> ALTER DATABASE SET TIME_ZONE='+8:00';

Database altered.

或

SQL> ALTER DATABASE SET TIME_ZONE='Asia/Shanghai';

Database altered.

关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

启动数据库

SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2213776 bytes
Variable Size		  402655344 bytes
Database Buffers	 1191182336 bytes
Redo Buffers		    7360512 bytes
Database mounted.
Database opened.

查看时区

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+08:00

或

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
Asia/Shanghai