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

原文地址：https://blog.csdn.net/m0_46267097/article/details/136478114

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！