Steps to update the TIME ZONE in a database.

Please follow the below step to update the timezone in a database whenever you upgrade the database if your timezone version is less than 14.

SQL> select * from v$timezone_file;


FILENAME                VERSION
——————– ———————–
timezlrg_14.dat              14

conn / as sysdba

purge dba_recyclebin;

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘upg_tzv’)

alter session set “_with_subquery”=materialize;

alter session set “_simple_view_merging”=TRUE;

exec DBMS_DST.BEGIN_PREPARE(18);

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => ‘sys.dst$affected_tables’,
log_errors => TRUE,
log_errors_table => ‘sys.dst$error_table’);
END;
/

SELECT * FROM sys.dst$affected_tables;

SELECT * FROM sys.dst$error_table;

EXEC DBMS_DST.END_PREPARE;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘upg_tzv’)

alter session set “_with_subquery”=materialize;

alter session set “_simple_view_merging”=TRUE;

EXEC DBMS_DST.BEGIN_UPGRADE(18);

SELECT * FROM sys.dst$error_table;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS=’YES’;

shutdown immediate
startup

alter session set “_with_subquery”=materialize;
alter session set “_simple_view_merging”=TRUE;

set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => ‘SYS.DST$ERROR_TABLE’,
log_triggers_table => ‘SYS.DST$TRIGGER_TABLE’,
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);
END;
/

SELECT * FROM sys.dst$error_table;

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :fail);
END;
/

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

SELECT * FROM v$timezone_file;

conn / as sysdba
SELECT VERSION FROM v$timezone_file;
select TZ_VERSION from registry$database;

conn / as sysdba
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;