1 PACKAGE dbms_streams_adm AUTHID CURRENT_USER AS
2
3 -------------
4 -- CONSTANTS
5 instantiation_none CONSTANT BINARY_INTEGER := 0;
6 instantiation_table CONSTANT BINARY_INTEGER := 1;
7 instantiation_table_network CONSTANT BINARY_INTEGER := 2;
8 instantiation_schema CONSTANT BINARY_INTEGER := 3;
9 instantiation_schema_network CONSTANT BINARY_INTEGER := 4;
10 instantiation_full CONSTANT BINARY_INTEGER := 5;
11 instantiation_full_network CONSTANT BINARY_INTEGER := 6;
12 -- transportable tablespace
13 instantiation_tts CONSTANT BINARY_INTEGER := 7;
14 instantiation_tts_network CONSTANT BINARY_INTEGER := 8;
15
16 -- The following constants are used by the prepare_upgrade API
17 exclude_flags_full CONSTANT BINARY_INTEGER := 1;
18 exclude_flags_unsupported CONSTANT BINARY_INTEGER := 2;
19 exclude_flags_dml CONSTANT BINARY_INTEGER := 4;
20 exclude_flags_ddl CONSTANT BINARY_INTEGER := 8;
21
22 -- The following constants are used by set_message_tracing
23 action_trace CONSTANT BINARY_INTEGER := 1;
24 action_memory CONSTANT BINARY_INTEGER := 2;
25
26
27 -------------
28 -- EXCEPTIONS
29 client_ruleset_not_exist EXCEPTION;
30 PRAGMA exception_init(client_ruleset_not_exist, -26698);
31 client_ruleset_not_exist_num NUMBER := -26698;
32
33 dequeue_exists EXCEPTION;
34 PRAGMA exception_init(dequeue_exists, -26699);
35 dequeue_exists_num NUMBER := -26699;
36
37 cannot_create_process EXCEPTION;
38 PRAGMA exception_init(cannot_create_process, -26664);
39 cannot_create_process_num NUMBER := -26664;
40
41 process_exists EXCEPTION;
42 PRAGMA exception_init(process_exists, -26665);
43 process_exists_num NUMBER := -26665;
44
45 invalid_parameter EXCEPTION;
46 PRAGMA exception_init(invalid_parameter, -26667);
47 invalid_parameter_num NUMBER := -26667;
48
49 process_not_exist EXCEPTION;
50 PRAGMA exception_init(process_not_exist, -26701);
51 process_not_exist_num NUMBER := -26701;
52
53 role_required EXCEPTION;
54 PRAGMA exception_init(role_required, -26723);
55 role_required_num NUMBER := -26723;
56
57 set_user_to_sys EXCEPTION;
58 PRAGMA exception_init(set_user_to_sys, -26724);
59 set_user_to_sys_num NUMBER := -26724;
60
61 mult_trans_specified EXCEPTION;
62 PRAGMA exception_init(mult_trans_specified, -26754);
63 mult_trans_specified_num NUMBER := -26754;
64
65 /*split off a propagation. If any of cloned_propagation_name,
66 cloned_capture_namei, cloned_queue_name are null, we will
67 generate a name for it*/
68 PROCEDURE split_streams (
69 propagation_name IN VARCHAR2,
70 cloned_propagation_name IN VARCHAR2 DEFAULT NULL,
71 cloned_queue_name IN VARCHAR2 DEFAULT NULL,
72 cloned_capture_name IN VARCHAR2 DEFAULT NULL,
73 perform_actions IN BOOLEAN DEFAULT TRUE,
74 script_name IN VARCHAR2 DEFAULT NULL,
75 script_directory_object IN VARCHAR2 DEFAULT NULL,
76 auto_merge_threshold IN NUMBER DEFAULT NULL,
77 schedule_name IN OUT VARCHAR2,
78 merge_job_name IN OUT VARCHAR2);
79
80 /* merge the propagation */
81 PROCEDURE merge_streams (
82 cloned_propagation_name IN VARCHAR2,
83 propagation_name IN VARCHAR2 DEFAULT NULL,
84 queue_name IN VARCHAR2 DEFAULT NULL,
85 perform_actions IN BOOLEAN DEFAULT TRUE,
86 script_name IN VARCHAR2 DEFAULT NULL,
87 script_directory_object IN VARCHAR2 DEFAULT NULL);
88
89 /* This function is called by a merge streams job to merge two streams */
90 PROCEDURE merge_streams_job (
91 cloned_propagation_name IN VARCHAR2,
92 propagation_name IN VARCHAR2 DEFAULT NULL,
93 queue_name IN VARCHAR2 DEFAULT NULL,
94 merge_threshold IN NUMBER,
95 schedule_name IN VARCHAR2 DEFAULT NULL,
96 merge_job_name IN VARCHAR2 DEFAULT NULL);
97
98 /* Add message rule */
99 PROCEDURE add_message_rule (
100 message_type IN VARCHAR2,
101 rule_condition IN VARCHAR2,
102 streams_type IN VARCHAR2,
103 streams_name IN VARCHAR2 DEFAULT NULL,
104 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
105 inclusion_rule IN BOOLEAN DEFAULT TRUE);
106
107 /* Add message rule */
108 PROCEDURE add_message_rule (
109 message_type IN VARCHAR2,
110 rule_condition IN VARCHAR2,
111 streams_type IN VARCHAR2,
112 streams_name IN VARCHAR2 DEFAULT NULL,
113 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
114 inclusion_rule IN BOOLEAN DEFAULT TRUE,
115 rule_name OUT VARCHAR2);
116
117 /* Add propagation rule */
118 PROCEDURE add_message_propagation_rule (
119 message_type IN VARCHAR2,
120 rule_condition IN VARCHAR2,
121 streams_name IN VARCHAR2 DEFAULT NULL,
122 source_queue_name IN VARCHAR2,
123 destination_queue_name IN VARCHAR2,
124 inclusion_rule IN BOOLEAN DEFAULT TRUE,
125 queue_to_queue IN BOOLEAN DEFAULT NULL);
126
127 /* Add propagation rule */
128 PROCEDURE add_message_propagation_rule (
129 message_type IN VARCHAR2,
130 rule_condition IN VARCHAR2,
131 streams_name IN VARCHAR2 DEFAULT NULL,
132 source_queue_name IN VARCHAR2,
133 destination_queue_name IN VARCHAR2,
134 inclusion_rule IN BOOLEAN DEFAULT TRUE,
135 rule_name OUT VARCHAR2,
136 queue_to_queue IN BOOLEAN DEFAULT NULL);
137
138 /* Adds capture or apply rules for a table. */
139 PROCEDURE add_table_rules(
140 table_name IN VARCHAR2,
141 streams_type IN VARCHAR2,
142 streams_name IN VARCHAR2 DEFAULT NULL,
143 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
144 include_dml IN BOOLEAN DEFAULT TRUE,
145 include_ddl IN BOOLEAN DEFAULT FALSE,
146 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
147 source_database IN VARCHAR2 DEFAULT NULL,
148 inclusion_rule IN BOOLEAN DEFAULT TRUE,
149 and_condition IN VARCHAR2 DEFAULT NULL);
150
151 /* Adds capture or apply rules for a table. */
152 PROCEDURE add_table_rules(
153 table_name IN VARCHAR2,
154 streams_type IN VARCHAR2,
155 streams_name IN VARCHAR2 DEFAULT NULL,
156 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
157 include_dml IN BOOLEAN DEFAULT TRUE,
158 include_ddl IN BOOLEAN DEFAULT FALSE,
159 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
160 source_database IN VARCHAR2 DEFAULT NULL,
161 dml_rule_name OUT VARCHAR2,
162 ddl_rule_name OUT VARCHAR2,
163 inclusion_rule IN BOOLEAN DEFAULT TRUE,
164 and_condition IN VARCHAR2 DEFAULT NULL);
165
166 /* Adds capture or apply rules for a schema. */
167 PROCEDURE add_schema_rules(
168 schema_name IN VARCHAR2,
169 streams_type IN VARCHAR2,
170 streams_name IN VARCHAR2 DEFAULT NULL,
171 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
172 include_dml IN BOOLEAN DEFAULT TRUE,
173 include_ddl IN BOOLEAN DEFAULT FALSE,
174 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
175 source_database IN VARCHAR2 DEFAULT NULL,
176 inclusion_rule IN BOOLEAN DEFAULT TRUE,
177 and_condition IN VARCHAR2 DEFAULT NULL);
178
179 /* Adds capture or apply rules for a schema. */
180 PROCEDURE add_schema_rules(
181 schema_name IN VARCHAR2,
182 streams_type IN VARCHAR2,
183 streams_name IN VARCHAR2 DEFAULT NULL,
184 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
185 include_dml IN BOOLEAN DEFAULT TRUE,
186 include_ddl IN BOOLEAN DEFAULT FALSE,
187 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
188 source_database IN VARCHAR2 DEFAULT NULL,
189 dml_rule_name OUT VARCHAR2,
190 ddl_rule_name OUT VARCHAR2,
191 inclusion_rule IN BOOLEAN DEFAULT TRUE,
192 and_condition IN VARCHAR2 DEFAULT NULL);
193
194 -- Adds a capture rule for an entire database or an apply rule
195 -- for an entire queue.
196 -- INPUT:
197 -- streams_type - The type of process: capture, apply or dequeue
198 -- streams_name - The name of the capture or apply process.
199 -- queue_name - The name of the queue. For capture rules, the queue
200 -- into which the changes will be enqueued. For apply rules,
201 -- the queue from which changes will be dequeued.
202 -- include_dml - If TRUE, then creates a rule for DML changes. If FALSE,
203 -- then does not create a DML rule. NULL is not permitted.
204 -- include_ddl - If TRUE, then creates a rule for DDL changes. If FALSE,
205 -- then does not create a DDL rule. NULL is not permitted.
206 -- include_tagged_lcr - If TRUE, then a logical change record is always
207 -- considered for capture or apply, regardless of
208 -- whether it has a non-NULL tag. This setting is
209 -- appropriate for a full (for example, standby) copy
210 -- of a database. If FALSE, then a logical change
211 -- record is considered for capture or apply only when
212 -- it was produced in a session in which its tag is
213 -- NULL. FALSE is often specified in update-anywhere
214 -- configurations to avoid sending a change back to
215 -- its source database.
216 -- source_database - The global name of the source database.
217 -- If NULL, then the global name of the current database
218 -- is used.
219 -- inclusion_rule - If TRUE, then the rule(s) are added to the positive
220 -- rule set for the streams process; if FALSE, then
221 -- the rule(s) are added to the negative rule set.
222 -- and_condition - additional condition to be appended to the generated rule
223 -- with an 'AND'. The variable name for the lcr in the
224 -- condition should be :lcr.
225 PROCEDURE add_global_rules(
226 streams_type IN VARCHAR2,
227 streams_name IN VARCHAR2 DEFAULT null,
228 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
229 include_dml IN BOOLEAN DEFAULT TRUE,
230 include_ddl IN BOOLEAN DEFAULT FALSE,
231 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
232 source_database IN VARCHAR2 DEFAULT NULL,
233 inclusion_rule IN BOOLEAN DEFAULT TRUE,
234 and_condition IN VARCHAR2 DEFAULT NULL);
235
236 /* Adds a capture rule for an entire database or an apply rule
237 * for an entire queue.
238 */
239 PROCEDURE add_global_rules(
240 streams_type IN VARCHAR2,
241 streams_name IN VARCHAR2 DEFAULT NULL,
242 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
243 include_dml IN BOOLEAN DEFAULT TRUE,
244 include_ddl IN BOOLEAN DEFAULT FALSE,
245 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
246 source_database IN VARCHAR2 DEFAULT NULL,
247 dml_rule_name OUT VARCHAR2,
248 ddl_rule_name OUT VARCHAR2,
249 inclusion_rule IN BOOLEAN DEFAULT TRUE,
250 and_condition IN VARCHAR2 DEFAULT NULL);
251
252 -- Removes the specified rule or removes all rules from the rule set
253 -- associated with the specified capture process, apply process,
254 -- propagation or message consumer rule set.
255 -- If the associate streams name no longer exists and rule_name is not null,
256 -- the entry in dba_streams_%_rules for rule_name will be removed and
257 -- ORA-23605 is raised.
258 --
259 -- INPUT:
260 -- rule_name - The name of the rule to remove. If NULL, then removes
261 -- all rules for the specified capture process, apply process,
262 -- or propagation stream rule set.
263 -- streams_type - The type of Streams rule, either capture, apply,
264 -- or propagate
265 -- streams_name - The name of the capture process, apply process,
266 -- or propagation stream
267 -- drop_unused_rule - If FALSE, then the rule is not dropped from
268 -- the database. If TRUE and the rule is not in any
269 -- rule set, then the rule is dropped from the database.
270 -- If TRUE and the rule exists in any rule set,
271 -- then the rule is not dropped from the database.
272 -- inclusion_rule - If TRUE, then the rule is dropped from the positive
273 -- rule set. If FALSE, then the rule is dropped from
274 -- the negative rule set.
275
276 PROCEDURE remove_rule(
277 rule_name IN VARCHAR2,
278 streams_type IN VARCHAR2,
279 streams_name IN VARCHAR2,
280 drop_unused_rule IN BOOLEAN DEFAULT TRUE,
281 inclusion_rule IN BOOLEAN DEFAULT TRUE);
282
283 -- Sets up a queue table and a queue for use with the capture,
284 -- propagate, and apply functionality of Streams.
285 -- The queue functions as a Streams queue.
286 -- INPUT:
287 -- queue_table - The name of the queue table
288 -- storage_clause - The storage clause for queue creation
289 -- queue_name - The name of the queue that will function as
290 -- the Streams queue
291 -- queue_user - The name of the user who requires enqueue and
292 -- dequeue privileges for the queue. If NULL, then no
293 -- privileges are granted. You can grant queue privileges
294 -- to the appropriate users using the DBMS_AQADM package.
295 -- transactional_queue -
296 -- comment -
297
298 PROCEDURE set_up_queue(
299 queue_table IN VARCHAR2 DEFAULT 'streams_queue_table',
300 storage_clause IN VARCHAR2 DEFAULT NULL,
301 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
302 queue_user IN VARCHAR2 DEFAULT NULL,
303 comment IN VARCHAR2 DEFAULT NULL);
304
305
306 -- Adds the destination queue as a subscriber of the source queue,
307 -- if the destination queue is not already subscribed to the source queue.
308 -- This procedure also configures propagation, if necessary, using
309 -- the currently connected user. This procedure also enables propagation
310 -- of messages for the specified table, subject to filtering conditions,
311 -- to the destination queue.
312 -- INPUT:
313 -- table_name - The name of the table specified as
314 -- schema_name.object_name. For example, hr.employees.
315 -- If the schema is not specified, then the current user
316 -- is the default.
317 -- schema_name - The name of the schema. For example, hr.
318 -- streams_name - The name of the propagation stream.
319 -- source_queue_name - The name of the source queue.
320 -- The current database must contain the source queue.
321 -- destination_queue_name - The name of the destination queue,
322 -- including any database link, such as
323 -- STREAMS_QUEUE@DBS2. If the database link
324 -- is omitted, then the global name of the current
325 -- database is used.
326 -- include_dml - If TRUE, then create a rule for DML changes. If FALSE,
327 -- then do not create a DML rule. NULL is not permitted.
328 -- include_ddl - If TRUE, then create a rule for DDL changes. If FALSE,
332 -- always considered for propagation, regardless
329 -- then do not create a DDL rule. NULL is not permitted.
330 --
331 -- include_tagged_lcr - If TRUE, then a logical change record is
333 -- of whether it has a non-NULL tag.
334 -- If FALSE, then a logical change record is considered
335 -- for propagation only when it is produced in a session
336 -- in which its tag is NULL.
337 -- source_database - The global name of the source database.
338 -- If NULL, then the global name of the current
339 -- database is used.
340 -- OUTPUT:
341 -- dml_rule_name - If include_dml is TRUE, then dml_rule_name contains
342 -- the DML rule name. If include_dml is FALSE,
343 -- then dml_rule_name contains a NULL.
344 --
345 -- ddl_rule_name - If include_ddl is TRUE, then ddl_rule_name contains
346 -- the DDL rule name. If include_ddl is FALSE,
347 -- then ddl_rule_name contains a NULL.
348 -- inclusion_rule - If TRUE, then the rule(s) are added to the positive
349 -- rule set for the streams process; if FALSE, then
350 -- the rule(s) are added to the negative rule set.
351 -- and_condition - additional condition to be appended to the generated rule
352 -- with an 'AND'. The variable name for the lcr in the
353 -- condition should be :lcr.
354 PROCEDURE add_table_propagation_rules(
355 table_name IN VARCHAR2,
356 streams_name IN VARCHAR2 DEFAULT NULL,
357 source_queue_name IN VARCHAR2,
358 destination_queue_name IN VARCHAR2,
359 include_dml IN BOOLEAN DEFAULT TRUE,
360 include_ddl IN BOOLEAN DEFAULT FALSE,
361 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
362 source_database IN VARCHAR2 DEFAULT NULL,
363 inclusion_rule IN BOOLEAN DEFAULT TRUE,
364 and_condition IN VARCHAR2 DEFAULT NULL,
365 queue_to_queue IN BOOLEAN DEFAULT NULL);
366
367 PROCEDURE add_table_propagation_rules(
368 table_name IN VARCHAR2,
369 streams_name IN VARCHAR2 DEFAULT NULL,
370 source_queue_name IN VARCHAR2,
371 destination_queue_name IN VARCHAR2,
372 include_dml IN BOOLEAN DEFAULT TRUE,
373 include_ddl IN BOOLEAN DEFAULT FALSE,
374 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
375 source_database IN VARCHAR2 DEFAULT NULL,
376 dml_rule_name OUT VARCHAR2,
377 ddl_rule_name OUT VARCHAR2,
378 inclusion_rule IN BOOLEAN DEFAULT TRUE,
379 and_condition IN VARCHAR2 DEFAULT NULL,
380 queue_to_queue IN BOOLEAN DEFAULT NULL);
381
382 PROCEDURE add_schema_propagation_rules(
383 schema_name IN VARCHAR2,
384 streams_name IN VARCHAR2 DEFAULT NULL,
385 source_queue_name IN VARCHAR2,
386 destination_queue_name IN VARCHAR2,
387 include_dml IN BOOLEAN DEFAULT TRUE,
388 include_ddl IN BOOLEAN DEFAULT FALSE,
389 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
390 source_database IN VARCHAR2 DEFAULT NULL,
391 inclusion_rule IN BOOLEAN DEFAULT TRUE,
392 and_condition IN VARCHAR2 DEFAULT NULL,
393 queue_to_queue IN BOOLEAN DEFAULT NULL);
394
395 PROCEDURE add_schema_propagation_rules(
396 schema_name IN VARCHAR2,
397 streams_name IN VARCHAR2 DEFAULT NULL,
398 source_queue_name IN VARCHAR2,
399 destination_queue_name IN VARCHAR2,
400 include_dml IN BOOLEAN DEFAULT TRUE,
401 include_ddl IN BOOLEAN DEFAULT FALSE,
402 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
403 source_database IN VARCHAR2 DEFAULT NULL,
404 dml_rule_name OUT VARCHAR2,
405 ddl_rule_name OUT VARCHAR2,
406 inclusion_rule IN BOOLEAN DEFAULT TRUE,
407 and_condition IN VARCHAR2 DEFAULT NULL,
408 queue_to_queue IN BOOLEAN DEFAULT NULL);
409
410 PROCEDURE add_global_propagation_rules(
411 streams_name IN VARCHAR2 DEFAULT NULL,
412 source_queue_name IN VARCHAR2,
413 destination_queue_name IN VARCHAR2,
414 include_dml IN BOOLEAN DEFAULT TRUE,
415 include_ddl IN BOOLEAN DEFAULT FALSE,
416 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
417 source_database IN VARCHAR2 DEFAULT NULL,
418 inclusion_rule IN BOOLEAN DEFAULT TRUE,
419 and_condition IN VARCHAR2 DEFAULT NULL,
420 queue_to_queue IN BOOLEAN DEFAULT NULL);
421
422 PROCEDURE add_global_propagation_rules(
423 streams_name IN VARCHAR2 DEFAULT NULL,
424 source_queue_name IN VARCHAR2,
425 destination_queue_name IN VARCHAR2,
426 include_dml IN BOOLEAN DEFAULT TRUE,
427 include_ddl IN BOOLEAN DEFAULT FALSE,
428 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
429 source_database IN VARCHAR2 DEFAULT NULL,
430 dml_rule_name OUT VARCHAR2,
431 ddl_rule_name OUT VARCHAR2,
432 inclusion_rule IN BOOLEAN DEFAULT TRUE,
433 and_condition IN VARCHAR2 DEFAULT NULL,
434 queue_to_queue IN BOOLEAN DEFAULT NULL);
435
436 PROCEDURE purge_source_catalog(
437 source_database IN VARCHAR2,
438 source_object_name IN VARCHAR2,
439 source_object_type IN VARCHAR2);
440
444 -- table_name - The name of the table the subset rule is specified for.
441 -- Adds subset rules for the given dml condition on the specified table.
442 -- Subset rules are used for row migration.
443 -- INPUT:
445 -- dml_condition - The dml condition used for subsetting.
446 -- streams_type - The type of process: capture, apply or dequeue
447 -- streams_name - The name of the capture or apply process.
448 -- queue_name - The name of the queue. For capture rules, the queue
449 -- into which the changes will be enqueued. For apply rules,
450 -- the queue from which changes will be dequeued.
451 -- include_tagged_lcr - If TRUE, then a logical change record is always
452 -- considered for capture or apply, regardless of
453 -- whether it has a non-NULL tag. This setting is
454 -- appropriate for a full (for example, standby) copy
455 -- of a database. If FALSE, then a logical change
456 -- record is considered for capture or apply only when
457 -- it was produced in a session in which its tag is
458 -- NULL. FALSE is often specified in update-anywhere
459 -- configurations to avoid sending a change back to
460 -- its source database.
461 -- source_database - The global name of the source database.
462 -- If NULL, then the global name of the current database
463 -- is used.
464 -- OUTPUT:
465 -- insert_rule_name - The name of the subset rule generated for insert DMLs
466 -- satisfying the specified dml condition.
467 -- update_rule_name - The name of the subset rule generated for update DMLs
468 -- satisfying the specified dml condition.
469 -- delete_rule_name - The name of the subset rule generated for delete DMLs
470 -- satisfying the specified dml condition.
471
472 PROCEDURE add_subset_rules(
473 table_name IN VARCHAR2,
474 dml_condition IN VARCHAR2,
475 streams_type IN VARCHAR2 DEFAULT 'APPLY',
476 streams_name IN VARCHAR2 DEFAULT NULL,
477 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
478 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
479 source_database IN VARCHAR2 DEFAULT NULL);
480
481 PROCEDURE add_subset_rules(
482 table_name IN VARCHAR2,
483 dml_condition IN VARCHAR2,
484 streams_type IN VARCHAR2 DEFAULT 'APPLY',
485 streams_name IN VARCHAR2 DEFAULT NULL,
486 queue_name IN VARCHAR2 DEFAULT 'streams_queue',
487 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
488 source_database IN VARCHAR2 DEFAULT NULL,
489 insert_rule_name OUT VARCHAR2,
490 update_rule_name OUT VARCHAR2,
491 delete_rule_name OUT VARCHAR2);
492
493 -- Adds propagation subset rules for the given dml condition on the
494 -- specified table. Subset rules are used for row migration.
495 -- INPUT:
496 -- table_name - The name of the table the subset rule is specified for.
497 -- dml_condition - The dml condition used for subsetting.
498 -- streams_name - The name of the capture or apply process.
499 -- source_queue_name - The name of the source queue.
500 -- destination_queue_name - The name of the destination queue.
501 -- include_tagged_lcr - If TRUE, then a logical change record is always
502 -- considered for propagation, regardless of
503 -- whether it has a non-NULL tag. This setting is
504 -- appropriate for a full (for example, standby) copy
505 -- of a database. If FALSE, then a logical change
506 -- record is considered for propagation only when
507 -- it was produced in a session in which its tag is
508 -- NULL. FALSE is often specified in update-anywhere
509 -- configurations to avoid sending a change back to
510 -- its source database.
511 -- source_database - The global name of the source database.
512 -- If NULL, then the global name of the current database
513 -- is used.
514 -- OUTPUT:
515 -- insert_rule_name - The name of the subset rule generated for insert DMLs
516 -- satisfying the specified dml condition.
517 -- update_rule_name - The name of the subset rule generated for update DMLs
518 -- satisfying the specified dml condition.
519 -- delete_rule_name - The name of the subset rule generated for delete DMLs
520 -- satisfying the specified dml condition.
521
522 PROCEDURE add_subset_propagation_rules(
523 table_name IN VARCHAR2,
524 dml_condition IN VARCHAR2,
525 streams_name IN VARCHAR2 DEFAULT NULL,
526 source_queue_name IN VARCHAR2,
527 destination_queue_name IN VARCHAR2,
528 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
529 source_database IN VARCHAR2 DEFAULT NULL,
530 queue_to_queue IN BOOLEAN DEFAULT NULL);
531
532 PROCEDURE add_subset_propagation_rules(
533 table_name IN VARCHAR2,
534 dml_condition IN VARCHAR2,
535 streams_name IN VARCHAR2 DEFAULT NULL,
536 source_queue_name IN VARCHAR2,
537 destination_queue_name IN VARCHAR2,
538 include_tagged_lcr IN BOOLEAN DEFAULT FALSE,
542 delete_rule_name OUT VARCHAR2,
539 source_database IN VARCHAR2 DEFAULT NULL,
540 insert_rule_name OUT VARCHAR2,
541 update_rule_name OUT VARCHAR2,
543 queue_to_queue IN BOOLEAN DEFAULT NULL);
544
545 -- Sets the Streams user-defined transformation for the given rule.
546 -- rule_name: the name of the rule to set the transformation for.
547 -- transform_function: the name of the user-defined transformation.
548 -- This should be the fully qualifed name: <schema>.<package>.<procedure>.
549 -- If this parameter is NULL, then any existing tranformation is deleted.
550 PROCEDURE set_rule_transform_function(
551 rule_name IN VARCHAR2,
552 transform_function IN VARCHAR2);
553
554 -- Removes any existing streams configuration. This means that:
555 -- Any capture/propagation/apply processes will be dropped, and
556 -- Any apply errors will be deleted, and
557 -- Any streams rules (rules created implicitly by the API's in package
558 -- dbms_streams_adm) will be removed and dropped, and
559 -- Any instantiation scn's for tables/schemas/database will be removed, and
560 -- If instantiation was prepared on any tables/schemas/database, then it
561 -- will be aborted, and
562 -- No queues will be dropped.
563 -- INPUT:
564 -- Nothing.
565 -- OUTPUT:
566 -- Nothing.
567 PROCEDURE remove_streams_configuration(
568 streams_only IN BOOLEAN DEFAULT TRUE);
569
570 PROCEDURE set_message_notification(
571 streams_name IN VARCHAR2,
572 notification_action IN VARCHAR2,
573 notification_type IN VARCHAR2 DEFAULT 'PROCEDURE',
574 notification_context IN SYS.ANYDATA DEFAULT NULL,
575 include_notification IN BOOLEAN DEFAULT TRUE,
576 queue_name IN VARCHAR2 DEFAULT 'streams_queue');
577
578 -- streams_name : The name specified in add_message_rule for DEQUEUE
579 -- queue_name : The name of the queue
580 -- notification_action : the action of the notification, e.g., URL without
581 -- 'HTTP://', email address, PL/SQL procedure.
582 -- notificaiton_type : one of 'PROCEDURE', 'HTTP', 'MAIL'
583 -- notification_context : the context of the notification.
584 -- include_notification : If TRUE, this notification is added for the given
585 -- streams_name and queue_name. If FALSE, this notification is removed for
586 -- the given streams_name and queue_name.
587
588 -- Removes a queue from use in Streams. Specifically, the queue will be
589 -- stopped, and no further enqueue or dequeues will be allowed on the queue.
590 -- INPUT:
591 -- queue_name - The name of the queue to be removed.
592 -- cascade - If TRUE, will remove the all associated streams
593 -- components in addition to the queue
594 -- If FALSE, will raise an error if any associated
595 -- streams components are found
596 -- drop_unused_queue_table - If TRUE, the queue table that stores
597 -- information about this queue is dropped if
598 -- it is empty.
599 -- If FALSE, the queue table is unchanged.
600 -- OUTPUT:
601 -- Nothing.
602 PROCEDURE remove_queue(
603 queue_name IN VARCHAR2,
604 cascade IN BOOLEAN DEFAULT FALSE,
605 drop_unused_queue_table IN BOOLEAN DEFAULT TRUE);
606
607
608 -- The maintain_tablespaces API does automatic tablespace transport and
609 -- incrementally maintains the transported tablespaces using Streams.
610 -- It optionally generates a script to do this which can be edited and
611 -- executed by the user.
612 -- Note this API has been deprecated, please use maintain_tts instead.
613 -- The incremental maintenace is performed as follows:
614 -- At the source database:
615 -- - Mark all the tablespaces in the supplied list of tablespaces as read
616 -- only.
617 -- - Clone the tablespaces using
618 -- dbms_streams_tablespace_adm.clone_tablespaces and place the tablespace
619 -- set in the source directory.
620 -- - Add supplemental log groups for the tables in the tablespaces if
621 -- necessary.
622 -- - Setup a Streams queue at the source.
623 -- - Create a capture process and add all supported tables in the
624 -- tablespaces to the capture rules.
625 -- - Create propagation, add all supported tables to the propagation rules
626 -- and disable propagation.
627 -- - Save the value of current scn as the instantiation scn for the
628 -- destination database apply setup.
629 -- - Startup capture process.
630 -- - Restore read-write status of tablespaces. DML operations can now
631 -- resume in the tablespaces.
632 -- - Move the tablespace set to the destination directory using
633 -- dbms_file_transfer.
634 -- At the destination database:
635 -- - Attach the tablespaces using
636 -- dbms_streams_tablespace_adm.attach_tablespaces.
637 -- - Set key columns for all the tables in the tablespaces if necessary.
638 -- - Setup Streams queue.
639 -- - Create an apply process and add all supported tables to the apply
640 -- rules.
641 -- - Set instantiation scn obtained from the source database for the each
642 -- table.
643 -- - Startup apply process.
644 -- - Mark tablespaces as read-write.
645 -- At the source database:
646 -- - Enable propagation schedule to the destination database.
647 -- INPUT:
648 -- tablespace_names : list of self-contained tablespaces
652 -- cloned tablespace set will be moved
649 -- source_directory_object : location where cloned tablespace set
650 -- will be placed at the source
651 -- destination_directory_object : directory at destination database where the
653 -- destination_database : destination database
654 -- setup_streams : If FALSE, only generate a script
655 -- script_name : name of the generated script
656 -- script_directory_object : generated script is placed here
657 -- dump_file_name : name of the file produced by datapump
658 -- export of the tablespace metadata
659 -- source_queue_table : queue table at source
660 -- source_queue_name : queue at source
661 -- source_queue_user : source queue user
662 -- destination_queue_table : queue table at destination
663 -- destination_queue_name : queue at destination
664 -- destination_queue_user : destination queue user
665 -- capture_name : name of the capture process
666 -- propagation_name : propagation name
667 -- apply_name : apply process name
668 -- log_file : name of the log file generated during
669 -- datapump export/import
670 -- bi_directional : If TRUE setup bi-directional information
671 -- sharing. Else, only uni-directional
672 -- sharing is setup.
673
674 PROCEDURE maintain_tablespaces(
675 tablespace_names IN dbms_streams_tablespace_adm.tablespace_set,
676 source_directory_object IN VARCHAR2,
677 destination_directory_object IN VARCHAR2,
678 destination_database IN VARCHAR2,
679 setup_streams IN BOOLEAN DEFAULT TRUE,
680 script_name IN VARCHAR2 DEFAULT NULL,
681 script_directory_object IN VARCHAR2 DEFAULT NULL,
682 dump_file_name IN VARCHAR2 DEFAULT NULL,
683 source_queue_table IN VARCHAR2 DEFAULT 'streams_queue_table',
684 source_queue_name IN VARCHAR2 DEFAULT 'streams_queue',
685 source_queue_user IN VARCHAR2 DEFAULT NULL,
686 destination_queue_table IN VARCHAR2 DEFAULT 'streams_queue_table',
687 destination_queue_name IN VARCHAR2 DEFAULT 'streams_queue',
688 destination_queue_user IN VARCHAR2 DEFAULT NULL,
689 capture_name IN VARCHAR2 DEFAULT 'capture',
690 propagation_name IN VARCHAR2 DEFAULT NULL,
691 apply_name IN VARCHAR2 DEFAULT NULL,
692 log_file IN VARCHAR2 DEFAULT NULL,
693 bi_directional IN BOOLEAN DEFAULT FALSE,
694 include_ddl IN BOOLEAN DEFAULT FALSE);
695
696 -- The maintain_tts API is similar to maintain_tablespaces except for
697 -- the following parameters:
698 -- source_database : the source database name.
699 -- If NULL, will default to the current
700 -- database name.
701 -- If non-NULL, and different from the actual
702 -- source database name, will setup
703 -- downstream capture on the specified
704 -- database.
705 -- perform_actions : If FALSE, only generate a script
706 -- capture_queue_table : queue table name for capture processes
707 -- if NULL, name will be generated
708 -- capture_queue_name : queue name for capture processes
709 -- if NULL, name will be generated
710 -- capture_queue_user : queue user for capture processes
711 -- if NULL, name will be generated
712 -- apply_queue_table : queue table for apply processes
713 -- if NULL, name will be generated
714 -- apply_queue_name : queue name for apply processes
715 -- if NULL, name will be generated
716 -- apply_queue_user : queue user for apply processes
717 -- if NULL, name will be generated
718 -- capture_name : name of the capture process
719 -- if NULL, name will be generated
720 -- For a bi-directional setup, two queues will be created on each database.
721
722 PROCEDURE maintain_tts(
723 tablespace_names IN dbms_streams_tablespace_adm.tablespace_set,
724 source_directory_object IN VARCHAR2,
725 destination_directory_object IN VARCHAR2,
726 source_database IN VARCHAR2,
727 destination_database IN VARCHAR2,
728 perform_actions IN BOOLEAN DEFAULT TRUE,
729 script_name IN VARCHAR2 DEFAULT NULL,
730 script_directory_object IN VARCHAR2 DEFAULT NULL,
731 dump_file_name IN VARCHAR2 DEFAULT NULL,
732 capture_name IN VARCHAR2 DEFAULT NULL,
733 capture_queue_table IN VARCHAR2 DEFAULT NULL,
734 capture_queue_name IN VARCHAR2 DEFAULT NULL,
735 capture_queue_user IN VARCHAR2 DEFAULT NULL,
736 propagation_name IN VARCHAR2 DEFAULT NULL,
737 apply_name IN VARCHAR2 DEFAULT NULL,
738 apply_queue_table IN VARCHAR2 DEFAULT NULL,
739 apply_queue_name IN VARCHAR2 DEFAULT NULL,
740 apply_queue_user IN VARCHAR2 DEFAULT NULL,
744
741 log_file IN VARCHAR2 DEFAULT NULL,
742 bi_directional IN BOOLEAN DEFAULT FALSE,
743 include_ddl IN BOOLEAN DEFAULT FALSE);
745 -- The maintain_simple_tablespace API does automatic tablespace transport and
746 -- incrementally maintains a single self contained tablespace.
747 -- It optionally generates a script which can be edited and executed by
748 -- the user.
749 -- Note this API has been deprecated, please use maintain_simple_tts instead.
750 -- Functionality is similar to maintain_tablespaces except that this
751 -- can be used only for a single self-contained tablespace. All the Streams
752 -- processes and objects will have the same names as the corresponding
753 -- defaults in the maintain_tablespaces API.
754 PROCEDURE maintain_simple_tablespace(
755 tablespace_name IN VARCHAR2,
756 source_directory_object IN VARCHAR2,
757 destination_directory_object IN VARCHAR2,
758 destination_database IN VARCHAR2,
759 setup_streams IN BOOLEAN DEFAULT TRUE,
760 script_name IN VARCHAR2 DEFAULT NULL,
761 script_directory_object IN VARCHAR2 DEFAULT NULL,
762 bi_directional IN BOOLEAN DEFAULT FALSE);
763
764 -- The maintain_simple_tts API is similar to maintain_simple_tablespace
765 -- except for the following parameters:
766 -- source_database : the source database name
767 -- If NULL, will default to the current
768 -- database name.
769 -- If non-NULL, and different from the actual
770 -- source database name, will setup
771 -- downstream capture on the specified
772 -- database.
773 -- perform_actions : If FALSE, only generate a script
774 -- For a bi-directional setup, two queues will be created on each database.
775 PROCEDURE maintain_simple_tts(
776 tablespace_name IN VARCHAR2,
777 source_directory_object IN VARCHAR2,
778 destination_directory_object IN VARCHAR2,
779 source_database IN VARCHAR2,
780 destination_database IN VARCHAR2,
781 perform_actions IN BOOLEAN DEFAULT TRUE,
782 script_name IN VARCHAR2 DEFAULT NULL,
783 script_directory_object IN VARCHAR2 DEFAULT NULL,
784 bi_directional IN BOOLEAN DEFAULT FALSE);
785
786 -- Parameters with same name as maintain_tts have the same meaning.
787 -- Other parameters:
788 -- table_names: names of tables with optional schema name. If schema name is
789 -- not specified, the invoker is used.
790 -- One version is a PL/SQL table and another one accepts a
791 -- comma-separated list.
792 PROCEDURE maintain_tables(
793 table_names IN dbms_utility.uncl_array,
794 source_directory_object IN VARCHAR2,
795 destination_directory_object IN VARCHAR2,
796 source_database IN VARCHAR2,
797 destination_database IN VARCHAR2,
798 perform_actions IN BOOLEAN DEFAULT TRUE,
799 script_name IN VARCHAR2 DEFAULT NULL,
800 script_directory_object IN VARCHAR2 DEFAULT NULL,
801 dump_file_name IN VARCHAR2 DEFAULT NULL,
802 capture_name IN VARCHAR2 DEFAULT NULL,
803 capture_queue_table IN VARCHAR2 DEFAULT NULL,
804 capture_queue_name IN VARCHAR2 DEFAULT NULL,
805 capture_queue_user IN VARCHAR2 DEFAULT NULL,
806 propagation_name IN VARCHAR2 DEFAULT NULL,
807 apply_name IN VARCHAR2 DEFAULT NULL,
808 apply_queue_table IN VARCHAR2 DEFAULT NULL,
809 apply_queue_name IN VARCHAR2 DEFAULT NULL,
810 apply_queue_user IN VARCHAR2 DEFAULT NULL,
811 log_file IN VARCHAR2 DEFAULT NULL,
812 bi_directional IN BOOLEAN DEFAULT FALSE,
813 include_ddl IN BOOLEAN DEFAULT FALSE,
814 instantiation IN BINARY_INTEGER DEFAULT
815 dbms_streams_adm.instantiation_table);
816
817 PROCEDURE maintain_tables(
818 table_names IN VARCHAR2,
819 source_directory_object IN VARCHAR2,
820 destination_directory_object IN VARCHAR2,
821 source_database IN VARCHAR2,
822 destination_database IN VARCHAR2,
823 perform_actions IN BOOLEAN DEFAULT TRUE,
824 script_name IN VARCHAR2 DEFAULT NULL,
825 script_directory_object IN VARCHAR2 DEFAULT NULL,
826 dump_file_name IN VARCHAR2 DEFAULT NULL,
827 capture_name IN VARCHAR2 DEFAULT NULL,
828 capture_queue_table IN VARCHAR2 DEFAULT NULL,
829 capture_queue_name IN VARCHAR2 DEFAULT NULL,
830 capture_queue_user IN VARCHAR2 DEFAULT NULL,
831 propagation_name IN VARCHAR2 DEFAULT NULL,
832 apply_name IN VARCHAR2 DEFAULT NULL,
833 apply_queue_table IN VARCHAR2 DEFAULT NULL,
834 apply_queue_name IN VARCHAR2 DEFAULT NULL,
835 apply_queue_user IN VARCHAR2 DEFAULT NULL,
836 log_file IN VARCHAR2 DEFAULT NULL,
837 bi_directional IN BOOLEAN DEFAULT FALSE,
838 include_ddl IN BOOLEAN DEFAULT FALSE,
839 instantiation IN BINARY_INTEGER DEFAULT
843 -- Other parameters:
840 dbms_streams_adm.instantiation_table);
841
842 -- Parameters with same name as maintain_tts have the same meaning.
844 -- schema_names: names of schemas.
845 -- One version is a PL/SQL table and another one accepts a
846 -- comma-separated list.
847 PROCEDURE maintain_schemas(
848 schema_names IN dbms_utility.uncl_array,
849 source_directory_object IN VARCHAR2,
850 destination_directory_object IN VARCHAR2,
851 source_database IN VARCHAR2,
852 destination_database IN VARCHAR2,
853 perform_actions IN BOOLEAN DEFAULT TRUE,
854 script_name IN VARCHAR2 DEFAULT NULL,
855 script_directory_object IN VARCHAR2 DEFAULT NULL,
856 dump_file_name IN VARCHAR2 DEFAULT NULL,
857 capture_name IN VARCHAR2 DEFAULT NULL,
858 capture_queue_table IN VARCHAR2 DEFAULT NULL,
859 capture_queue_name IN VARCHAR2 DEFAULT NULL,
860 capture_queue_user IN VARCHAR2 DEFAULT NULL,
861 propagation_name IN VARCHAR2 DEFAULT NULL,
862 apply_name IN VARCHAR2 DEFAULT NULL,
863 apply_queue_table IN VARCHAR2 DEFAULT NULL,
864 apply_queue_name IN VARCHAR2 DEFAULT NULL,
865 apply_queue_user IN VARCHAR2 DEFAULT NULL,
866 log_file IN VARCHAR2 DEFAULT NULL,
867 bi_directional IN BOOLEAN DEFAULT FALSE,
868 include_ddl IN BOOLEAN DEFAULT FALSE,
869 instantiation IN BINARY_INTEGER DEFAULT
870 dbms_streams_adm.instantiation_schema);
871
872 PROCEDURE maintain_schemas(
873 schema_names IN VARCHAR2,
874 source_directory_object IN VARCHAR2,
875 destination_directory_object IN VARCHAR2,
876 source_database IN VARCHAR2,
877 destination_database IN VARCHAR2,
878 perform_actions IN BOOLEAN DEFAULT TRUE,
879 script_name IN VARCHAR2 DEFAULT NULL,
880 script_directory_object IN VARCHAR2 DEFAULT NULL,
881 dump_file_name IN VARCHAR2 DEFAULT NULL,
882 capture_name IN VARCHAR2 DEFAULT NULL,
883 capture_queue_table IN VARCHAR2 DEFAULT NULL,
884 capture_queue_name IN VARCHAR2 DEFAULT NULL,
885 capture_queue_user IN VARCHAR2 DEFAULT NULL,
886 propagation_name IN VARCHAR2 DEFAULT NULL,
887 apply_name IN VARCHAR2 DEFAULT NULL,
888 apply_queue_table IN VARCHAR2 DEFAULT NULL,
889 apply_queue_name IN VARCHAR2 DEFAULT NULL,
890 apply_queue_user IN VARCHAR2 DEFAULT NULL,
891 log_file IN VARCHAR2 DEFAULT NULL,
892 bi_directional IN BOOLEAN DEFAULT FALSE,
893 include_ddl IN BOOLEAN DEFAULT FALSE,
894 instantiation IN BINARY_INTEGER DEFAULT
895 dbms_streams_adm.instantiation_schema);
896
897 PROCEDURE maintain_global(
898 source_directory_object IN VARCHAR2,
899 destination_directory_object IN VARCHAR2,
900 source_database IN VARCHAR2,
901 destination_database IN VARCHAR2,
902 perform_actions IN BOOLEAN DEFAULT TRUE,
903 script_name IN VARCHAR2 DEFAULT NULL,
904 script_directory_object IN VARCHAR2 DEFAULT NULL,
905 dump_file_name IN VARCHAR2 DEFAULT NULL,
906 capture_name IN VARCHAR2 DEFAULT NULL,
907 capture_queue_table IN VARCHAR2 DEFAULT NULL,
908 capture_queue_name IN VARCHAR2 DEFAULT NULL,
909 capture_queue_user IN VARCHAR2 DEFAULT NULL,
910 propagation_name IN VARCHAR2 DEFAULT NULL,
911 apply_name IN VARCHAR2 DEFAULT NULL,
912 apply_queue_table IN VARCHAR2 DEFAULT NULL,
913 apply_queue_name IN VARCHAR2 DEFAULT NULL,
914 apply_queue_user IN VARCHAR2 DEFAULT NULL,
915 log_file IN VARCHAR2 DEFAULT NULL,
916 bi_directional IN BOOLEAN DEFAULT FALSE,
917 include_ddl IN BOOLEAN DEFAULT FALSE,
918 instantiation IN BINARY_INTEGER DEFAULT
919 dbms_streams_adm.instantiation_full);
920 PROCEDURE pre_instantiation_setup(
921 maintain_mode IN VARCHAR2,
922 tablespace_names IN dbms_streams_tablespace_adm.tablespace_set,
923 source_database IN VARCHAR2,
924 destination_database IN VARCHAR2,
925 perform_actions IN BOOLEAN DEFAULT TRUE,
926 script_name IN VARCHAR2 DEFAULT NULL,
927 script_directory_object IN VARCHAR2 DEFAULT NULL,
928 capture_name IN VARCHAR2 DEFAULT NULL,
929 capture_queue_table IN VARCHAR2 DEFAULT NULL,
930 capture_queue_name IN VARCHAR2 DEFAULT NULL,
931 capture_queue_user IN VARCHAR2 DEFAULT NULL,
932 propagation_name IN VARCHAR2 DEFAULT NULL,
933 apply_name IN VARCHAR2 DEFAULT NULL,
934 apply_queue_table IN VARCHAR2 DEFAULT NULL,
935 apply_queue_name IN VARCHAR2 DEFAULT NULL,
936 apply_queue_user IN VARCHAR2 DEFAULT NULL,
937 bi_directional IN BOOLEAN DEFAULT FALSE,
941 exclude_flags IN BINARY_INTEGER DEFAULT NULL);
938 include_ddl IN BOOLEAN DEFAULT FALSE,
939 start_processes IN BOOLEAN DEFAULT FALSE,
940 exclude_schemas IN VARCHAR2 DEFAULT NULL,
942
943 PROCEDURE post_instantiation_setup(
944 maintain_mode IN VARCHAR2,
945 tablespace_names IN dbms_streams_tablespace_adm.tablespace_set,
946 source_database IN VARCHAR2,
947 destination_database IN VARCHAR2,
948 perform_actions IN BOOLEAN DEFAULT TRUE,
949 script_name IN VARCHAR2 DEFAULT NULL,
950 script_directory_object IN VARCHAR2 DEFAULT NULL,
951 capture_name IN VARCHAR2 DEFAULT NULL,
952 capture_queue_table IN VARCHAR2 DEFAULT NULL,
953 capture_queue_name IN VARCHAR2 DEFAULT NULL,
954 capture_queue_user IN VARCHAR2 DEFAULT NULL,
955 propagation_name IN VARCHAR2 DEFAULT NULL,
956 apply_name IN VARCHAR2 DEFAULT NULL,
957 apply_queue_table IN VARCHAR2 DEFAULT NULL,
958 apply_queue_name IN VARCHAR2 DEFAULT NULL,
959 apply_queue_user IN VARCHAR2 DEFAULT NULL,
960 bi_directional IN BOOLEAN DEFAULT FALSE,
961 include_ddl IN BOOLEAN DEFAULT FALSE,
962 start_processes IN BOOLEAN DEFAULT FALSE,
963 instantiation_scn IN NUMBER DEFAULT NULL,
964 exclude_schemas IN VARCHAR2 DEFAULT NULL,
965 exclude_flags IN BINARY_INTEGER DEFAULT NULL);
966
967 PROCEDURE cleanup_instantiation_setup(
968 maintain_mode IN VARCHAR2,
969 tablespace_names IN dbms_streams_tablespace_adm.tablespace_set,
970 source_database IN VARCHAR2,
971 destination_database IN VARCHAR2,
972 perform_actions IN BOOLEAN DEFAULT TRUE,
973 script_name IN VARCHAR2 DEFAULT NULL,
974 script_directory_object IN VARCHAR2 DEFAULT NULL,
975 capture_name IN VARCHAR2 DEFAULT NULL,
976 capture_queue_table IN VARCHAR2 DEFAULT NULL,
977 capture_queue_name IN VARCHAR2 DEFAULT NULL,
978 capture_queue_user IN VARCHAR2 DEFAULT NULL,
979 propagation_name IN VARCHAR2 DEFAULT NULL,
980 apply_name IN VARCHAR2 DEFAULT NULL,
981 apply_queue_table IN VARCHAR2 DEFAULT NULL,
982 apply_queue_name IN VARCHAR2 DEFAULT NULL,
983 apply_queue_user IN VARCHAR2 DEFAULT NULL,
984 bi_directional IN BOOLEAN DEFAULT FALSE,
985 change_global_name IN BOOLEAN DEFAULT FALSE);
986
987 -- This procedure provides the user the option to either roll FORWARD
988 -- the operation, ROLLBACK the operation or PURGE the metadata for the
989 -- operation.
990 -- Input parameters:
991 -- script_id : operation id of the API invocation.
992 -- can be obtained from dba_recoverable_script_* views
993 -- operation_mode : this can have the following values:
994 -- 'FORWARD' : roll forward (default mode)
995 -- 'ROLLBACK' : rollback all the operations performed so far.
996 -- 'PURGE' : purge all metadata without rolling back.
997 PROCEDURE recover_operation(
998 script_id IN RAW,
999 operation_mode IN VARCHAR2 DEFAULT 'FORWARD');
1000
1001
1002 -- For point in time recovery, given the SCN at the source, returns
1003 -- the instantiation SCN and start SCN from the destination. These
1004 -- SCNs can be used to configure the capture and apply processes
1005 -- used for recovery.
1006 -- The procedure will also return a list of transactions to skip
1007 -- (transactions applied at the source earlier than src_pit_scn,
1008 -- but which were applied out of order, after
1009 -- dest_instantiation_scn, at the destination).
1010 -- The caller of this procedure must have EXECUTE privileges for
1011 -- DBMS_FLASHBACK. The log files containing the applied changes
1012 -- lost at the source must exist.
1013 --
1014 -- Parameters:
1015 -- apply_name: name of the apply process which applies LCRs
1016 -- from the source database being recovered.
1017 -- src_pit_scn: the point in time recovery SCN at the source
1018 -- database.
1019 -- dest_instantiation_scn: SCN to set the instantiation SCNs to at the
1020 -- source during recovery.
1021 -- dest_start_scn: SCN to use for the start_scn parameter for
1022 -- recovery capture process.
1023 -- dest_skip_txn_ids: transactions ids which should be ignored by
1024 -- the recovery apply process. These transactions
1025 -- were applied out of order.
1026 PROCEDURE get_scn_mapping(
1027 apply_name IN VARCHAR2,
1028 src_pit_scn IN NUMBER,
1029 dest_instantiation_scn OUT NUMBER,
1030 dest_start_scn OUT NUMBER,
1031 dest_skip_txn_ids OUT DBMS_UTILITY.NAME_ARRAY);
1032
1033 -- Specifies for the given rule that all lcrs with the specified
1034 -- schema will have their schema name renamed.
1035 --
1036 -- Parameters:
1037 -- rule_name: The name of the rule to add this
1038 -- functionality to.
1039 -- from_schema_name: The schema to rename.
1040 -- to_schema_name: The new schema name.
1044 PROCEDURE rename_schema(
1041 -- step_number: The order relative to other transformations.
1042 -- operation: Specify 'ADD' to add this
1043 -- transformation, or 'REMOVE' to remove it.
1045 rule_name IN VARCHAR2,
1046 from_schema_name IN VARCHAR2,
1047 to_schema_name IN VARCHAR2,
1048 step_number IN NUMBER DEFAULT 0,
1049 operation IN VARCHAR2 DEFAULT 'ADD');
1050
1051
1052 -- Specifies for the given rule that all lcrs with the specified
1053 -- schema will have their table name renamed.
1054 --
1055 -- Parameters:
1056 -- rule_name: The name of the rule to add this
1057 -- functionality to.
1058 -- from_table_name: The fully specified (SCHEMA.TABLE)
1059 -- table to be renamed. If the schema is
1060 -- not specified, the invoker of the api
1061 -- will be used.
1062 -- to_table_name: The fully specified (SCHEMA.TABLE) new
1063 -- table name. If the schema is not
1064 -- specified, the invoker of the api will
1065 -- be used.
1066 -- step_number: The order relative to other transformations.
1067 -- operation: Specify 'ADD' to add this
1068 -- transformation, or 'REMOVE' to remove it.
1069 PROCEDURE rename_table(
1070 rule_name IN VARCHAR2,
1071 from_table_name IN VARCHAR2,
1072 to_table_name IN VARCHAR2,
1073 step_number IN NUMBER DEFAULT 0,
1074 operation IN VARCHAR2 DEFAULT 'ADD');
1075
1076 -- Specifies for the given rule that all lcrs with the specified
1077 -- schema and table will have a column deleted.
1078 --
1079 -- Parameters:
1080 -- rule name: The name of the rule to add this functionality to.
1081 -- table_name: The fully-qualified table name whose columns are to be
1082 -- dropped. If the schema is not specified, the
1083 -- invoker of the api will be used.
1084 -- column_name: The column to drop delete.
1085 -- value_type: Whether to drop the old, new, or both columns
1086 -- in the lcr. Specify 'old', 'new', or '*'.
1087 -- step_number: The order relative to other transformations.
1088 -- operation: Specify 'ADD' to add this transformation, or
1089 -- 'REMOVE' to remove it.
1090 PROCEDURE delete_column(
1091 rule_name IN VARCHAR2,
1092 table_name IN VARCHAR2,
1093 column_name IN VARCHAR2,
1094 value_type IN VARCHAR2 DEFAULT '*',
1095 step_number IN NUMBER DEFAULT 0,
1096 operation IN VARCHAR2 DEFAULT 'ADD');
1097
1098 -- Specifies for the given rule that all lcrs with the specified
1099 -- schema and table will have a list of columns kept.
1100 --
1101 -- Parameters:
1102 -- rule name: The name of the rule to add this functionality to.
1103 -- table_name: The fully-qualified table name whose columns are to be
1104 -- kept. If the schema is not specified, the
1105 -- invoker of the api will be used.
1106 -- column_table: The list of columns to keep.
1107 -- value_type: Whether to keep the old, new, or both columns
1108 -- in the lcr. Specify 'old', 'new', or '*'.
1109 -- step_number: The order relative to other transformations.
1110 -- operation: Specify 'ADD' to add this transformation, or
1111 -- 'REMOVE' to remove it.
1112 PROCEDURE keep_columns(
1113 rule_name IN VARCHAR2,
1114 table_name IN VARCHAR2,
1115 column_table IN DBMS_UTILITY.LNAME_ARRAY,
1116 value_type IN VARCHAR2 DEFAULT '*',
1117 step_number IN NUMBER DEFAULT 0,
1118 operation IN VARCHAR2 DEFAULT 'ADD');
1119
1120 -- Specifies for the given rule that all lcrs with the specified
1121 -- schema and table will have a list of columns kept.
1122 --
1123 -- Parameters:
1124 -- rule name: The name of the rule to add this functionality to.
1125 -- table_name: The fully-qualified table name whose columns are to be
1126 -- kept. If the schema is not specified, the
1127 -- invoker of the api will be used.
1128 -- column_list: The comma separated list of columns to keep.
1129 -- value_type: Whether to keep the old, new, or both columns
1130 -- in the lcr. Specify 'old', 'new', or '*'.
1131 -- step_number: The order relative to other transformations.
1132 -- operation: Specify 'ADD' to add this transformation, or
1133 -- 'REMOVE' to remove it.
1134 PROCEDURE keep_columns(
1135 rule_name IN VARCHAR2,
1136 table_name IN VARCHAR2,
1137 column_list IN VARCHAR2,
1138 value_type IN VARCHAR2 DEFAULT '*',
1139 step_number IN NUMBER DEFAULT 0,
1140 operation IN VARCHAR2 DEFAULT 'ADD');
1141
1142 -- Specifies for the given rule that all lcrs with the specified
1143 -- schema and table will have a column renamed.
1144 --
1145 -- Parameters:
1146 -- rule name: The name of the rule to add this functionality to.
1147 -- table_name: The fully-qualified table name whose columns are to be
1148 -- renamed. If the schema is not specified, the
1152 -- value_type: Whether to rename the old, new, or both
1149 -- invoker of the api will be used.
1150 -- from_column_name: The column to be renamed.
1151 -- to_column_name: The new column name.
1153 -- columns in the lcr. Specify 'old', 'new', or '*'.
1154 -- step_number: The order relative to other transformations.
1155 -- operation: Specify 'ADD' to add this
1156 -- transformation, or 'REMOVE' to remove it.
1157 PROCEDURE rename_column(
1158 rule_name IN VARCHAR2,
1159 table_name IN VARCHAR2,
1160 from_column_name IN VARCHAR2,
1161 to_column_name IN VARCHAR2,
1162 value_type IN VARCHAR2 DEFAULT '*',
1163 step_number IN NUMBER DEFAULT 0,
1164 operation IN VARCHAR2 DEFAULT 'ADD');
1165
1166 -- Specifies for the given rule that all lcrs with the specified
1167 -- schema and table will have a column added.
1168 --
1169 -- Parameters:
1170 -- rule name: The name of the rule to add this functionality to.
1171 -- table_name: The fully-qualified table name whose columns are to be
1172 -- renamed. If the schema is not specified, the
1173 -- invoker of the api will be used.
1174 -- column_name: The new name of the column.
1175 -- column_value: The value to place in this new column. The
1176 -- type of the column will be determined by the
1177 -- type held within the AnyData. For now, this
1178 -- is limited to scalar values.
1179 -- value_type: Whether to add the old or new columns in the lcr.
1180 -- Specify 'old' or 'new'.
1181 -- step_number: The order relative to other transformations.
1182 -- operation: Specify 'ADD' to add this
1183 -- transformation, or 'REMOVE' to remove it.
1184 PROCEDURE add_column(
1185 rule_name IN VARCHAR2,
1186 table_name IN VARCHAR2,
1187 column_name IN VARCHAR2,
1188 column_value IN SYS.ANYDATA,
1189 value_type IN VARCHAR2 DEFAULT 'NEW',
1190 step_number IN NUMBER DEFAULT 0,
1191 operation IN VARCHAR2 DEFAULT 'ADD');
1192
1193 -- Specifies for the given rule that all lcrs with the specified
1194 -- schema and table will have a column added.
1195 --
1196 -- Parameters:
1197 -- rule name: The name of the rule to add this functionality to.
1198 -- table_name: The fully-qualified table name whose columns are to
1199 -- be renamed. If the schema is not specified, the
1200 -- invoker of the api will be used.
1201 -- column_name: The new name of the column.
1202 -- column_function: The name of a system built-in function whose
1203 -- value we want to place in a new column
1204 -- specified by column_name. For example, if we
1205 -- specified 'SYSDATE' as the function name, we
1206 -- would create a new column of type DATE
1207 -- (determined by the function return value),
1208 -- and place the result of SYSDATE into this
1209 -- column.
1210 -- value_type: Whether to add the old or new columns in the lcr.
1211 -- Specify 'old' or 'new'.
1212 -- step_number: The order relative to other transformations.
1213 -- operation: Specify 'ADD' to add this
1214 -- transformation, or 'REMOVE' to remove it.
1215 PROCEDURE add_column(
1216 rule_name IN VARCHAR2,
1217 table_name IN VARCHAR2,
1218 column_name IN VARCHAR2,
1219 column_function IN VARCHAR2,
1220 value_type IN VARCHAR2 DEFAULT 'NEW',
1221 step_number IN NUMBER DEFAULT 0,
1222 operation IN VARCHAR2 DEFAULT 'ADD');
1223
1224 PROCEDURE set_message_tracking(
1225 tracking_label IN VARCHAR2 DEFAULT 'Streams_tracking',
1226 actions IN NUMBER DEFAULT action_memory);
1227
1228 FUNCTION get_message_tracking RETURN VARCHAR2;
1229
1230 --
1231 -- One step API to set up Streams replication environment, including source
1232 -- queue, capture process, destinaiton queue, apply process, and optionally,
1233 -- propagation for change data capture (CDC). The one-step API constructs a
1234 -- DDL string to create a change table at the destination database, creates a
1235 -- capture process to capture changes to source table at the source database
1236 -- and creates an apply process to apply
1237 -- changes to the change table at the destination database.
1238 --
1239 -- Parameters
1240 --
1241 -- change_table_name : Name of change table
1242 -- source_table_name : Name of source table
1243 -- column_type_list : A comma-separated list of columns and datatypes
1244 -- for the change table
1245 -- extra_column_list : A comma-separated list LCR attributes to include
1246 -- in the change table
1247 -- capture_values : 'OLD', 'NEW', or '*'(BOTH)
1248 -- options_string : The syntactically correct options to be passed
1249 -- to a CREATE TABLE DDL statement. The string is
1250 -- appended to the generated CREATE TABLE DDL
1251 -- statement after the closing parenthesis that
1252 -- defines the columns of the table.
1253 -- script_name : Name of the script file to be generated
1254 -- script_directory_object: The directory object for the directory on the
1255 -- local computer system into which the generated
1256 -- script is placed.
1257 -- perform_actions : Whether to execute generate script?
1258 -- capture_name : Name of capture process
1259 -- propagation_name : Name of propagation
1260 -- apply_name : Name of apply process
1261 -- source_database : Name of source database
1262 -- destination_database : Name of destination database
1263 -- keep_columns : Whether the LCR only keeps the specified columns,
1264 -- including column from column_type_list and
1265 -- extra_column_list
1266 --
1267 PROCEDURE maintain_change_table(
1268 change_table_name VARCHAR2,
1269 source_table_name VARCHAR2,
1270 column_type_list VARCHAR2,
1271 extra_column_list VARCHAR2 DEFAULT 'COMMAND_TYPE, VALUE_TYPE',
1272 capture_values VARCHAR2,
1273 options_string VARCHAR2 DEFAULT NULL,
1274 script_name VARCHAR2 DEFAULT NULL,
1275 script_directory_object VARCHAR2 DEFAULT NULL,
1276 perform_actions BOOLEAN DEFAULT TRUE,
1277 capture_name VARCHAR2 DEFAULT NULL,
1278 propagation_name VARCHAR2 DEFAULT NULL,
1279 apply_name VARCHAR2 DEFAULT NULL,
1280 source_database VARCHAR2 DEFAULT NULL,
1281 destination_database VARCHAR2 DEFAULT NULL,
1282 keep_change_columns_only BOOLEAN DEFAULT TRUE,
1283 execute_lcr BOOLEAN DEFAULT FALSE);
1284
1285 -- sets the binary tag for all LCRs subsequently generated by the
1286 -- current session. Each LCR created by DML or DDL statement
1287 -- in the current session will have this tag.
1288 -- this procedure is not transactional and not affected by rollback
1289 -- Note: the invoker of set_tag should have execute privilege on
1290 -- dbms_streams_adm or execute_catalog_role
1291 PROCEDURE set_tag(tag IN RAW DEFAULT NULL);
1292
1293 -- get the binary tag for all LCRs generated by the current session.
1294 -- Note: the invoker of get_tag should have execute privilege on
1295 -- dbms_streams_adm or execute_catalog_role
1296 FUNCTION get_tag RETURN RAW;
1297
1298 END dbms_streams_adm;
[Home] [Help]