Home » Oracle » Oracle Database » how to check timezone in oracle database: DST level, TSTZ & TSLTZ columns

how to check timezone in oracle database: DST level, TSTZ & TSLTZ columns

I am presenting here some of the good queries on timezone settings like how to check timezone in oracle database, how to check DST patch in oracle, How to select TimeStamp with Time Zone (TSTZ) columns in your database, How to select the TimeStamp with Local Time Zone (TSLTZ) columns in your database

how to check timezone in oracle database/how to check DST patch in oracle

SQL> select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4

or

SELECT tz_version FROM registry$database;

or

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

With 19c, we have a CDB database, So we can have different timezone versions for the pdb. Please login to the correct PDB to get the timezone version

alter session set container='TEST_PDB';
select * from v$timezone_file;

How to select TimeStamp with Time Zone (TSTZ) columns in your database:


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 TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by col

How to select the TimeStamp with Local Time Zone (TSLTZ) columns in your database:


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
/

How to select database timezone


SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
--------------
+00:00

How can I check the session time zone?

SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------
+01:00

How to change the Time zone in Oracle

ALTER DATABASE SET TIME_ZONE = '+00:00';
  • This will only work if there are no TSLTZ values already stored in the database or an ORA-02231 (9i) or ORA-30079 will be seen
  • The change will not take effect until the database is restarted
See also  How to check the java version in Unix & Windows

Hope you like these queries on Timezone settings in the Oracle database. Please do provide your feedback

Related Articles
Automatic Workload Repository: Learn about Automatic Workload Repository(AWR).About views, table, how to purge information, how to collect and frequency of collection
alter system kill session: We can kill an oracle session using alter system kill session in the Oracle database. Sessions are marked for killed if not killed immediately
Oracle 12c pluggable database commands: Check out this post for useful Oracle 12c pluggable database commands example how to check container database in oracle 12c, drop pluggable database
oracle date functions: Check out this post for oracle date functions, oracle date difference in years, oracle date difference in days, oracle date difference in months.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top