ORACLE ERROR: ORA-30079 [Update]

Situation:

Oracle DB 11g R2

SQL>alter database set time_zone=’+08:00′;

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Reason:

因為DB中有Table使用TIMESTAMP WITH…以致無法修改Time Zone

Solution:

1. 查出有幾個Table使用到此資料型態

SQL>select count (*) from dba_tab_columns where data_type like ‘TIMESTAMP%WITH LOCAL TIME ZONE’;

2. 查出是哪些Table使用到此資料型態

SQL>select owner, table_name from dba_tab_columns where data_type like ‘TIMESTAMP%WITH LOCAL TIME ZONE’;

3. 列出Table內的所有欄位

SQL>desc oe.orders;

4. 移除此欄位

SQL>alter table OE.ORDERS drop column ORDER_DATE;

5. 執行更改Time Zone指令

SQL>alter database set time_zone=’+08:00′;

6. 重新啟動DB instance以套用新設定

SQL>connect /as sysdba;

SQL>shutdown immediate;

SQL>startup;

7. 再查一次dbtimezone就會顯示新值

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s