Changing the Time Zone of the Database

Once I was trying to change the database time zone, I got the below alert:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns.

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database. You can set the database time zone when the database is created. 
It is not possible to change the database timezone when TSLTZ columns are used. As stated before TSLTZ columns are stored normalized to the database timezone, i.e. after changing the database timezone the interpretation of the data would be wrong.




Solution.

Finding TSLTZ columns
Use the following query to find all the TSLTZ columns in the database:

select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
and o.obj# = c.obj#
where c.type# = 231
and u.user# = o.owner#;

OR
select count (*) from dba_tab_columns
where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE'; 
select owner,table_name from dba_tab_columns
where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE'; 
One should follow the below options to drop that TSLTZ cloumn from the table:
1. Backup the table that contains the TSLTZ column.
2. Drop the table or the column only.
3. Issue the alter database to change the DB time Zone.
4. Add the dropped column and restore the data OR restore the table if it's dropped. 
5. Bounce the database.

In my case, I found the table which had TSLTZ column and since it was belonging from
the EXAMPLE schema so I preferred to drop that table.