Oracle DBMS_DST
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.
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