Oracle DBMS_DST
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
First Available 11.2.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsdst.sql
Subprograms
 
BEGIN_PREPARE
Starts a prepare window dbms_dst.begin_prepare(begin_prepare(new_version IN BINARY_INTEGER);
conn / as sysdba

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

SQL> SELECT ora_dst_affected(SYSTIMESTAMP)
  2  FROM dual;

ORA_DST_AFFECTED(SYSTIMESTAMP)
------------------------------
                             0

SQL> SELECT ora_dst_error(SYSTIMESTAMP)
  2  FROM dual;

ORA_DST_ERROR(SYSTIMESTAMP)
---------------------------
                          0

SQL> SELECT ora_dst_convert(SYSTIMESTAMP, 0, 0)
  2  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');

desc uw_cat
 
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');

desc uw_cet
 
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');

desc uw_ctt
 
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 (new 18.1 parameter)
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(31);
  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;
SQL> SELECT dbms_dst.get_latest_timezone_version
  2  FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         31
 
LOAD_SECONDARY
Loads secondary timezone data file into SGA dbms_dst.load_secondary(sec_version IN BINARY_INTEGER);
exec dbms_dst.load_secondary(31);
 
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);
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%';

Related Topics
Built-in Functions
Built-in Packages
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
Packages
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved