CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_STREAMS_ADM
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsstr.sql
First Available 9.2

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

destination _queue_name

 

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
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_STREAMS_ADM'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_STREAMS_ADM';

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
Security Model Execute is granted to the EXECUTE_CATALOG_ROLE role
 
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
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
 
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
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
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
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_message_propagation_rules(
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
dbms_streams_adm.add_message_propagation_rules(
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_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
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
 
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
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);
TBD
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 rule set for a propagation, or adds table 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_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
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 a  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;
/
 
GET_MESSAGE_TRACKING (new in 11g)

Undocumented
dbms_streams_adm.get_message_tracking RETURN VARCHAR2;
See SET_MESSAGE_TRACKING Demo
 
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
 
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);
TBD
 
MERGE_STREAMS (new in 11g)

Undocumented
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 (new in 11g)

Undocumented
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
 
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 (new in 11g)

Undocumented
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
 
SPLIT_STREAMS (new in 11g)

Undocumented
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
 
Related Topics
Advanced Queuing
DBMS_CAPTURE_ADM
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_PROPAGATION_ADM
DBMS_STREAMS
DBMS_STREAMS_AUTH
Streams Demo
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [262 users online]    © 2010 psoug.org