Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) Part Number E10577-03 |
|
|
View PDF |
The DBMS_DST package provides an interface to apply the Daylight Saving Time (DST) patch to the Timestamp with Time Zone data type.
This chapter contains the following topics:
Overview
Security Model
Views
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.
The DBMS_DST
package is an invoker's rights package.
See Also:
Oracle Database PL/SQL Language Reference for more information about using Invoker Rights or Definer RightsThe 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
The DBMS_DST
package uses the views shown in Table 58-1, "Views used by DBMS_DST", further described in the Oracle Database Reference:
Table 58-1 Views used by DBMS_DST
View | Description |
---|---|
Displays information about all tables in the database, which have columns defined on |
|
Displays information about the tables owned by the current user, which have columns defined on |
|
Displays information about the tables accessible to the current user, which have columns defined on |
Table 58-2 DBMS_DST Package Subprograms
Subprogram | Description |
---|---|
Starts a prepare window |
|
Starts an upgrade window |
|
Creates a table that has the schema shown in the comments for the FIND_AFFECTED_TABLES Procedure |
|
Creates a log error table |
|
Creates a table that is used to record active triggers disabled before performing upgrade on the table, having not been enabled due to fatal failure during the upgrading process |
|
Ends a prepare window |
|
Ends an upgrade window |
|
Finds all the tables that have affected TSTZ data due to the new timezone version |
|
Upgrades all tables in the database that have one or more columns defined on the TSTZ type, or an ADT containing the TSTZ type |
|
Upgrades tables in a specified list of schemas that has one or more columns defined on the TSTZ type, or an ADT containing the TSTZ type |
|
Upgrades a specified list of tables that has one or more columns defined on the TSTZ type or an ADT containing the TSTZ type |
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
Table 58-3 BEGIN_PREPARE Procedure Parameters
Parameter | Description |
---|---|
|
New timezone version to which the database is to be prepared to upgrade |
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
Table 58-4 BEGIN_UPGRADE Procedure Parameters
Parameter | Description |
---|---|
|
New timezone version to which the database is to be upgraded |
|
Boolean flag indicating whether to report errors on the 'overlap' time semantic conversion error. The default is |
|
Boolean flag indicating whether to report errors on the 'non-existing' time semantic conversion error. The default is |
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
Table 58-5 CREATE_AFFECTED_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the table created |
Usage Notes
This procedures takes a table_name
without schema qualification, creating a table within the current user schema.
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
Table 58-6 CREATE_ERROR_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the table created |
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
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
Table 58-7 CREATE_TRIGGER_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of table to be created |
Usage Notes
This procedures takes a table_name
without schema qualification, creating a table within the current user schema.
This procedure ends a prepare window.
Syntax
DBMS_DST.BEGIN_PREPARE;
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
Table 58-8 END_UPGRADE Procedure Parameters
Parameter | Description |
---|---|
|
Number of tables that fail to complete |
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
Table 58-9 FIND_AFFECTED_TABLES Procedure Parameters
Parameter | Description |
---|---|
|
Name of table with the following schema: CREATE TABLE dst$affected_tables ( table_owner VARCHAR2(30), table_name VARCHAR2(30), column_name VARCHAR2(4000), row_count NUMBER, error_count NUMBER) The table can be created with the CREATE_AFFECTED_TABLE Procedure. |
|
Boolean flag indicating whether to log errors during upgrade. If The default is |
|
Table name with 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) The table can be created with the CREATE_ERROR_TABLE Procedure. The |
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
Table 58-10 UPGRADE_DATABASE Procedure Parameters
Parameter | Description |
---|---|
|
Number of tables that fail to complete |
|
Boolean flag indicating whether to convert TSTZ data using the new Time Zone patch File ( |
|
Boolean flag indicating whether to convert tables using PDML (Parallel DML) or Serial DML.The default is |
|
Boolean flag indicating whether to continue after upgrade fails on the current table. The default is |
|
Boolean flag indicating whether to log errors during upgrade. If The default is |
|
Table name with 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) The table can be created with the CREATE_ERROR_TABLE Procedure. The |
|
Boolean flag indicating whether to report errors on the 'overlap' time semantic conversion error. The default is |
|
Boolean flag indicating whether to report errors on the 'non-existing' time semantic conversion error. The default is |
|
Table to log triggers which are disabled before upgrade, having not been enabled due to a fatal failure when performing an upgrade |
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
Table 58-11 UPGRADE_SCHEMA Procedure Parameters
Parameter | Description |
---|---|
|
Number of tables that fail to complete |
|
Schema name list (comma separated strings) |
|
Boolean flag indicating whether to convert TSTZ data using the new Time Zone patch File ( |
|
Boolean flag indicating whether to convert tables using PDML (Parallel DML) or Serial DML.The default is |
|
Boolean flag indicating whether to continue after upgrade fails on the current table.The default is |
|
Boolean flag indicating whether to log errors during upgrade. If The default is |
|
Table name with 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) The table can be created with the CREATE_ERROR_TABLE Procedure. The |
|
Boolean flag indicating whether to report errors on the 'overlap' time semantic conversion error. The default is |
|
Boolean flag indicating whether to report errors on the 'non-existing' time semantic conversion error. The default is |
|
Table to log triggers that are disabled before upgrade, having not been enabled due to a fatal failure when performing an upgrade |
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
Table 58-12 UPGRADE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Number of tables that fail to complete |
|
Table name list (comma separated strings) |
|
Boolean flag indicating whether to convert TSTZ data using the new Time Zone patch File ( The default is |
|
Boolean flag indicating whether to convert tables using PDML (Parallel DML), or Serial DML. The default is |
|
Boolean flag indicating whether to continue after upgrade fails on the current table. The default is |
|
Boolean flag indicating whether to log errors during upgrade. If The default is |
|
Table name with 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) The table can be created with the CREATE_ERROR_TABLE Procedure. The |
|
Boolean flag indicating whether to report errors on the 'overlap' time semantic conversion error. The default is |
|
Boolean flag indicating whether to report errors on the 'non-existing' time semantic conversion error. The default is |
|
Table to log triggers that are disabled before upgrade, having not been enabled due to a fatal failure when performing an upgrade |
|
Boolean flag indicating whether to convert the listed tables atomically (in a single transaction). If The default is |
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:
If a base table has a materialized view log table, the log table must be the next item in the list.
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
.