1 PACKAGE dbms_snapshot IS
2
3 -- constants for snapshot version
4 reg_unknown CONSTANT NUMBER := 0;
5 reg_v7_snapshot CONSTANT NUMBER := 1;
6 reg_v8_snapshot CONSTANT NUMBER := 2;
7 reg_repapi_snapshot CONSTANT NUMBER := 3;
8
9 -- constants for register_mview(), parameter 'flag'
10 -- NOTE: keep these constants in sync with snap$.flag
11 -- and dba_registered_mviews
12 reg_rowid_mview CONSTANT NUMBER := 16;
13 reg_primary_key_mview CONSTANT NUMBER := 32;
14 reg_object_id_mview CONSTANT NUMBER := 536870912;
15 reg_fast_refreshable_mview CONSTANT NUMBER := 1;
16 reg_updatable_mview CONSTANT NUMBER := 2;
17
18 ------------
19 -- OVERVIEW
20 --
21 -- These routines allow the user to refresh snapshots and purge logs.
22
23 ------------------------------------------------
24 -- SUMMARY OF SERVICES PROVIDED BY THIS PACKAGE
25 --
26 -- refresh - refresh a given snapshot
27 -- refresh_all - refresh all snapshots that are due
28 -- to be refreshed
29 -- refresh_dependent - refresh all stale snapshots that depend
30 -- on the specified master tables
31 -- refresh_all_mviews - refresh all stale snapshots
32 -- get_mv_dependencies - gets the list of snapshots that depend
33 -- on the specified tables/snapshots.
34 -- i_am_a_refresh - return TRUE if local site is in the process
35 -- of refreshing one or more snapshots
36 -- set_i_am_a_refresh - set the refresh indicator
37 -- begin_table_reorganization - indicate the start of a table reorganization
38 -- end_table_reorganization - indicate the end of a table reorganization
39 -- purge_log - purge log of unnecessary rows
40 -- purge_direct_load_log - purge direct loader log of unnecessary rows
41 -- register_snapshot - register a snapshot with the master site
42 -- unregister_snapshot - unregister a snapshot with the master site
43 -- purge_snapshot_from_log - purge the snapshot log for a specific
44 -- snapshot
45 -- pmarker - partition marker generator
46 -- explain_rewrite - explain why a query failed to rewrite
47 -- explain_mview - explain an mv or potential mv
48 -- estimate_mview_size - estimate the size of a potential MV
49
50 ----------------------------
51 -- PROCEDURES AND FUNCTIONS
52 --
53
54 -- -----------------------------------------------------------------------
55 -- Transaction consistent refresh of an array of snapshots.
56 -- The max number of snapshots that can be consistently refreshed is 400.
57 -- The snapshots are refreshed atomically and consistently.
58 -- Atomically: all snapshots are refreshed or none are.
59 -- Consistently: all integrity constraints that hold among master tables
60 -- will hold among the snapshot tables.
61 --
62 -- LIST
63 -- A comma-separated list or PL/SQL table of the snapshots
64 -- to be refreshed.
65 -- METHOD
66 -- A string that contains a letter for each
67 -- of the snapshots in the array according to the following codes:
68 -- '?' -- use fast refresh when possible
69 -- 'F' -- use fast refresh or raise an error if not possible
70 -- 'C' -- perform a complete refresh, copying the entire snapshot from
71 -- the master
72 -- The default method for refreshing a snapshot is the method stored for
73 -- that snapshot in the data dictionary.
74 -- ROLLBACK_SEG
75 -- The name of the rollback segment to use while
76 -- refreshing snapshots.
77 -- PUSH_DEFERRED_RPC
78 -- If TRUE then push all changes made to an updatable snapshot to its
79 -- associated master before refreshing the snapshot. Otherwise, these
80 -- changes may appear to be temporarily lost.
81 -- REFRESH_AFTER_ERRORS
82 -- If TRUE, then allow the refresh to proceed
83 -- even if there are outstanding conflicts logged in the DefError
84 -- table for the snapshot's master.
85 -- PURGE_OPTION
86 -- How to purge the transaction queue if PUSH_DEFERRED_RPC is true.
87 -- 0 = don't
88 -- 1 = cheap but imprecise (optimize for time)
89 -- 2 = expensive but precise (optimize for space)
90 -- PARALLELISM
91 -- Max degree of parallelism for pushing deferred RPCs. This value
92 -- is considered only if PUSH_DEFERRED_RPC is true.
93 -- 0 = (old algorithm) serial propagation
94 -- 1 = (new algorithm) parallel propagation with only 1 slave
95 -- n = (new algorithm) parallel propagation with n slaves
96 -- HEAP_SIZE
97 -- The max number of txns to be examined simultaneously for
98 -- parallel scheduling computation. This parameter is used only if
99 -- the value of the PARALLELISM parameter is greater than 0.
100 -- ATOMIC_REFRESH
101 -- If TRUE, then perform the refresh operations for the specified
102 -- set of snapshots in a single transaction. This guarantees that either
103 -- all of the snapshots are successfully refresh or none of the snapshots
104 -- are refreshed.
105 -- NESTED
106 -- If TRUE, then perform nested refresh operations for the specified
107 -- set of MVs. Nested refresh operations refresh all the depending MVs
108 -- and the specified set of MVs based on a dependency order to ensure
109 -- the MVs are truly fresh with respect to the underlying base tables.
110 -- OUT_OF_PLACE
111 -- If TRUE and if atomic_refresh is FALSE, then perform out-of-place
112 -- refresh. Otherwise perform in-place refresh.
113 PROCEDURE refresh(list IN VARCHAR2,
114 method IN VARCHAR2 := NULL,
115 rollback_seg IN VARCHAR2 := NULL,
116 push_deferred_rpc IN BOOLEAN := TRUE,
117 refresh_after_errors IN BOOLEAN := FALSE,
118 purge_option IN BINARY_INTEGER := 1,
119 parallelism IN BINARY_INTEGER := 0,
120 heap_size IN BINARY_INTEGER := 0,
121 atomic_refresh IN BOOLEAN := TRUE,
122 nested IN BOOLEAN := FALSE,
123 out_of_place IN BOOLEAN := FALSE);
124
125 PROCEDURE refresh(tab IN OUT dbms_utility.uncl_array,
126 method IN VARCHAR2 := NULL,
127 rollback_seg IN VARCHAR2 := NULL,
128 push_deferred_rpc IN BOOLEAN := TRUE,
129 refresh_after_errors IN BOOLEAN := FALSE,
130 purge_option IN BINARY_INTEGER := 1,
131 parallelism IN BINARY_INTEGER := 0,
132 heap_size IN BINARY_INTEGER := 0,
133 atomic_refresh IN BOOLEAN := TRUE,
134 nested IN BOOLEAN := FALSE,
135 out_of_place IN BOOLEAN := FALSE);
136
137 -- -----------------------------------------------------------------------
138 -- Execute all refresh jobs due to be executed
139 -- Requires ALTER ANY SNAPSHOT privilege
140 PROCEDURE refresh_all;
141
142 -- -----------------------------------------------------------------------
143 -- Refresh all local snapshots based on a specified local master table where
144 -- 1) the table has been modified since it was last successfully refreshed
145 -- 2) the snapshot is a member of dba_mview_analysis
146 -- NOTE:
147 -- A snapshot will be considered only if all of its tables are local.
148 --
149 -- NUMBER_OF_FAILURES
150 -- Returns the number of failures that occurred during processing.
151 -- LIST
152 -- A comma-separated list of the master tables to consider.
153 -- TAB
154 -- A PL/SQL table of the master tables to consider.
155 -- METHOD
156 -- A string of refresh methods indicating how to refresh the dependent
157 -- snapshots. All of the snapshots that depend on a particular table
158 -- are refreshed according to the refresh method associated with that
159 -- table. If a table does not have a corresponding refresh method
160 -- (that is, more tables are specified than refresh methods), then any
161 -- snapshot that depends on that table is refreshed according to its
162 -- default refresh method. The default refresh method for a snapshot
163 -- is stored in the data dictionary.
164 -- The refresh methods are represented by the following codes:
165 -- '?' -- use fast refresh if possible; otherwise, use complete refresh
166 -- 'F' -- use fast refresh if possible; otherwise, raise an error
167 -- 'C' -- use complete refresh to construct the entire snapshot from
168 -- the master tables
169 -- ROLLBACK_SEG
170 -- The name of the rollback segment to use while refreshing the snapshots.
171 -- REFRESH_AFTER_ERRORS
172 -- If TRUE and if ATOMIC_REFRESH is FALSE, then an error will not be
173 -- raised if an error is encountered during the refresh. Otherwise,
174 -- any error encountered during refresh will be raised.
175 -- ATOMIC_REFRESH
176 -- If TRUE, then refresh all of the dependent snapshots in a single
177 -- transaction. This guarantees that either all of the snapshots are
178 -- successfully refreshed or none of the snapshots are refreshed.
179 -- Otherwise, refresh each dependent snapshot in a separate transaction.
180 -- NESTED
181 -- If TRUE, then perform nested refresh operations for the specified
182 -- set of tables. Nested refresh operations refresh all the depending MVs
183 -- of the specified set of tables based on a dependency order to ensure
184 -- the MVs are truly fresh with respect to the underlying base tables.
185 -- OUT_OF_PLACE
186 -- If TRUE and if atomic_refresh is FALSE, then perform out-of-place
187 -- refresh. Otherwise perform in-place refresh.
188 PROCEDURE refresh_dependent(number_of_failures OUT BINARY_INTEGER,
189 list IN VARCHAR2,
190 method IN VARCHAR2 := NULL,
191 rollback_seg IN VARCHAR2 := NULL,
192 refresh_after_errors IN BOOLEAN := FALSE,
193 atomic_refresh IN BOOLEAN := TRUE,
194 nested IN BOOLEAN := FALSE,
195 out_of_place IN BOOLEAN := FALSE);
196
197 PROCEDURE refresh_dependent(number_of_failures OUT BINARY_INTEGER,
198 tab IN dbms_utility.uncl_array,
199 method IN VARCHAR2 := NULL,
200 rollback_seg IN VARCHAR2 := NULL,
201 refresh_after_errors IN BOOLEAN := FALSE,
202 atomic_refresh IN BOOLEAN := TRUE,
203 nested IN BOOLEAN := FALSE,
204 out_of_place IN BOOLEAN := FALSE);
205
206 -- -----------------------------------------------------------------------
207 -- Refresh all local snapshots based on a local master table where
208 -- 1) the table has been modified since it was last successfully refreshed
209 -- 2) the snapshot is a member of dba_mview_analysis
210 -- NOTE:
211 -- A snapshot will be considered only if all of its tables are local.
212 --
213 -- NUMBER_OF_FAILURES
214 -- Returns the number of failures that occurred during processing.
215 -- METHOD
216 -- A single refresh method indicating how to refresh the dependent
217 -- snapshots. If a refresh method is not specified, then any dependent
218 -- snapshot is refreshed according to its default refresh method. The
219 -- default refresh method for a snapshot is stored in the data dictionary.
220 -- A refresh method is represented by the following codes:
221 -- '?' -- use fast refresh if possible; otherwise, use complete refresh
222 -- 'F' -- use fast refresh if possible; otherwise, raise an error
223 -- 'C' -- use complete refresh to construct the entire snapshot from
224 -- the master tables
225 -- ROLLBACK_SEG
226 -- The name of the rollback segment to use while refreshing the snapshots.
227 -- REFRESH_AFTER_ERRORS
228 -- If TRUE and if ATOMIC_REFRESH is FALSE, then an error will not be
229 -- raised if an error is encountered during the refresh. Otherwise,
230 -- any error encountered during refresh will be raised.
231 -- ATOMIC_REFRESH
232 -- If TRUE, then refresh all of the dependent snapshots in a single
233 -- transaction. This guarantees that either all of the snapshots are
234 -- successfully refreshed or none of the snapshots are refreshed.
235 -- Otherwise, refresh each dependent snapshot in a separate transaction.
236 -- OUT_OF_PLACE
237 -- If TRUE and if atomic_refresh is FALSE, then perform out-of-place
238 -- refresh. Otherwise perform in-place refresh.
239 PROCEDURE refresh_all_mviews(number_of_failures OUT BINARY_INTEGER,
240 method IN VARCHAR2 := NULL,
241 rollback_seg IN VARCHAR2 := NULL,
242 refresh_after_errors IN BOOLEAN := FALSE,
243 atomic_refresh IN BOOLEAN := TRUE,
244 out_of_place IN BOOLEAN := FALSE);
245
246 -- ------------------------------------------------------------------------
247 -- This procedure finds the list of materialized view that are directly
248 -- dependent on the list of tables or materialized views that has been
249 -- specified.
250 --
251 -- LIST :
252 -- A comma separated list of the tables/materialized views to consider
253 -- DEPLIST
254 -- The list of materialized views that are directly dependent on the
255 -- tables/materialized view that has been specified in "LIST".
256 --
257 PROCEDURE get_mv_dependencies(list IN VARCHAR2,
258 deplist OUT VARCHAR2);
259
260 -- ------------------------------------------------------------------------
261 -- This procedure disables or enables snapshot replication trigger at the
262 -- local snapshot site.
263 -- value = TRUE -> disable all local replication triggers for snapshots
264 -- value = FALSE -> enable all local replication triggers for snapshots
265 PROCEDURE set_i_am_a_refresh(value IN BOOLEAN);
266
267 -- ------------------------------------------------------------------------
268 -- Returns TRUE if the local site is in the process of refreshing one or
269 -- more snapshots. Return FALSE otherwise.
270 FUNCTION i_am_a_refresh RETURN BOOLEAN;
271
272 -- ------------------------------------------------------------------------
273 -- This procedure must be called before a master table is reorganized. It
274 -- performs process to preserve snapshot data needed for refresh.
275 PROCEDURE begin_table_reorganization(tabowner IN VARCHAR2,
276 tabname IN VARCHAR2);
277
278 -- ------------------------------------------------------------------------
279 -- This procedure myst be call after a master tanel is reorganized. It
280 -- ensures that the snapshot data for the master table is valid and that
281 -- the master table is in the proper state.
282 PROCEDURE end_table_reorganization(tabowner IN VARCHAR2,
283 tabname IN VARCHAR2);
284
285 -- ------------------------------------------------------------------------
286 -- Purge the snapshot log for the specified master master of unecessary rows.
287 PROCEDURE purge_log(master IN VARCHAR2,
288 num IN BINARY_INTEGER := 1,
289 flag IN VARCHAR2 := 'NOP' );
290
291 -- ------------------------------------------------------------------------
292 -- Remove entries from the direct loader log after they are no longer
293 -- needed for any known snapshot.
294 PROCEDURE purge_direct_load_log;
295
296 -- ------------------------------------------------------------------------
297 -- Invoked at the master site by (remote) snapshot site 'snapsite' to
298 -- register snapshot 'snapname' at the master site. The invocation
299 -- is done using a synchronous RPC.
300 -- May also be invoked directly at the master site by the DBA to manually
304 -- snapowner Owner of the snapshot
301 -- register a snapshot.
302 --
303 -- Input argugments:
305 -- snapname Name of the snapshot
306 -- snapsite Name of the snapshot site (should contain no double qoutes)
307 -- snapshot_id V7 snapshot identifier
308 -- flag Attributes of the snapshot
309 -- qry_txt Snapshot definition query
310 -- rep_type Version of snapshot
311 PROCEDURE register_mview(mviewowner IN VARCHAR2,
312 mviewname IN VARCHAR2,
313 mviewsite IN VARCHAR2,
314 mview_id IN DATE,
315 flag IN BINARY_INTEGER,
316 qry_txt IN VARCHAR2,
317 rep_type IN BINARY_integer
318 := dbms_snapshot.reg_unknown);
319 -- Input argugments:
320 -- snapowner Owner of the snapshot
321 -- snapname Name of the snapshot
322 -- snapsite Name of the snapshot site (should contain no double qoutes)
323 -- snapshot_id snapshot identifier
324 -- flag Attributes of the snapshot
325 -- qry_txt Snapshot definition query
326 -- rep_type Version of snapshot
327 PROCEDURE register_mview(mviewowner IN VARCHAR2,
328 mviewname IN VARCHAR2,
329 mviewsite IN VARCHAR2,
330 mview_id IN BINARY_INTEGER,
331 flag IN BINARY_INTEGER,
332 qry_txt IN VARCHAR2,
333 rep_type IN BINARY_integer
334 := dbms_snapshot.reg_unknown);
335
336 -- ------------------------------------------------------------------------
337 -- Invoked at the master site by (remote) snapshot site 'snapsite' to
338 -- unregister snapshot 'snapname' at the master site. The invocation
339 -- is done using a synchronous RPC.
340 -- May also be invoked directly at the master site by the DBA to manually
341 -- register a snapshot.
342 --
343 -- Input argugments:
344 -- snapowner Owner of the snapshot
345 -- snapname Name of the snapshot
346 -- snapsite Name of the snapshot site (should contain no double qoutes)
347 PROCEDURE unregister_mview(mviewowner IN VARCHAR2,
348 mviewname IN VARCHAR2,
349 mviewsite IN VARCHAR2);
350
351 -- ------------------------------------------------------------------------
352 -- This procedure is called on the master site to delete the rows in
353 -- snapshot refresh related data dictionary tables maintained at the
354 -- master site for the specified snapshot identified by its snapshot_id.
355 -- If the snapshot specified is the oldest snapshot to have refreshed
356 -- from any of the master tables, then the snapshot log is also purged.
357 -- This procedure does not unregister the snapshot.
358 --
359 -- In case there is an error while purging one of the snapshot logs, the
360 -- successful purge operations of the previous snapshot logs are not rolled
361 -- back. This is to minimize the size of the snapshot logs. In case of an
362 -- error, this procedure can be invoked again until all the snapshot
363 -- logs are purged.
364 PROCEDURE purge_mview_from_log(mview_id IN BINARY_INTEGER);
365
366 -- ------------------------------------------------------------------------
367 -- This procedure is called on the master site to delete the rows in
368 -- snapshot refresh related data dictionary tables maintained at the
369 -- master site for the specified snapshot. If the snapshot specified is
370 -- the oldest snapshot to have refreshed from any of the master tables,
371 -- then the snapshot log is also purged. This procedure does not unregister
372 -- the snapshot.
373 --
374 -- In case there is an error while purging one of the snapshot logs, the
375 -- successful purge operations of the previous snapshot logs are not rolled
376 -- back. This is to minimize the size of the snapshot logs. In case of an
377 -- error, this procedure can be invoked again until all the snapshot
378 -- logs are purged.
379 PROCEDURE purge_mview_from_log(mviewowner IN VARCHAR2,
380 mviewname IN VARCHAR2,
381 mviewsite IN VARCHAR2);
382
383 FUNCTION pmarker (rid IN ROWID) RETURN NUMBER PARALLEL_ENABLE;
384
385
386 -- ------------------------------------------------------------------------
387 -- Interface for EXPLAIN_MVIEW PROCEDURES
388 -- ------------------------------------------------------------------------
389 --
390 -- ------------------------------------------------------------------------
391 -- This procedure explains the various capabilities of a potential
392 -- materialized view or an existing materialized view and the reasons
393 -- why certain capabilities would not be possible for the materialized
394 -- view. Specify a potential materialized view as a SQL SELECT statement.
395 -- Alternatively, specify an existing materialized view by giving the name
396 -- and the schema in which the materialized view was created ([schema.]mvname)
397 -- The output is placed in MV_CAPABILITIES_TABLE. Invoke the admin/utlxmv.sql
398 -- script to define MV_CAPABILITIES_TABLE prior to invoking this procedure.
399 PROCEDURE explain_mview ( mv IN VARCHAR2,
400 stmt_id IN VARCHAR2 := NULL );
401
402 -- ------------------------------------------------------------------------
403 -- This procedure explains the various capabilities of a potential
407 -- Alternatively, specify an existing materialized view by giving the name
404 -- materialized view or an existing materialized view and the reasons
405 -- why certain capabilities would not be possible for the materialized
406 -- view. Specify a potential materialized view as a SQL SELECT statement.
408 -- and the schema in which the materialized view was created ([schema.]mvname)
409 -- It accepts a CLOB instead of VARCHAR, so users can specify SQL string up
410 -- to 4G. The output is placed in MV_CAPABILITIES_TABLE. Invoke the
411 -- admin/utlxmv.sql script to define MV_CAPABILITIES_TABLE prior to invoking
412 -- this procedure.
413 PROCEDURE explain_mview ( mv IN CLOB,
414 stmt_id IN VARCHAR2 := NULL );
415
416 -- ------------------------------------------------------------------------
417 -- This procedure explains the various capabilities of a potential
418 -- materialized view or an existing materialized view and the reasons
419 -- why certain capabilities would not be possible for the materialized
420 -- view. Specify a potential materialized view as a SQL SELECT statement.
421 -- Alternatively, specify an existing materialized view by giving the name
422 -- and the schema in which the materialized view was created ([schema.]mvname)
423 -- The output is placed into an VARRAY.
424 PROCEDURE explain_mview ( mv IN VARCHAR2,
425 msg_array IN OUT SYS.ExplainMVArrayType);
426
427 -- ------------------------------------------------------------------------
428 -- This procedure explains the various capabilities of a potential
429 -- materialized view or an existing materialized view and the reasons
430 -- why certain capabilities would not be possible for the materialized
431 -- view. Specify a potential materialized view as a SQL SELECT statement.
432 -- Alternatively, specify an existing materialized view by giving the name
433 -- and the schema in which the materialized view was created ([schema.]mvname)
434 -- It accepts a CLOB instead of VARCHAR, so users can specify SQL string up to
435 -- 4G. The output is placed into an VARRAY.
436 PROCEDURE explain_mview ( mv IN CLOB,
437 msg_array IN OUT SYS.ExplainMVArrayType);
438
439 -- ------------------------------------------------------------------------
440 -- End of user interface for EXPLAIN_MVIEW PROCEDURES
441 -- ------------------------------------------------------------------------
442
443 -- ------------------------------------------------------------------------
444 -- Interface for EXPLAIN_REWRITE PROCEDURES
445 -- ------------------------------------------------------------------------
446 --
447
448 -- PROCEDURE EXPLAIN_REWRITE
449 --
450 -- PURPOSE: Explain Rewrite user interface using a table for output
451 --
452 -- PARAMETERS
453 -- ==========
454 --
455 -- QUERY : SQL select statement to be explained
456 -- MV : Fully qualified MV name specified by the user (mv_owner.mv_name)
457 -- STATEMENT_ID: a unique id from the user to distinguish output messages
458 --
459 PROCEDURE Explain_Rewrite ( QUERY IN VARCHAR2,
460 MV IN VARCHAR2 := NULL,
461 STATEMENT_ID IN VARCHAR2 := NULL);
462
463 -- PROCEDURE EXPLAIN_REWRITE
464 --
465 -- PURPOSE: Explain Rewrite user interface using a table for output. This
466 -- overloaded function uses CLOB instead of VARCHAR, so users can
467 -- specify a SQL query upto 4GB.
468 --
469 -- PARAMETERS
470 -- ==========
471 --
472 -- QUERY : SQL select statement to be explained in CLOB
473 -- MV : Fully qualified MV name specified by the user (mv_owner.mv_name)
474 -- STATEMENT_ID: a unique id from the user to distinguish output messages
475 --
476 PROCEDURE Explain_Rewrite ( QUERY IN CLOB,
477 MV IN VARCHAR2 := NULL,
478 STATEMENT_ID IN VARCHAR2 := NULL);
479
480 --
481 -- PROCEDURE EXPLAIN_REWRITE
482 --
483 -- PURPOSE: Explain Rewrite user interface using a VARRAY for output
484 --
485 -- PARAMETERS
486 -- ==========
487 --
488 -- QUERY : SQL select statement to be explained
489 -- MV : Fully qualified MV name specified by the user (mv_owner.mv_name)
490 -- MSG_ARRAY : name of the output array
491 --
492 PROCEDURE Explain_Rewrite ( QUERY IN VARCHAR2,
493 MV IN VARCHAR2 := NULL,
494 MSG_ARRAY IN OUT SYS.RewriteArrayType);
495
496 --
497 -- PROCEDURE EXPLAIN_REWRITE
498 --
499 -- PURPOSE: Explain Rewrite user interface using a VARRAY for output. This
500 -- overloaded function uses CLOB instead of VARCHAR, so users can
501 -- specify a SQL query upto 4GB.
502 --
503 -- PARAMETERS
504 -- ==========
505 --
506 -- QUERY : SQL select statement to be explained in CLOB
507 -- MV : Fully qualified MV name specified by the user (mv_owner.mv_name)
508 -- MSG_ARRAY : name of the output array
509 --
510 PROCEDURE Explain_Rewrite ( QUERY IN CLOB,
511 MV IN VARCHAR2 := NULL,
512 MSG_ARRAY IN OUT SYS.RewriteArrayType);
513
514 -- PROCEDURE EXPLAIN_REWRITE_SQLID
515 --
516 -- PURPOSE: Explain Rewrite user interface using a table for output for
517 -- using on EM
518 --
519 -- PARAMETERS
520 -- ==========
521 --
525 -- SQLID : SQL_ID of the query from EM
522 -- QUERY : SQL select statement to be explained
523 -- MV : Fully qualified MV name specified by the user (mv_owner.mv_name)
524 -- STATEMENT_ID: a unique id from the user to distinguish output messages
526 --
527 PROCEDURE Explain_Rewrite_SQLID ( QUERY IN VARCHAR2,
528 MV IN VARCHAR2 := NULL,
529 STATEMENT_ID IN VARCHAR2 := NULL,
530 SQLID IN VARCHAR2 := NULL);
531
532 -- ------------------------------------------------------------------------
533 -- End of user interface for EXPLAIN_REWRITE PROCEDURES
534 -- ------------------------------------------------------------------------
535
536 -- ------------------------------------------------------------------------
537 -- This estimates the size of a materialized view that you might create,
538 -- in bytes and number of rows.
539 -- PARAMETERS:
540 -- stmt_id: NUMBER
541 -- User-specified id
542 -- select_clause: VARCHAR2
543 -- SQL text for the defining query
544 -- num_row: NUMBER
545 -- Estimated number of rows
546 -- num_col: NUMBER
547 -- Estimated number of bytes
548 -- COMMENTS:
549 -- This procedure requires that 'utlxplan.sql' be executed
550 PROCEDURE estimate_mview_size (stmt_id IN VARCHAR2,
551 select_clause IN VARCHAR2,
552 num_rows OUT NUMBER,
553 num_bytes OUT NUMBER);
554
555
556 --- #######################################################################
557 --- INTERNAL PROCEDURES
558 ---
559 --- The following procedure provide internal functionality and should
560 --- not be called directly.
561 ---
562 --- #######################################################################
563
564 --- These interfaces are obselete in V8 and are present only for
565 --- providing backwards compatibility
566
567 PROCEDURE set_up(mowner IN VARCHAR2,
568 master IN VARCHAR2,
569 log IN OUT VARCHAR2,
570 snapshot IN OUT DATE,
571 snaptime IN OUT DATE);
572
573 PROCEDURE wrap_up(mowner IN VARCHAR2,
574 master IN VARCHAR2,
575 sshot IN DATE,
576 stime IN DATE);
577
578 PROCEDURE get_log_age(oldest IN OUT DATE,
579 mow IN VARCHAR2,
580 mas IN VARCHAR2);
581
582 -- obselete interface, present for backward compatability
583 PROCEDURE drop_snapshot(mowner IN VARCHAR2,
584 master IN VARCHAR2,
585 snapshot IN DATE);
586
587 PROCEDURE testing;
588
589 -- Internal Procedure ONLY. DO NOT USE DIRECTLY
590 -- Note: added parameter 'resources' for internal parallel resource
591 -- load balancing
592 PROCEDURE refresh_mv (pipename IN VARCHAR2,
593 mv_index IN BINARY_INTEGER,
594 owner IN VARCHAR2,
595 name IN VARCHAR2,
596 method IN VARCHAR2,
597 rollseg IN VARCHAR2,
598 atomic_refresh IN BINARY_INTEGER,
599 out_of_place IN BINARY_INTEGER,
600 env IN BINARY_INTEGER,
601 resources IN BINARY_INTEGER DEFAULT 0);
602
603 --- #######################################################################
604 --- #######################################################################
605 --- DEPRECATED PROCEDURES
606 ---
607 --- The following procedures will soon obsolete due to the materialized
608 --- view integration with snapshots. They are kept around for backwards
609 --- compatibility purposes.
610 ---
611 --- #######################################################################
612 --- #######################################################################
613 PROCEDURE register_snapshot(snapowner IN VARCHAR2,
614 snapname IN VARCHAR2,
615 snapsite IN VARCHAR2,
616 snapshot_id IN DATE,
617 flag IN BINARY_INTEGER,
618 qry_txt IN VARCHAR2,
619 rep_type IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
620
621 PROCEDURE register_snapshot(snapowner IN VARCHAR2,
622 snapname IN VARCHAR2,
623 snapsite IN VARCHAR2,
624 snapshot_id IN BINARY_INTEGER,
625 flag IN BINARY_INTEGER,
626 qry_txt IN VARCHAR2,
627 rep_type IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
628
629 PROCEDURE unregister_snapshot(snapowner IN VARCHAR2,
630 snapname IN VARCHAR2,
631 snapsite IN VARCHAR2);
632
633 PROCEDURE purge_snapshot_from_log(snapshot_id IN BINARY_INTEGER);
634
635 PROCEDURE purge_snapshot_from_log(snapowner IN VARCHAR2,
636 snapname IN VARCHAR2,
637 snapsite IN VARCHAR2);
638
639
640
641
642
643 END dbms_snapshot;
[Home] [Help]