Re: Streams configuration within schemas in a database

From: Alex Fatkulin <>
Date: Tue, 17 Mar 2009 00:13:35 -0400
Message-ID: <>

I'd go that route as well.

Having a single queue for capture/apply simplifies the setup. You can further simply this by keeping rules on capture side only.

also, calling lcr.execute causes a soft parse of whatever is stored in the lcr every time (at least that was the behavior in 10GR2, not sure if that was changed in 11G). Statements directly executed by apply are not subject to that, thus rename_schema might be a better idea from a performance standpoint as well (just keep an mind that that not gonna work for ddls).

On Fri, Mar 13, 2009 at 6:19 PM, Chen Shapira <> wrote:
> Hi Gidhin and Riyaj
>>    Since you have streams running already, I assume, you know how to do
>> basic setup for streams. With that, to setup streams between two schemas in
>> the same database, you would setup streams just like schemas between two
>> different database (i.e. loop back database link, capture, propagate and
>> apply) etc. Then add a DML handler to the table at apply process.
> I'd recommend a simpler setup which includes a capture process, one
> queue and an apply process. No need for loopback, and no need for
> propagation. Streams is complicated enough so the simpler we can
> configure it, the better.
>> CREATE OR REPLACE PROCEDURE generic_dml_handler(in_any IN SYS.ANYDATA) IS
> Again, in interest of simplification, you can use declarative rules to
> change schema name, no need for a handler.
> I'm attaching an example, but the basic declarative rule syntax is:
>   rule_name=>v_dml_rule_name,
>   from_schema_name=>'HR',
>   to_schema_name=>'MYHR',
>   step_number=>0,
>   operation=>'ADD');
> Chen Shapira

Alex Fatkulin,
Received on Mon Mar 16 2009 - 23:13:35 CDT

Original text of this message