Oracle | DBMS

Oracle DBMS_DST Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_UTILITY
DBMS_ASSERT	DBMS_SQL	KUPM$MCP
DBMS_DATAPUMP	DBMS_STANDARD	KUPP$PROC
DBMS_DST_LIB	DBMS_SYSTEM	KUPW$WORKER
DBMS_IJOB	DBMS_SYS_ERROR	V$TIMEZONE_FILE
DBMS_OUTPUT	DBMS_SYS_SQL

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	END_PREPARE	UNLOAD_SECONDARY
BEGIN_UPGRADE	END_UPGRADE	UPGRADE_DATABASE
CREATE_AFFECTED_TABLE	FIND_AFFECTED_TABLES	UPGRADE_SCHEMA
CREATE_ERROR_TABLE	GET_LATEST_TIMEZONE_VERSION	UPGRADE_TABLE
CREATE_TRIGGER_TABLE	LOAD_SECONDARY

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

      

      SQL>
      exec dbms_dst.begin_prepare(32);

      

      SELECT ora_dst_affected(SYSTIMESTAMP)

      FROM dual;

      

      ORA_DST_AFFECTED(SYSTIMESTAMP)

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

                                   0

      

      SQL> SELECT ora_dst_error(SYSTIMESTAMP)

        2  FROM dual;

      

      ORA_DST_ERROR(SYSTIMESTAMP)

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

                                0

      

      SELECT ora_dst_convert(SYSTIMESTAMP, 0, 0)

      FROM dual;

      SELECT ora_dst_convert(SYSTIMESTAMP, 0, 0)

      *

      ERROR at line 1:

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

      

      SQL>
      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);

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

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; PRAGMA SUPPLEMENTAL_LOG_DATA(get_latest_timezone_version, READ_ONLY);

SELECT dbms_dst.get_latest_timezone_version

      FROM dual;

      

      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);

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 (new 20c parameter)

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