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

Error
====

ORA-39166: Object APEX_050100.APEX was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job
Job “ROOT”.”SYS_EXPORT_TABLE_01″ completed with 2 error(s) at Tue Dec 18 06:04:51 2018 elapsed 0 00:00:02

Solution
======

You can only change the database time zone if you have no TIMESTAMP WITH LOCAL TIME ZONE columns in the database otherwise ORA-02231: missing or invalid option to ALTER DATABASE (in 9i) or ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns (in 10g and up) will be seen.  


Query to check the tables with local timezones
============================


select c.owner || ‘.’ || c.table_name || ‘(‘ || c.column_name || ‘) -‘ || c.data_type || ‘ ‘ col
from dba_tab_cols c, dba_objects o
where c.data_type like ‘%WITH LOCAL TIME ZONE’
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = ‘TABLE’
order by col

/


Step 1:
=====


Export the tables that have local timezone columns in it.i.e hte output of the above query


Step 2:
======


Truncate or drop the tables with local timezone values.




Step 3
====


PURGE DBA_RECYCLEBIN


ALTER DATABASE SET TIME_ZONE = ‘+00:00’;


SHUTDOWN the database


Step 4
=====

Start the database and import back the tables.

  • January 22, 2019 | 14 views
  • Comments