58 DBMS_DST

Using DBMS_DST

• Overview

• Security Model

• Views

Overview

The transition period during which Daylight Saving Time comes into effect, or stops being in effect, has the potential for problems, such as data loss, when handling timestamps with time zone data. The DBMS_DST package enables working with these transitions in the context of a set of rules.

Security Model

The DBMS_DST package is an invoker's rights package.

The execute privilege on the package is granted to the EXECUTE_CATALOG_ROLE role. This role is normally granted to selected users to allow EXECUTE privileges for packages and procedures in the data dictionary.

The user that invokes the package must have the following privileges:

• CREATE ANY TABLE

• ALTER ANY TABLE

• DROP ANY TABLE

• SELECT ANY TABLE

• LOCK ANY TABLE

• ALTER ANY INDEX

• ALTER ANY TRIGGER

• UPDATE ANY TABLE

• EXECUTE ANY TYPE

Views

The DBMS_DST package uses the views shown in Table 58-1, "Views used by DBMS_DST", further described in the Oracle Database Reference:

Summary of DBMS_DST Subprograms

BEGIN_PREPARE Procedure

This procedure starts a prepare window. Once a prepare window is started successfully, the database property 'DST_UPGRADE_STATE' is set to 'PREPARE', and the database property 'SECONDARY_TT_VERSION' is set to a new timezone version.

The prepare window lets a DBA investigate data affected by the upgrade, and so judge when it is optimal to perform the upgrade. The prepare window can overlap normal database operation.

Syntax

DBMS_DST.BEGIN_PREPARE (
   new_version                IN  BINARY_INTEGER);

Parameters

BEGIN_UPGRADE Procedure

This procedure starts an upgrade window. When an upgraded window is started successfully, the TSTZ data in the dictionary tables is upgraded to reflect the new timezone version, and the database property 'DST_UPGRADE_STATE' is set to 'UPGRADE'. Once BEGIN_UPGRADE has been performed successfully, the user must re-start the database. After a successful restart, the database property 'PRIMARY_TT_VERSION' is the new timezone version, and 'SECONDARY_TT_VERSION' is the old timezone version.

The procedure operates atomically, and upgrades all or none of the dictionary tables and the database properties. It must be called in the database in OPEN MIGRATE mode.

Syntax

DBMS_DST.BEGIN_UPGRADE (
   new_version                IN  BINARY_INTEGER,
   error_on_overlap_time      IN  BOOLEAN := FALSE,
   error_on_nonexisting_time  IN  BOOLEAN := FALSE);

Parameters

CREATE_AFFECTED_TABLE Procedure

This procedure creates a table that has the schema shown in the comments for the FIND_AFFECTED_TABLES Procedure.

Syntax

DBMS_DST.CREATE_AFFECTED_TABLE (
   table_name      IN  VARCHAR2);

Parameters

Usage Notes

This procedures takes a table_name without schema qualification, creating a table within the current user schema.

CREATE_ERROR_TABLE Procedure

This procedure creates a log error table which has the following schema:

CREATE TABLE dst$error_table(
   table_owner     VARCHAR2(30),
   table_name      VARCHAR2(30),
   column_name     VARCHAR2(4000),
   rid             ROWID,
   error_number    NUMBER)

Syntax

DBMS_DST.CREATE_ERROR_TABLE (
   table_name      IN  VARCHAR2);

Parameters

Usage Notes

• This procedures takes a table_name without schema qualification, creating a table within the current user schema.

• The error number is found when upgrading time zone file and timestamp with time zone data. For more information about error handling when upgrading time zone file and timestamp with time zone data, see Oracle Database Globalization Support Guide

CREATE_TRIGGER_TABLE Procedure

This procedure creates a table that has the following schema.

CREATE TABLE dst_trigger_table (
trigger_owner  VARCHAR2(30),
trigger_name   VARCHAR2(30));

This table is used to record active triggers that are disabled before performing upgrade on the table, having not been enabled due to fatal failure during the upgrading process.

Syntax

DBMS_DST.CREATE_TRIGGER_TABLE (
   table_name     IN    VARCHAR2);

Parameters

Usage Notes

This procedures takes a table_name without schema qualification, creating a table within the current user schema.

END_PREPARE Procedure

This procedure ends a prepare window.

Syntax

DBMS_DST.BEGIN_PREPARE;

END_UPGRADE Procedure

This procedure ends an upgrade window. An upgraded window is ended if all the affected user tables have been upgraded. Otherwise, the OUT parameter num_of_failures indicates how many tables have not been converted.

Syntax

DBMS_DST.END_UPGRADE (
  num_of_failures    OUT  BINARY_INTEGER);

Parameters

FIND_AFFECTED_TABLES Procedure

This procedure 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. The tables which have affected TSTZ data are recorded into a table indicated by parameter affected_tables. If semantic errors must be logged, they are recorded into a table indicated by parameter log_errors_table.

Syntax

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

Parameters

CREATE TABLE dst$affected_tables (
table_owner VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(4000),
row_count NUMBER,
error_count NUMBER)

CREATE TABLE dst$error_table (
table_owner VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(4000),
rid ROWID,
error_number NUMBER)

UPGRADE_DATABASE Procedure

This procedure upgrades all tables in the database, which have one or more columns defined on the TSTZ type or an ADT containing the TSTZ type. This procedure can only be invoked after an upgrade window has been started. Each table is upgraded in an atomic transaction. Note that, a base table and its materialized view log table are upgraded in an atomic transaction.

Syntax

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

Parameters

CREATE TABLE dst$error_table (
table_owner VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(4000),
rid ROWID,
error_number NUMBER)

UPGRADE_SCHEMA Procedure

This procedure upgrades tables in a specified list of schemas that have one or more columns defined on the TSTZ type, or an ADT containing the TSTZ type. This procedure can be invoked only after an upgrade window has been started. Each table is upgraded in an atomic transaction. Note that a base table and its materialized view log table are upgraded in an atomic transaction.

Syntax

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

Parameters

CREATE TABLE dst$error_table (
table_owner VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(4000),
rid ROWID,
error_number NUMBER)

UPGRADE_TABLE Procedure

This procedure upgrades a specified list of tables that have one or more columns defined on the TSTZ type, or an ADT containing the TSTZ type.

Syntax

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

Parameters

CREATE TABLE dst$error_table (
table_owner VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(4000),
rid ROWID,
error_number NUMBER)

Usage Notes

This procedure can only be invoked after an upgrade window has been started. The table list has to satisfy the following partial ordering:

1. If a base table has a materialized view log table, the log table must be the next item in the list.

2. If the container table for a materialized view appears in the list, the materialized view's 'non-upgraded' base tables and log tables must appear in the table list and before the container table.

A base table and its materialized view log table need to be upgraded in an atomic transaction by specifying atomic_upgrade to TRUE.