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
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.