Oracle DBMS_DST
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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

SELECT * FROM v$timezone_file;

-- using the filename returned such as timezlrg_18.dat

SQL> host

# cd $ORACLE_HOME/oracore/zoneinfo
# ls *18*
# cp timezlrg_18.dat timezlrg_19.dat
# cp timezone_18.dat timezone_19.dat
# exit

exec dbms_dst.begin_prepare(19);

-- ORA_DST_AFFECTED (new 12.1)
SELECT ora_dst_affected(SYSTIMESTAMP)
FROM dual;

-- ORA_DST_ERROR (new 12.1)
SELECT ora_dst_error(SYSTIMESTAMP)
FROM dual;

-- ORA_DST_CONVERT (new 12.1)
SELECT ora_dst_convert(SYSTIMESTAMP, 0, 0)
FROM dual;
       *
ERROR at line 1:
ORA-30092: function not allowed at this stage of DST upgrade process

-- to run ORA_DST_CONVERT the database must be in upgrade mode and a DST upgrade in progress.

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(19);
 
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
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');
BEGIN
  dbms_dst.begin_prepare(19);
  dbms_dst.find_affected_tables;
  dbms_dst.end_prepare;
END;
/

SELECT * FROM sys.dst$affected_tables;
 
LOAD_SECONDARY
Loads secondary timezone data file into SGA dbms_dst.load_secondary(sec_version IN BINARY_INTEGER);
exec dbms_dst.load_secondary(19);
 
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(19);
  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(19);
  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(19);
    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@pdborcl

set serveroutput on

DECLARE
 numfail PLS_INTEGER;
 x       NATURAL;
BEGIN
  dbms_dst.begin_prepare(19);
  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(19);
    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
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
Packages

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-2014 Daniel A. Morgan All Rights Reserved