1 PACKAGE dbms_dst AUTHID CURRENT_USER IS
2
3 ------------
4 -- OVERVIEW
5 --
6 -- These routines allow the user to apply Daylight Saving Time (DST)
7 -- patch to TIMESTAMP WITH TIME ZONE (TSTZ) data type
8
9 ------------------------------------------------
10 -- SUMMARY OF SERVICES PROVIDED BY THE PACKAGE
11 --
12 -- begin_upgrade - begin upgrade process
13 -- end_upgrade - complete the upgrade process
14 -- begin_prepare - begin the prepare window to check what tables
15 -- will be affected by the upgrade
16 -- end_prepare - complete the prepare window
17 -- upgrade_table - upgrade a list of tables with column(s) defined
18 -- on TSTZ type or ADT containing TSTZ type
19 -- upgrade_schema - upgrade all tables with column(s) defined on
20 -- TSTZ type or ADT containing TSTZ type in a
21 -- list of schemas
22 -- find_affected_tables - check all tables with TSTZ data during prepare
23 -- window and indicate which tables need upgrade
24 -- create_errors_table - create an error table for logging upgrade errors
25 -- create_affected_table - create an affected table to discover tables
26 -- which need to be upgraded during prepare window
27 -- create_trigger_table - create a trigger table for logging triggers
28 -- which were disabled during upgrade process, but
29 -- not enabled after finishing upgrade
30 -- load_secondary - load secondary TZ data file
31 -- unload_secondary - unload secondary TZ data file
32
33
34 ----------------------------
35 -- PROCEDURES AND FUNCTIONS
36 --
37
38 -- -----------------------------------------------------------------------
39 -- We have these general parameters:
40 --
41 -- TABLE_LIST
42 -- A comma-separated list or PL/SQL table of the tables to be upgraded.
43 -- UPGRADE_DATA
44 -- A boolean flag indicating if TSTZ data should be converted
45 -- using new Time Zone Data File (TRUE) or left uncoverted (FALSE).
46 -- The default is TRUE.
47 -- CONTINUE_AFTER_ERRORS
48 -- A boolean flag indicating if we should continue after upgrade fails
49 -- on the current table.
50 -- The default is TRUE.
51 -- PARALLEL
52 -- A boolean flag indicating if tables should be converted using
53 -- PDML (Parallel DML) or Serial DML.
54 -- The default is FALSE.
55 -- LOG_ERRORS
56 -- A boolean flag indicating if we should log errors during upgrade.
57 -- If FALSE, any error will abort conversion of a current table and
58 -- all upgrades to it will be rolled back. If TRUE, the error logged
59 -- logged to log_errors_table and conversion will continue. Error
60 -- logging internally uses Oracle Error logging.
61 -- The default is FALSE.
62 -- LOG_ERRORS_TABLE
63 -- A table name with the following schema:
64 -- CREATE TABLE dst_error_table
65 -- (
66 -- table_owner VARCHAR2(30),
67 -- table_name VARCHAR2(30),
68 -- column_name VARCHAR2(30),
69 -- rid urowid,
70 -- error_number NUMBER
71 -- )
72 -- The table can be created with the create_errors_table procedure.
73 -- The rid parameter records the rowids of the offending rows
74 -- and the corresponding error number.
75 -- ERROR_ON_OVERLAP_TIME
76 -- A boolean flag indicating if we should report errors on the
77 -- 'overlap' time semantic conversion error.
78 -- The default is TRUE.
79 -- ERROR_ON_NONEXISTING_TIME
80 -- A boolean flag indicating if we should report errors on the
81 -- 'non-existing' time semantic conversion error.
82 -- The default is TRUE.
83 -- ATOMIC_UPGRADE
84 -- A boolean flag indicating if we should convert the listed
85 -- tables atomically, i.e., in a single transaction.
86 -- If FALSE, each table is converted in its own transaction.
87 -- The default is FALSE.
88 -- NUM_OF_FAILURES
89 -- A variable indicating how many tables fail to complete the upgrade
90 -- process.
91 --
92
93 ------------------------------- upgrade_table ----------------------------
94 -- NAME:
95 -- upgrade_table
96 --
97 -- DESCRIPTION:
98 -- This procedure upgrades a given list of tables, which have column(s)
99 -- defined on TSTZ type or ADT containning TSTZ type. This procedure
100 -- can only be invoked after an upgrade window has been started. The
101 -- table list has to satisfy the following partial ordering:
102 -- (1) a base table needs to have its materialized view log table
103 -- immediately followed by if there is any.
104 -- (2) if the container table for a materialized view appears in the
105 -- given table list, the materialized view's 'non-upgraded' base
106 -- tables and log tables also need to appear in the table list
107 -- and before the container table
108 -- Also, a base table and its materialized view table will be upgraded
109 -- in an atomic transaction.
110 --
111 -- PARAMETERS:
112 -- num_of_failures (OUT) - See above on general parameters
113 -- table_list (IN) - Table name list (comma sep. str)
114 -- upgrade_data (IN) - See above on general parameters
115 -- parallel (IN) - See above on general parameters
116 -- continue_after_errors (IN) - See above on general parameters
117 -- log_errors (IN) - See above on general parameters
118 -- log_errors_table (IN) - See above on general parameters
119 -- error_on_overlap_time (IN) - See above on general parameters
120 -- error_on_nonexisting_time (IN) - See above on general parameters
121 -- log_triggers_table (IN) - a table to log triggers which are
122 -- disabled before upgrade, but not
123 -- being enabled due to fatal failure
124 -- when performing upgrade
125 -- atomic_ugrade (IN) - See above on general parameters
126 --
127 -- RETURN:
128 -- VOID
129 --------------------------------------------------------------------------
130 PROCEDURE upgrade_table(
131 num_of_failures OUT BINARY_INTEGER,
132 table_list IN VARCHAR2,
133 upgrade_data IN BOOLEAN := TRUE,
134 parallel IN BOOLEAN := FALSE,
135 continue_after_errors IN BOOLEAN := TRUE,
136 log_errors IN BOOLEAN := FALSE,
137 log_errors_table IN VARCHAR2 := 'sys.dst$error_table',
138 error_on_overlap_time IN BOOLEAN := FALSE,
139 error_on_nonexisting_time IN BOOLEAN := FALSE,
140 log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table',
141 atomic_upgrade IN BOOLEAN := FALSE);
142
143 ------------------------------- upgrade_schema ---------------------------
144 -- NAME:
145 -- upgrade_schema
146 --
147 -- DESCRIPTION:
148 -- This procedure upgrades tables in given list of schemas, which have
149 -- column(s) defined on TSTZ type or ADT containning TSTZ type. This
150 -- procedure can only be invoked after an upgrade window has been
151 -- started. Each table is upgraded in an atomic transaction. Note that,
152 -- a base table and its materialized view log table are upgraded in an
153 -- atomic transaction.
154 --
155 -- PARAMETERS:
156 -- num_of_failures (OUT) - See above on general parameters
157 -- schema_list (IN) - Schema name list (comma sep. str)
158 -- upgrade_data (IN) - See above on general parameters
159 -- parallel (IN) - See above on general parameters
160 -- continue_after_errors (IN) - See above on general parameters
161 -- log_errors (IN) - See above on general parameters
162 -- log_errors_table (IN) - See above on general parameters
163 -- error_on_overlap_time (IN) - See above on general parameters
164 -- error_on_nonexisting_time (IN) - See above on general parameters
165 -- log_triggers_table (IN) - a table to log triggers which are
166 -- disabled before upgrade, but not
167 -- being enabled due to fatal failure
168 -- when performing upgrade
169 --
170 -- RETURN:
171 -- VOID
172 --------------------------------------------------------------------------
173 PROCEDURE upgrade_schema(
174 num_of_failures OUT BINARY_INTEGER,
175 schema_list IN VARCHAR2,
176 upgrade_data IN BOOLEAN := TRUE,
177 parallel IN BOOLEAN := FALSE,
178 continue_after_errors IN BOOLEAN := TRUE,
179 log_errors IN BOOLEAN := FALSE,
180 log_errors_table IN VARCHAR2 := 'sys.dst$error_table',
181 error_on_overlap_time IN BOOLEAN := FALSE,
182 error_on_nonexisting_time IN BOOLEAN := FALSE,
183 log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table');
184
185 ----------------------------- upgrade_database ---------------------------
186 -- NAME:
187 -- upgrade_database
188 --
189 -- DESCRIPTION:
190 -- This procedure upgrades all tables in the database, which have
191 -- column(s) defined on TSTZ type or ADT type containning TSTZ type.
192 -- This procedure can only be invoked after an upgrade window has been
193 -- started. Each table is upgraded in an atomic transaction. Note that,
194 -- a base table and its materialized view log table are upgraded in an
195 -- atomic transaction.
196 --
197 -- PARAMETERS:
198 -- num_of_failures (OUT) - See above on general parameters
199 -- upgrade_data (IN) - See above on general parameters
200 -- parallel (IN) - See above on general parameters
201 -- continue_after_errors (IN) - See above on general parameters
202 -- log_errors (IN) - See above on general parameters
203 -- log_errors_table (IN) - See above on general parameters
204 -- error_on_overlap_time (IN) - See above on general parameters
205 -- error_on_nonexisting_time (IN) - See above on general parameters
206 -- log_triggers_table (IN) - a table to log triggers which are
207 -- disabled before upgrade, but not
208 -- being enabled due to fatal failure
209 -- when performing upgrade
210 --
211 -- RETURN:
212 -- VOID
213 --------------------------------------------------------------------------
214 PROCEDURE upgrade_database(
215 num_of_failures OUT BINARY_INTEGER,
216 upgrade_data IN BOOLEAN := TRUE,
217 parallel IN BOOLEAN := FALSE,
218 continue_after_errors IN BOOLEAN := TRUE,
219 log_errors IN BOOLEAN := FALSE,
220 log_errors_table IN VARCHAR2 := 'sys.dst$error_table',
221 error_on_overlap_time IN BOOLEAN := FALSE,
222 error_on_nonexisting_time IN BOOLEAN := FALSE,
223 log_triggers_table IN VARCHAR2 := 'sys.dst$trigger_table');
224
225 ------------------------------- begin_upgrade ----------------------------
226 -- NAME:
227 -- begin_upgrade
228 --
229 -- DESCRIPTION:
230 -- This procedure starts an upgrade window. Once an upgraded window
231 -- is started successfully, TSTZ data in dictionary tables have been
232 -- upgraded to reflect the new timezone version. Also, database property
233 -- 'DST_UPGRADE_STATE' is set to 'UPGRADE'. Database property
234 -- 'SECONDARY_TT_VERSION' is set to new timezone version. After an upgrade
235 -- is started successfully, DB has to be restarted. After the restart,
236 -- Database property 'PRIMARY_TT_VERSION' is the new timezone version and
237 -- 'SECONDARY_TT_VERSION' is the old timezone version.
238 --
239 -- PARAMETERS:
240 -- new_version (IN) - new timezone version
241 -- error_on_overlap_time (IN) - report errors on overlap time?
242 -- error_on_nonexisting_time (IN) - report errors on non-existing time?
243 -- RETURN:
244 -- VOID
245 --------------------------------------------------------------------------
246 PROCEDURE begin_upgrade(
247 new_version IN BINARY_INTEGER,
248 error_on_overlap_time IN BOOLEAN := FALSE,
249 error_on_nonexisting_time IN BOOLEAN := FALSE);
250
251 -------------------------------- end_upgrade -----------------------------
252 -- NAME:
253 -- end_upgrade
254 --
255 -- DESCRIPTION:
256 -- This procedure ends an upgrade window. An upgraded window will be
257 -- ended if all the affected user tables have been upgraded. Otherwise,
258 -- OUT parameter num_of_failures will indicate how many tables have not
259 -- been converted yet.
260 --
261 --
262 -- PARAMETERS:
263 -- num_of_failures (OUT) - See above on general parameters
264 --
265 -- RETURN:
266 -- VOID
267 --------------------------------------------------------------------------
268 PROCEDURE end_upgrade(num_of_failures OUT BINARY_INTEGER);
269
270 ------------------------------- begin_prepare ----------------------------
271 -- NAME:
272 -- begin_prepare
273 --
274 -- DESCRIPTION:
275 -- This procedure starts a prepare window. Once a prepare window
276 -- is started successfully, Database property 'DST_UPGRADE_STATE' is
277 -- set to 'PREPARE'. Database property 'SECONDARY_TT_VERSION' is set
278 -- to new timezone version.
279 --
280 -- PARAMETERS:
281 -- new_version (IN) - The new timezone version to be prepared to
282 --
283 -- RETURN:
284 -- VOID
285 --------------------------------------------------------------------------
286 PROCEDURE begin_prepare(new_version IN BINARY_INTEGER);
287
288 -------------------------------- end_prepare -----------------------------
289 -- NAME:
290 -- end_prepare
291 --
292 -- DESCRIPTION:
293 -- This procedure ends a prepare window.
294 --
295 -- PARAMETERS:
296 -- NONE
297 --
298 -- RETURN:
299 -- VOID
300 --------------------------------------------------------------------------
301 PROCEDURE end_prepare;
302
303 --------------------------- find_affected_tables -------------------------
304 -- NAME:
305 -- find_affected_tables
306 --
307 -- DESCRIPTION:
308 -- This procedure finds all the tables which have affected TSTZ data
309 -- due to the new timezone version. This procedure can only be invoked
310 -- during a prepare window. The tables which have affected TSTZ data
311 -- are recorded into a table indicated by parameter affected_tables.
312 -- If semantic errors need to be logged, they will be recorded into a
313 -- table indicated by parameter log_error_table.
314 --
315 -- PARAMETERS:
316 -- affected_tables (IN) - A table name with the following schema:
317 -- CREATE TABLE dst$affected_tables
318 -- (
319 -- table_owner VARCHAR2(30),
320 -- table_name VARCHAR2(30),
321 -- row_count NUMBER,
322 -- error_count NUMBER
323 -- )
327 -- log_errors_table (IN) - See above on general parameters
324 -- The table can be created with the
325 -- create_affected_table procedure.
326 -- log_errors (IN) - See above on general parameters
328 --
329 -- RETURN:
330 -- VOID
331 --------------------------------------------------------------------------
332 PROCEDURE find_affected_tables(
333 affected_tables IN VARCHAR2 := 'sys.dst$affected_tables',
334 log_errors IN BOOLEAN := FALSE,
335 log_errors_table IN VARCHAR2 := 'sys.dst$error_table');
336
337 --------------------------- create_affected_table ------------------------
338 -- NAME:
339 -- create_affected_table
340 --
341 -- DESCRIPTION:
342 -- This procedure creates a table which has the schema as shown in the
343 -- comments for procedure find_affected_tables.
344 --
345 -- PARAMETERS:
346 -- table_name (IN) - The name of the table to be created
347 --
348 -- RETURN:
349 -- VOID
350 --------------------------------------------------------------------------
351 PROCEDURE create_affected_table(table_name IN VARCHAR2);
352
353 ----------------------------- create_error_table -------------------------
354 -- NAME:
355 -- create_error_table
356 --
357 -- DESCRIPTION:
358 -- This procedure creates a table which has the schema as shown in the
359 -- comments for general paramters
360 --
361 -- PARAMETERS:
362 -- table_name (IN) - The name of the table to be created
363 --
364 -- RETURN:
365 -- VOID
366 --------------------------------------------------------------------------
367 PROCEDURE create_error_table(table_name IN VARCHAR2);
368
369 ----------------------------- create_trigger_table -----------------------
370 -- NAME:
371 -- create_trigger_table
372 --
373 -- DESCRIPTION:
374 -- This procedure creates a table which has the following schema. This
375 -- table is used to record active triggers which are disabled before
376 -- performing upgrade on the table, but not being enabled due to fatal
377 -- failure during the upgrading process itself.
378 --
379 -- CREATE TABLE dst_trigger_table
380 -- (
381 -- trigger_owner VARCHAR2(30),
382 -- trigger_name VARCHAR2(30)
383 -- )
384 -- PARAMETERS:
385 -- table_name (IN) - The name of the table to be created
386 --
387 -- RETURN:
388 -- VOID
389 --------------------------------------------------------------------------
390 PROCEDURE create_trigger_table(table_name IN VARCHAR2);
391
392 ------------------------------ load_secondary ----------------------------
393 -- NAME:
394 -- load_secondary
395 --
396 -- DESCRIPTION:
397 -- This procedure loads the secondary timezone data file into SGA.
398 -- In RAC, a Cross Instance Call is made to notify all other nodes in
399 -- the cluster to load the secondary timezone transition table into their
400 -- own SGA as well. Database property 'DST_UPGRADE_STATE' is either set to
401 -- 'ON_DEMAND' or if it is a data pump job, set to 'DATAPUMP(i)' - i is
402 -- the counter for data pump jobs. Also, database property
403 -- 'SECONDARY_TT_VERSION' is set to the timezone version of the loaded
404 -- secondary timezone data file when it is on-demand loading or the
405 -- first data pump job loading. Note that, if current 'DST_UPGRADE_STATE'
406 -- is 'DATAPUM(i)' and a new data pump job is requesting a different
407 -- secondary TZ version than the existing data pump jobs, we will not
408 -- allow it.
409 --
410 -- PARAMETERS:
411 -- sec_version (IN) - The secondary timezone version to be loaded
412
413 --
414 -- RETURN:
415 -- VOID
416 --------------------------------------------------------------------------
417 PROCEDURE load_secondary(sec_version IN BINARY_INTEGER);
418
419 ---------------------------- unload_secondary ----------------------------
420 -- NAME:
421 -- unload_secondary
422 --
423 -- DESCRIPTION:
424 -- This procedure unloads the secondary timezone data file from SGA.
425 -- In RAC, a Cross Instance Call is made to notify all other nodes in
426 -- the cluster to unload the secondary timezone transition table from
427 -- their own SGA as well. Also, database property 'DST_UPGRADE_STATE'
428 -- is set to 'NORMAL' or 'DATAPUMP(i-1)' (if the caller is a data pump
429 -- job where i > 1) and database property 'SECONDARY_TT_VERSION' is
430 -- set to 0 when 'DST_UPGRADE_STATE' is set to 'NORMAL'.
431 --
432 -- PARAMETERS:
433 -- NONE
434 --
435 -- RETURN:
436 -- VOID
437 --------------------------------------------------------------------------
438 PROCEDURE unload_secondary;
439
440 END dbms_dst;
[Home] [Help]