Consider database timezone when created during DST

Not long time ago customer asked me to create new a database and refresh it from production. Nothing special here database was quickly created and then refreshed using network import for few schemas. Few weeks later I’ve been told that the database has a timestamp problem. The date and time were correct but the time zone was different from the production:

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------
+01:00

Looking back I tried to find why that happened and I quickly found the answer in the documentation:
If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server.

Of course, by that time the time zone of the server was +1 (Daylight saving time) and the database inherited that time zone. The next logical thing was simply to change the time zone to correct one (UTC):

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

Right, that won’t work for if there are tables with columns of type TIMESTAMP WITH LOCAL TIME ZONE and there is data within these tables. Unfortunately the only solution for that is to export the database, drop the users and then import back the data. Also for the change to take effect database must be restarted.

You can simply list the columns of that type and export just these tables, I had a lot of them and decided to export/import the whole database as it was small and used for testing anyway:

SQL> select owner, table_name, column_name, data_type from all_tab_columns where data_type like '%WITH LOCAL TIME ZONE' and owner='MY_USER';
MY_USER                         INVENTORY            DSTAMP    TIMESTAMP(6) WITH LOCAL TIME ZONE