Oracle | DBMS

Oracle DBMS_DST Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose These routines allow the user to apply Daylight Saving Time (DST) patch to TIMESTAMP WITH TIME ZONE (TSTZ) data type

AUTHID CURRENT_USER

Dependencies

DATABASE_PROPERTIES	DBMS_SCHEDULER	DBMS_SYS_SQL
DBMS_ASSERT	DBMS_SQL	DBMS_UTILITY
DBMS_DATAPUMP	DBMS_STANDARD	KUPM$MCP
DBMS_DST_LIB	DBMS_SYSTEM	KUPP$PROC
DBMS_IJOB	DBMS_SYS_ERROR	KUPW$WORKER
DBMS_OUTPUT

Documented Yes: Packages and Types Reference

First Available 11.2

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(DEFAULT, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmsdst.sql

Subprograms

BEGIN_PREPARE	FIND_AFFECTED_TABLES
BEGIN_UPGRADE	GET_LATEST_TIMEZONE_VERSION
CANCEL_BEGIN_UPGRADE	LOAD_SECONDARY
CREATE_AFFECTED_TABLE	PATCHLESS_TIMEZONE_VERSION_UPGRADE
CREATE_ERROR_TABLE	UNLOAD_SECONDARY
CREATE_TRIGGER_TABLE	UPGRADE_DATABASE
DROP_DST_UPGRADE_CONSTRAINTS	UPGRADE_SCHEMA
END_PREPARE	UPGRADE_TABLE
END_UPGRADE

BEGIN_PREPARE

Starts a prepare window dbms_dst.begin_prepare(new_version IN BINARY_INTEGER);

conn / as sysdba

      

      SELECT * FROM v$timezone_file;

      

      FILENAME         VERSION CON_ID

      ---------------- ------- ------

      timezlrg_31.dat       31      0

      

      -- using the filename returned such as timezlrg_18.dat

      

      SQL> host

      

      # cd $ORACLE_HOME/oracore/zoneinfo

      # ls *31*

      # cp timezlrg_31.dat timezlrg_32.dat

      # cp timezone_31.dat timezone_32.dat

      # exit

      

      exec dbms_dst.begin_prepare(32);

      

      SELECT ora_dst_affected(SYSTIMESTAMP);

      

      ORA_DST_AFFECTED(SYSTIMESTAMP)

      ------------------------------

                                   0

      

      SELECT ora_dst_error(SYSTIMESTAMP);

      

      ORA_DST_ERROR(SYSTIMESTAMP)

      ---------------------------

                                0

      

      SELECT ora_dst_convert(SYSTIMESTAMP, 0, 0);

             *

      ORA-30092: function not allowed at this stage of DST upgrade process

      

      exec dbms_dst.end_prepare;

BEGIN_UPGRADE

Starts an upgrade window dbms_dst.begin_upgrade( new_version IN BINARY_INTEGER, error_on_overlap_time IN BOOLEAN := FALSE, error_on_nonexisting_time IN BOOLEAN := FALSE);

exec dbms_dst.begin_upgrade(31);

CANCEL_BEGIN_UPGRADE (new 23ai)

Undocumented dbms_dst.cancel_begin_upgrade(

TBD

CREATE_AFFECTED_TABLE

Creates a table that holds a list of affected tables dbms_dst.create_affected_table(table_name IN VARCHAR2);

exec dbms_dst.create_affected_table('UW_CAT');

      

      "SYS"."UW_CAT" has been successfully created.

      

      PL/SQL procedure successfully completed.

      

      desc uw_cat

      

       Name                         Null?    Type

      ----------------------------- -------- ---------------

      TABLE_OWNER                   NOT NULL VARCHAR2(128)

      TABLE_NAME                    NOT NULL VARCHAR2(128)

      COLUMN_NAME                   NOT NULL VARCHAR2(4000)

      ROW_COUNT                              NUMBER

      ERROR_COUNT                            NUMBER

CREATE_ERROR_TABLE

Creates a table that holds a list of errors dbms_dst.create_error_table(table_name IN VARCHAR2);

exec dbms_dst.create_affected_table('UW_CET');

      

      "SYS"."UW_CET" has been successfully created.

      

      PL/SQL procedure successfully completed.

      

      desc uw_cet

      

      Name                          Null?    Type

      ----------------------------- -------- ---------------

      TABLE_OWNER                   NOT NULL VARCHAR2(128)

      TABLE_NAME                    NOT NULL VARCHAR2(128)

      COLUMN_NAME                   NOT NULL VARCHAR2(4000)

      ROW_COUNT                              NUMBER

      ERROR_COUNT                            NUMBER

CREATE_TRIGGER_TABLE

Creates a table used to record active triggers which are disabled before performing an upgrade on the table, but not re-enabled due to a fatal failure during the upgrade process dbms_dst.create_trigger_table(table_name IN VARCHAR2);

exec dbms_dst.create_affected_table('UW_CTT');

      

      "SYS"."UW_CTT" has been successfully created.

      

      PL/SQL procedure successfully completed.

      

      desc uw_ctt

      

      Name                          Null?    Type

      ----------------------------- -------- --------------------

      TABLE_OWNER                   NOT NULL VARCHAR2(128)

      TABLE_NAME                    NOT NULL VARCHAR2(128)

      COLUMN_NAME                   NOT NULL VARCHAR2(4000)

      ROW_COUNT                              NUMBER

      ERROR_COUNT                            NUMBER

DROP_DST_UPGRADE_CONSTRAINTS (new 23ai)

Undocumented dbms_dst.drop_dst_upgrade_constraints(

TBD

END_PREPARE

Ends a prepare window dbms_dst.end_prepare;

See BEGIN_PREPARE Demo Above

END_UPGRADE

Ends an upgrade window dbms_dst.end_upgrade(num_of_failures OUT BINARY_INTEGER);

See Demos Below

FIND_AFFECTED_TABLES

Finds all the tables which have affected TSTZ data due to the new timezone version. This procedure can only be invoked during a prepare window. dbms_dst.find_affected_tables( affected_tables IN VARCHAR2 := 'sys.dst$affected_tables', log_errors IN BOOLEAN := FALSE, log_errors_table IN VARCHAR2 := 'sys.dst$error_table', parallel IN BOOLEAN := FALSE);

BEGIN

        dbms_dst.begin_prepare(35);

        dbms_dst.find_affected_tables;

        dbms_dst.end_prepare;

      END;

      /

      

      SELECT * FROM sys.dst$affected_tables;

GET_LATEST_TIMEZONE_VERSION

Returns the version number of the latest time zone data file in directory $ORACLE_HOME/oracore/zoneinfo/ dbms_dst.get_latest_timezone_version RETURN BINARY_INTEGER;

SELECT dbms_dst.get_latest_timezone_version;

      

      GET_LATEST_TIMEZONE_VERSION

      ---------------------------

                               34

LOAD_SECONDARY

Loads secondary timezone data file into SGA dbms_dst.load_secondary(sec_version IN BINARY_INTEGER);

exec dbms_dst.load_secondary(34);

PATCHLESS_TIMEZONE_VERSION_UPGRADE (new 23ai)

Undocumented dbms_dst.patchless_timezone_version_upgrade(

TBD

UNLOAD_SECONDARY

Unload secondary TZ data file from the SGA dbms_dst.unload_secondary;

exec dbms_dst.unload_secondary;

UPGRADE_DATABASE

Upgrades all tables in the database, which have column(s) defined on TSTZ type or ADT type containing TSTZ type dbms_dst.upgrade_database( num_of_failures OUT BINARY_INTEGER, upgrade_data IN BOOLEAN := TRUE, parallel IN BOOLEAN := FALSE, continue_after_errors IN BOOLEAN := TRUE, log_errors IN BOOLEAN := FALSE, log_errors_table IN VARCHAR2 := 'sys.dst$error_table', error_on_overlap_time IN BOOLEAN := FALSE, error_on_nonexisting_time IN BOOLEAN := FALSE, log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table');

set serveroutput on

      

      DECLARE

       numfail PLS_INTEGER;

      BEGIN

        dbms_dst.begin_upgrade(31);

        dbms_dst.upgrade_database(numfail);

        dbms_output.put_line(numfail);

        dbms_dst.end_upgrade(numfail);

        dbms_output.put_line(numfail);

      END;

      /

UPGRADE_SCHEMA

Upgrades tables in given list of schemas, which have column(s) defined on TSTZ type or ADT containing TSTZ type dbms_dst.upgrade_schema( num_of_failures OUT BINARY_INTEGER, schema_list IN VARCHAR2, upgrade_data IN BOOLEAN := TRUE, parallel IN BOOLEAN := FALSE, continue_after_errors IN BOOLEAN := TRUE, log_errors IN BOOLEAN := FALSE, log_errors_table IN VARCHAR2 := 'sys.dst$error_table', error_on_overlap_time IN BOOLEAN := FALSE, error_on_nonexisting_time IN BOOLEAN := FALSE, log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table');

set serveroutput on

      

      DECLARE

       numfail PLS_INTEGER;

       x       NATURAL;

      BEGIN

        dbms_dst.begin_prepare(31);

        dbms_dst.find_affected_tables;

        dbms_dst.end_prepare;

      

        SELECT COUNT(*)

        INTO x

        FROM dst$affected_tables;

      

        IF x = 0 THEN

          dbms_dst.begin_upgrade(31);

          dbms_dst.upgrade_schema(numfail, 'OE');

          dbms_output.put_line(numfail);

          dbms_dst.end_upgrade(numfail);

          dbms_output.put_line(numfail);

        END IF;

      END;

      /

UPGRADE_TABLE

Upgrades a given list of tables, which have column(s) defined on TSTZ type or ADT containing TSTZ type dbms_dst.upgrade_table( num_of_failures OUT BINARY_INTEGER, table_list IN VARCHAR2, upgrade_data IN BOOLEAN := TRUE, parallel IN BOOLEAN := FALSE, continue_after_errors IN BOOLEAN := TRUE, log_errors IN BOOLEAN := FALSE, log_errors_table IN VARCHAR2 := 'sys.dst$error_table', error_on_overlap_time IN BOOLEAN := FALSE, error_on_nonexisting_time IN BOOLEAN := FALSE, log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table', atomic_upgrade IN BOOLEAN := FALSE, from_tz_version IN BINARY_INTEGER := 0);

conn oe/oe@pdbdev

      

      set serveroutput on

      

      DECLARE

       numfail PLS_INTEGER;

       x       NATURAL;

      BEGIN

        dbms_dst.begin_prepare(31);

        dbms_dst.find_affected_tables;

        dbms_dst.end_prepare;

      

        SELECT COUNT(*)

        INTO x

        FROM dst$affected_tables;

      

        IF x = 0 THEN

          dbms_dst.begin_upgrade(31);

          dbms_dst.upgrade_table(numfail, 'ORDERS');

          dbms_output.put_line(numfail);

          dbms_dst.end_upgrade(numfail);

          dbms_output.put_line(numfail);

        END IF;

      END;

      /

Related Queries

View timezone related properties col value$ format a30 col comment$ format a50 SELECT name, value$, comment$ FROM props$ WHERE comment$ LIKE '%timezone%';