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 is now availble. If you haven't you downloaded already: Why?

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%';