| General Information |
| Purpose |
These routines allow the user to apply Daylight Saving Time (DST) patch to TIMESTAMP WITH TIME ZONE (TSTZ) data type |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsdst.sql |
| First Available |
11.2.0.1 |
| Dependencies |
| DATABASE_PROPERTIES |
DBMS_SCHEDULER |
DST$AFFECTED_TABLES |
| DBMS_ASSERT |
DBMS_SQL |
DST$ERROR_TABLE |
| DBMS_DATAPUMP |
DBMS_SYSTEM |
DST$TRIGGER_TABLE |
| DBMS_DST_LIB |
DBMS_SYS_ERROR |
KUPM$MCP |
| DBMS_IJOB |
DBMS_SYS_SQL |
V$TIMEZONE_FILE |
| DBMS_OUTPUT |
DBMS_UTILITY |
|
|
| Related Queries |
col value$ format a30
col comment$ format a50
SELECT name, value$, comment$
FROM props$
WHERE comment$ LIKE '%timezone%'; |
| Security Model |
Created with AUTHID CURRENT_USER. Execute is granted to the execute_catalog_role role. |
| 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;
exec dbms_dst.begin_prepare(8);
exec dbms_dst.begin_prepare(9);
-- open and read the files at $ORACLE_HOME/oracore/zoneinfo |
| |
| 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(8);
exec dbms_dst.begin_upgrade(9); |
| |
| 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; |
| exec dbms_dst.end_prepare; |
| |
| 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(9);
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(9); |
| |
| 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(9);
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(9);
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(9);
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
set serveroutput on
DECLARE
numfail PLS_INTEGER;
x NATURAL;
BEGIN
dbms_dst.begin_prepare(9);
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(9);
dbms_dst.upgrade_table(numfail, 'ORDERS');
dbms_output.put_line(numfail);
dbms_dst.end_upgrade(numfail);
dbms_output.put_line(numfail);
END IF;
END;
/ |
