Oracle | DBMS_STREAMS

Oracle DBMS_STREAMS_ADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Subprograms for configuring a Streams replication environment

Note The parameter SOURCE_DATABASE should be left NULL except in the event of downstream capture.

Streams was deprecated 03-Jan-2018 but this package still exists in 21c.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Instantiation Constants
instantiation_none	BINARY_INTEGER	0
instantiation_table	BINARY_INTEGER	1
instantiation_table_network	BINARY_INTEGER	2
instantiation_schema	BINARY_INTEGER	3
instantiation_schema_network	BINARY_INTEGER	4
instantiation_full	BINARY_INTEGER	5
instantiation_full_network	BINARY_INTEGER	6
instantiation_tts	BINARY_INTEGER	7
instantiation_tts_network	BINARY_INTEGER	8
Prepare_Upgrade API Constants
exclude_flags_full	BINARY_INTEGER	1
exclude_flags_unsupported	BINARY_INTEGER	2
exclude_flags_dml	BINARY_INTEGER	4
exclude_flags_ddl	BINARY_INTEGER	8
Message Tracing Constants
action_trace	BINARY_INTEGER	1
action_memory	BINARY_INTEGER	2

Definitions

Keyword

Definition

tagged_lcr

Every redo log entry has an associated tag. The datatype of the tag is RAW. By default, when a user or application generates redo entries, the value of the tag is NULL.

You can configure how tag values are interpreted. A tag can be used to determine whether an LCR contains a change that originated in the local database or at a different database, so that to avoid change cycling (sending an LCR back to the database where it originated). Tags can be used for other LCR tracking purposes as well. For example to specify the set of destination databases for each LCR.

Create tags with DBMS_STREAMS.SET_TAG.

Dependencies

ANYDATA	DBMS_STREAMS_ADM_IVK	DBMS_UTILITY
DBMS_LOGREP_UTIL	DBMS_STREAMS_TABLESPACE_ADM	DBMS_XSTREAM_ADM_INTERNAL

Documented Yes

Exceptions

Error Code	Reason
ORA-26664	Can not create process
ORA-26665	Process exists
ORA-26667	Invalid parameter
ORA-26698	Client Rule Set does not exist
ORA-26699	Dequeue exists
ORA-26701	Process does not exist
ORA-26723	Role required
ORA-26724	Set user to SYS
ORA-26754	Mult trans specified

First Available 9.2.0.1

Security Model Owned by SYS with EXECUTE granted to EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmsstr.sql

Subprograms

ADD_COLUMN	GET_SCN_MAPPING	PURGE_SOURCE_CATALOG
ADD_GLOBAL_PROPAGATION_RULES	GET_TAG	RECOVER_OPERATION
ADD_GLOBAL_RULES	KEEP_COLUMNS	REMOVE_QUEUE
ADD_MESSAGE_PROPAGATION_RULE	MAINTAIN_GLOBAL	REMOVE_RULE
ADD_MESSAGE_RULE	MAINTAIN_SCHEMAS	REMOVE_STREAMS_CONFIGURATION
ADD_SCHEMA_PROPAGATION_RULES	MAINTAIN_SIMPLE_TABLESPACE	RENAME_COLUMN
ADD_SCHEMA_RULES	MAINTAIN_SIMPLE_TTS	RENAME_SCHEMA
ADD_SUBSET_PROPAGATION_RULES	MAINTAIN_TABLES	RENAME_TABLE
ADD_SUBSET_RULES	MAINTAIN_TABLESPACES	SET_MESSAGE_NOTIFICATION
ADD_TABLE_PROPAGATION_RULES	MAINTAIN_TTS	SET_MESSAGE_TRACKING
ADD_TABLE_RULES	MERGE_STREAMS	SET_RULE_TRANSFORM_FUNCTION
CLEANUP_INSTANTIATION_SETUP	MERGE_STREAMS_JOB	SET_TAG
DELETE_COLUMN	POST_INSTANTIATION_SETUP	SET_UP_QUEUE
DELETE_REPLICATION_EVENTS	PRE_INSTANTIATION_SETUP	SPLIT_STREAMS
GET_MESSAGE_TRACKING

ADD_COLUMN

Either adds or removes a declarative rule-based transformation which adds a column to a row logical change record (row LCR) that satisfies the specified rule

Overload 1 dbms_streams_adm.add_column( rule_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2, column_function IN VARCHAR2, value_type IN VARCHAR2 DEFAULT 'NEW', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

Overload 2 dbms_streams_adm.add_column( rule_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2, column_value IN SYS.ANYDATA, value_type IN VARCHAR2 DEFAULT 'NEW', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

ADD_GLOBAL_PROPAGATION_RULES

Either adds global rules to the positive rule set for a propagation, or adds global rules to the negative rule set for a propagation, and creates the specified propagation if it does not exist

Overload 1 dbms_streams_adm.add_global_propagation_rules( streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_global_propagation_rules( streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

See Streams Demo 1 Link at page bottom

ADD_GLOBAL_RULES

Adds rules to a RULE SET of one a Streams clients

Overload 1 dbms_streams_adm.add_global_rules( streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_global_rules( streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

TBD

ADD_MESSAGE_PROPAGATION_RULE

Adds a message rule to the positive RULE SET for a propagation, or adds a message rule to the negative RULE SET for a propagation, and creates the specified propagation if it does not exist

Overload 1 dbms_streams_adm.add_message_propagation_rule ( message_type IN VARCHAR2, rule_condition IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as an OUT parameter, the rule name dbms_streams_adm.add_message_propagation_rule ( message_type IN VARCHAR2, rule_condition IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, rule_name OUT VARCHAR2, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

ADD_MESSAGE_RULE

Adds a message rule to a RULE SET of a Streams clients

Overload 1 dbms_streams_adm.add_message_rule ( message_type IN VARCHAR2, rule_condition IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', inclusion_rule IN BOOLEAN DEFAULT TRUE);

TBD

Overload 2

Same as above but returns, as an OUT parameter, the rule name dbms_streams_adm.add_message_rule ( message_type IN VARCHAR2, rule_condition IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', inclusion_rule IN BOOLEAN DEFAULT TRUE, rule_name OUT VARCHAR2);

TBD

ADD_SCHEMA_PROPAGATION_RULES

Either adds schema rules to the positive rule set for a propagation, or adds schema rules to the negative RULE SET for a propagation, and creates the specified propagation if it does not exist

Overload 1 dbms_streams_adm.add_schema_propagation_rules( schema_name IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_schema_propagation_rules( schema_name IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

ADD_SCHEMA_RULES

Adds rules to a rule set of one of a Streams clients

Overload 1 dbms_streams_adm.add_schema_rules( schema_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_schema_rules( schema_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

See Streams Demo 1 Link at page bottom

ADD_SUBSET_PROPAGATION_RULES

Adds propagation rules that propagate the logical change records (LCRs) related to a subset of the rows in the specified table in a source queue to a destination queue, and creates the specified propagation if it does not exist

Overload 1 dbms_streams_adm.add_subset_propagation_rules( table_name IN VARCHAR2, dml_condition IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_subset_propagation_rules( table_name IN VARCHAR2, dml_condition IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, insert_rule_name OUT VARCHAR2, update_rule_name OUT VARCHAR2, delete_rule_name OUT VARCHAR2, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

ADD_SUBSET_RULES

Adds rules to a rule set of a Streams clients

Overload 1 dbms_streams_adm.add_subset_rules( table_name IN VARCHAR2, dml_condition IN VARCHAR2, streams_type IN VARCHAR2 DEFAULT 'APPLY', streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL);

set serveroutput on

      

      DECLARE

       ins_rule VARCHAR2(30);

       upd_rule VARCHAR2(30);

       del_rule VARCHAR2(30);

      BEGIN

        dbms_streams_adm.add_subset_rules(

          table_name         => 'SCOTT.EMP',

          dml_condition      => 'DEPTNO=20',

          streams_type       => 'CAPTURE',

          streams_name       => 'SCOTT_CAPTURE',

          queue_name         => 'SCOTT_CAPTURE_Q',

          include_tagged_lcr => FALSE,

          source_database    => NULL,

          insert_rule_name   => ins_rule,

          update_rule_name   => upd_rule,

          delete_rule_name   => del_rule);

      

        dbms_output.put_line(ins_rule);

        dbms_output.put_line(ins_rule);

        dbms_output.put_line(ins_rule);

      END;

      /

Overload 2 dbms_streams_adm.add_subset_rules( table_name IN VARCHAR2, dml_condition IN VARCHAR2, streams_type IN VARCHAR2 DEFAULT 'APPLY', streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, insert_rule_name OUT VARCHAR2, update_rule_name OUT VARCHAR2, delete_rule_name OUT VARCHAR2);

TBD

ADD_TABLE_PROPAGATION_RULES

Adds table rules to the positive or negative rule set for a propagation and creates the specified propagation if it does not exist

Overload 1 dbms_streams_adm.add_table_propagation_rules( table_name IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

Overload 2

Same as above but returns, as OUT parameters, the rule names dbms_streams_adm.add_table_propagation_rules( table_name IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, source_queue_name IN VARCHAR2, destination_queue_name IN VARCHAR2, include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL, queue_to_queue IN BOOLEAN DEFAULT NULL);

TBD

ADD_TABLE_RULES

Adds rules to a rule set of Streams clients

Overload 1 dbms_streams_adm.add_table_rules( table_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

TBD

Overload 2 dbms_streams_adm.add_table_rules( table_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', include_dml IN BOOLEAN DEFAULT TRUE, include_ddl IN BOOLEAN DEFAULT FALSE, include_tagged_lcr IN BOOLEAN DEFAULT FALSE, source_database IN VARCHAR2 DEFAULT NULL, dml_rule_name OUT VARCHAR2, ddl_rule_name OUT VARCHAR2, inclusion_rule IN BOOLEAN DEFAULT TRUE, and_condition IN VARCHAR2 DEFAULT NULL);

DECLARE

       dml_rule VARCHAR2(50);

       ddl_rule VARCHAR2(50);

      BEGIN

        dbms_streams_adm.add_table_rules(

        table_name    => ''SCOTT.EMP'',

        streams_type  => 'CAPTURE',

        streams_name  => 'SCOTT_CAPTURE',

        queue_name    => 'SCOTT_CAPTURE_Q',

        dml_rule_name => dml_rule,

        ddl_rule_name => ddl_rule,

        and_condition => ':lcr.get_command_type() != ''DELETE''');

      

        dbms_output.put_line(v_dml_rule);

        dbms_output.put_line(v_ddl_rule);

      END;

      /

CLEANUP_INSTANTIATION_SETUP

Removes a Streams replication configuration set up by the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP dbms_streams_adm.cleanup_instantiation_setup( maintain_mode IN VARCHAR2, tablespace_names IN dbms_streams_tablespace_adm.tablespace_set, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, change_global_name IN BOOLEAN DEFAULT FALSE);

TBD

DELETE_COLUMN

Either adds or removes a declarative rule-based transformation which deletes a column from a row logical change record (LCR) that satisfies the specified rule dbms_streams_adm.delete_column( rule_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2, value_type IN VARCHAR2 DEFAULT '*', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

DECLARE

       v_dml_rule VARCHAR2(50);

      BEGIN

        dbms_streams_adm.delete_column(

        rule_name   => v_dml_rule,

        table_name  => 'EMP',

        column_name => 'SAL',

        value_type  => '*',

        step_number => 0,

        operation   => 'ADD');

      

        dbms_output.put_line(v_dml_rule);

      END;

      /

DELETE_REPLICATION_EVENTS

Purges replication event records from repl$_process_events dbms_streams_adm.delete_replication_events( streams_name IN VARCHAR2 DEFAULT NULL, streams_type IN VARCHAR2 DEFAULT NULL, process_type IN VARCHAR2 DEFAULT NULL, event_name IN VARCHAR2 DEFAULT NULL, include_error IN BOOLEAN DEFAULT TRUE, event_time IN TIMESTAMP DEFAULT NULL);

TBD

GET_MESSAGE_TRACKING

Returns the tracking label for the current session dbms_streams_adm.get_message_tracking RETURN VARCHAR2;

See SET_MESSAGE_TRACKING Demo Below

GET_SCN_MAPPING

For point in time recovery, given the SCN at the source, returns the instantiation SCN and start SCN from the destination dbms_streams_adm.get_scn_mapping( apply_name IN VARCHAR2, src_pit_scn IN NUMBER, dest_instantiation_scn OUT NUMBER, dest_start_scn OUT NUMBER, dest_skip_txn_ids OUT dbms_utility.name_array);

TBD

GET_TAG

Returns the binary tag for all redo entries generated by the current session dbms_streams_adm.get_tag RETURN RAW;

set serveroutput on

      

      DECLARE

       tagVal RAW(2000);

      BEGIN

        tagval := dbms_streams_adm.get_tag;

        dbms_output.put_line(tagVal);

      END;

      /

KEEP_COLUMNS

Specifies for the given rule that all LCRs with the specified schema and table will have a list of columns kept

Overload 1 dbms_streams_adm.keep_columns( rule_name IN VARCHAR2, table_name IN VARCHAR2, column_table IN dbms_utility.lname_array, value_type IN VARCHAR2 DEFAULT '*', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

Overload 2 dbms_streams_adm.keep_columns( rule_name IN VARCHAR2, table_name IN VARCHAR2, column_list IN VARCHAR2, value_type IN VARCHAR2 DEFAULT '*', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

MAINTAIN_GLOBAL

Configures a Streams environment that replicates changes at the database level between two databases dbms_streams_adm.maintain_global( source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, instantiation IN BINARY_INTEGER DEFAULT dbms_streams_adm.instantiation_full);

TBD

MAINTAIN_SCHEMAS

Configures a Streams environment that replicates changes to specified schemas between two databases

Overload 1 dbms_streams_adm.maintain_schemas( schema_names IN dbms_utility.uncl_array, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, instantiation IN BINARY_INTEGER DEFAULT dbms_streams_adm.instantiation_schema);

TBD

Overload 2 dbms_streams_adm.maintain_schemas( schema_names IN VARCHAR2, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, instantiation IN BINARY_INTEGER DEFAULT dbms_streams_adm.instantiation_schema);

TBD

MAINTAIN_SIMPLE_TABLESPACE

Clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases dbms_streams_adm.maintain_simple_tablespace( tablespace_name IN VARCHAR2, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, destination_database IN VARCHAR2, setup_streams IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE);

TBD

MAINTAIN_SIMPLE_TTS

Clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases. This procedure can either perform these actions directly, or it can generate a script that performs these actions dbms_streams_adm.maintain_simple_tts( tablespace_name IN VARCHAR2, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE);

TBD

MAINTAIN_TABLES

Configures a Streams environment that replicates changes to specified tables between two databases. This procedure can either configure the environment directly, or it can generate a script that configures the environment.

Overload 1 dbms_streams_adm.maintain_tables( table_names IN VARCHAR2, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, instantiation IN BINARY_INTEGER DEFAULT dbms_streams_adm.instantiation_table);

TBD

Overload 2 dbms_streams_adm.maintain_tables( table_names IN dbms_utility.uncl_array, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, instantiation IN BINARY_INTEGER DEFAULT dbms_streams_adm.instantiation_table);

TBD

MAINTAIN_TABLESPACES

Clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases dbms_streams_adm.maintain_tablespaces( tablespace_names IN dbms_streams_tablespace_adm.tablespace_set, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, destination_database IN VARCHAR2, setup_streams IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, source_queue_table IN VARCHAR2 DEFAULT 'streams_queue_table', source_queue_name IN VARCHAR2 DEFAULT 'streams_queue', source_queue_user IN VARCHAR2 DEFAULT NULL, destination_queue_table IN VARCHAR2 DEFAULT 'streams_queue_table', destination_queue_name IN VARCHAR2 DEFAULT 'streams_queue', destination_queue_user IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT 'capture', propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE);

TBD

MAINTAIN_TTS

Clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases dbms_streams_adm.maintain_tts( tablespace_names IN dbms_streams_tablespace_adm.tablespace_set, source_directory_object IN VARCHAR2, destination_directory_object IN VARCHAR2, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, dump_file_name IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, log_file IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE);

See DBMS_STREAMS_TABLESPACE_ADM link below

MERGE_STREAMS

Merges a stream that is flowing from one capture process with a stream that is flowing from another capture process dbms_streams_adm.merge_streams( cloned_propagation_name IN VARCHAR2, propagation_name IN VARCHAR2, queue_name IN VARCHAR2 DEFAULT NULL, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL);

TBD

MERGE_STREAMS_JOB

Determines whether the original capture process and the cloned capture process are within the specified merge threshold. If they are within the merge threshold, then this procedure runs the MERGE_STREAMS procedure to merge the two streams. dbms_streams_adm.merge_streams_job( capture_name IN VARCHAR2, cloned_capture_name IN VARCHAR2, merge_threshold IN NUMBER, schedule_name IN VARCHAR2 DEFAULT NULL, merge_job_name IN VARCHAR2 DEFAULT NULL);

TBD

POST_INSTANTIATION_SETUP

Performs the actions required after instantiation to configure a Streams replication environment dbms_streams_adm.post_instantiation_setup( maintain_mode IN VARCHAR2, tablespace_names IN dbms_streams_tablespace_adm.tablespace_set, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, start_processes IN BOOLEAN DEFAULT FALSE, instantiation_scn IN NUMBER DEFAULT NULL, exclude_schemas IN VARCHAR2 DEFAULT NULL, exclude_flags IN BINARY_INTEGER DEFAULT NULL);

TBD

PRE_INSTANTIATION_SETUP

Performs the actions required before instantiation to configure a Streams replication environment dbms_streams_adm.pre_instantiation_setup( maintain_mode IN VARCHAR2, tablespace_names IN dbms_streams_tablespace_adm.tablespace_set, source_database IN VARCHAR2, destination_database IN VARCHAR2, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, capture_name IN VARCHAR2 DEFAULT NULL, capture_queue_table IN VARCHAR2 DEFAULT NULL, capture_queue_name IN VARCHAR2 DEFAULT NULL, capture_queue_user IN VARCHAR2 DEFAULT NULL, propagation_name IN VARCHAR2 DEFAULT NULL, apply_name IN VARCHAR2 DEFAULT NULL, apply_queue_table IN VARCHAR2 DEFAULT NULL, apply_queue_name IN VARCHAR2 DEFAULT NULL, apply_queue_user IN VARCHAR2 DEFAULT NULL, bi_directional IN BOOLEAN DEFAULT FALSE, include_ddl IN BOOLEAN DEFAULT FALSE, start_processes IN BOOLEAN DEFAULT FALSE, exclude_schemas IN VARCHAR2 DEFAULT NULL, exclude_flags IN BINARY_INTEGER DEFAULT NULL);

TBD

PURGE_SOURCE_CATALOG

Removes all Streams data dictionary information at the local database for the specified object dbms_streams_adm.purge_source_catalog( source_database IN VARCHAR2, source_object_name IN VARCHAR2, source_object_type IN VARCHAR2);

exec dbms_streams_adm.purge_source_catalog('UKOUG', 'HR.CDC_DEMO', 'TABLE');

RECOVER_OPERATION

Provides options for a Streams replication configuration operation that stopped because it encountered an error either the operation forward or backward, or purges all of the metadata about the operation dbms_streams_adm.recover_operation( script_id IN RAW, operation_mode IN VARCHAR2 DEFAULT 'FORWARD');

TBD: but see the link, below to DBMS_RECOVERABLE_SCRIPT

REMOVE_QUEUE

Removes a queue from use in Streams

Specifically, the queue will be stopped, and no further enqueue or dequeues will be allowed on the queue. dbms_streams_adm.remove_queue( queue_name IN VARCHAR2, cascade IN BOOLEAN DEFAULT FALSE, drop_unused_queue_table IN BOOLEAN DEFAULT TRUE);

desc user_queues

      

      SELECT name, queue_table

      FROM user_queues;

      

      exec dbms_streams_adm.remove_queue('myafsc_envoy_q', TRUE, TRUE);

REMOVE_RULE

Removes the specified rule or all rules from the rule set associated with the specified capture process, apply process, propagation, or messaging client dbms_streams_adm.remove_rule( rule_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2, drop_unused_rule IN BOOLEAN DEFAULT TRUE, inclusion_rule IN BOOLEAN DEFAULT TRUE);

TBD

REMOVE_STREAMS_CONFIGURATION

Removes the local Streams configuration dbms_streams_adm.remove_streams_configuration;

/* the following provided by Michael M. Brennan from RSA Security who reports that REMOVE_STREAMS_CONFIGURATION does not work unless the

      propagation has been dropped first. */

      

      SELECT propagation_name

      FROM dba_propagation;

      

      exec dbms_propagation_adm.drop_propagation('PROPAGATION$_143');

      

      exec dbms_streams_adm.remove_streams_configuration;

RENAME_COLUMN

Either adds or removes a declarative rule-based transformation which renames a column in a row logical change record (LCR) that satisfies the specified rule dbms_streams_adm.rename_column( rule_name IN VARCHAR2, table_name IN VARCHAR2, from_column_name IN VARCHAR2, to_column_name IN VARCHAR2, value_type IN VARCHAR2 DEFAULT '*', step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

RENAME_SCHEMA

Either adds or removes a declarative rule-based transformation which renames a schema in a row logical change record (LCR) that satisfies the specified rule dbms_streams_adm.rename_schema( rule_name IN VARCHAR2, from_schema_name IN VARCHAR2, to_schema_name IN VARCHAR2, step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

DECLARE

       v_dml_rule VARCHAR2(50);

      BEGIN

        dbms_streams_adm.rename_schema(

        rule_name => v_dml_rule,

        from_schema_name => 'ENGINE',

        to_schema_name => 'ENGINE_WEST',

        step_number => 0,

        operation => 'ADD');

      

        dbms_output.put_line(v_dml_rule);

      END;

      /

RENAME_TABLE

Either adds or removes a declarative rule-based transformation which renames a table in a row logical change record (row LCR) that satisfies the specified rule dbms_streams_adm.rename_table( rule_name IN VARCHAR2, from_table_name IN VARCHAR2, to_table_name IN VARCHAR2, step_number IN NUMBER DEFAULT 0, operation IN VARCHAR2 DEFAULT 'ADD');

TBD

REMOVE_RULE

Removes the specified rule or removes all rules from the rule set associated with the specified capture process, apply process, propagation or message consumer rule set dbms_streams_adm.remove_rule( rule_name IN VARCHAR2, streams_type IN VARCHAR2, streams_name IN VARCHAR2, drop_unused_rule IN BOOLEAN DEFAULT TRUE, inclusion_rule IN BOOLEAN DEFAULT TRUE);

TBD

SET_MESSAGE_NOTIFICATION

Sets a notification for messages that can be dequeued by a specified Streams messaging client from a specified queue dbms_streams_adm.set_message_notification( streams_name IN VARCHAR2, notification_action IN VARCHAR2, notification_type IN VARCHAR2 DEFAULT 'PROCEDURE', notification_context IN sys.anydata DEFAULT NULL, include_notification IN BOOLEAN DEFAULT TRUE, queue_name IN VARCHAR2 DEFAULT 'streams_queue');

TBD

SET_MESSAGE_TRACKING

Sets the tracking label for logical change records (LCRs) produced by the current session. This procedure affects only the current session. Any LCRs produced by the current session are tracked, including captured LCRs and persistent LCRs. dbms_streams_adm.set_message_tracking ( tracking_label IN VARCHAR2 DEFAULT 'Streams_tracking', actions IN NUMBER DEFAULT action_memory);

set serveroutput on

      

      DECLARE

       str VARCHAR2(100);

      BEGIN

        dbms_streams_adm.set_message_tracking;

        str := dbms_streams_adm.get_message_tracking;

        dbms_output.put_line(str);

      END;

      /

SET_RULE_TRANSFORM_FUNCTION

Sets or removes the transformation function name for a custom rule-based transformation dbms_streams_adm.set_rule_transform_function( rule_name IN VARCHAR2, transform_function IN VARCHAR2);

TBD

SET_UP_QUEUE

Sets up a queue table and a queue for use with the capture, propagate, and apply functionality of Streams. The queue functions as a Streams queue. dbms_streams_adm.set_up_queue( queue_table IN VARCHAR2 DEFAULT 'streams_queue_table', storage_clause IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', queue_user IN VARCHAR2 DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

See Streams Demo 1 Link at page bottom

SPLIT_STREAMS

splits one stream flowing from a capture process off from all of the other streams flowing from the capture process dbms_streams_adm.split_streams( propagation_name IN VARCHAR2, cloned_propagation_name IN VARCHAR2 DEFAULT NULL, cloned_queue_name IN VARCHAR2 DEFAULT NULL, cloned_capture_name IN VARCHAR2 DEFAULT NULL, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, auto_merge_threshold IN NUMBER DEFAULT NULL, schedule_name IN OUT VARCHAR2, merge_job_name IN OUT VARCHAR2);

TBD

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated:

DBSecWorx