Changing the Time Zone of the database (Oracle10g)

To show the current setting for the time zone in Oracle, execute the following query:

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

To change the Time Zone to Europe/Amsterdam execute the following ALTER-DATABASE-command:

ALTER DATABASE SET TIME_ZONE='Europe/Amsterdam';

In my case, this command was executed unsuccessfully:

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

This means that there are tables using a date data-type with ‘local time zone’. To determine how many tables have this data-type, a next query can be executed:

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

COUNT(*)
----------
1

In this case there is only one table. The owner and name can be determined by:

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

OWNER TABLE_NAME
------------------------------ ------------------------------
OE ORDERS


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)


This oe.orders is just a table in the example schema of OE so this colums can be dropped.

SQL> alter table OE.ORDERS drop column ORDER_DATE ;

Table altered.

Now the Time Zone can be altered:

SQL> ALTER DATABASE SET TIME_ZONE='Europe/Amsterdam';

Database altered.

If the dbtimezone is queried, the change is not yet reflected:

SQL> select dbtimezone from dual ;

DBTIME
------
+00:00

A database restart is necessary to activate the Time Zone change:

SQL> connect /as sysdba ;
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1248428 bytes
Variable Size 142607188 bytes
Database Buffers 113246208 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual ;

DBTIMEZONE
----------------
Europe/Amsterdam