• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
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

October 16, 2022 by techgoeasy Leave a Comment

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

Table of Contents

  • 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 select database timezone
  • How can I check the session time zone?
  • How to change the Time zone in Oracle

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

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.

Filed Under: Oracle, Oracle Database Tagged With: DST, timezone, timezone settings

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us