Re: Streams configuration within schemas in a database
- From: Alex Fatkulin <afatkulin@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 17 Mar 2009 00:13:35 -0400
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 <cshapi@xxxxxxxxx> 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:
>
> DBMS_STREAMS_ADM.RENAME_SCHEMA(
> rule_name=>v_dml_rule_name,
> from_schema_name=>'HR',
> to_schema_name=>'MYHR',
> step_number=>0,
> operation=>'ADD');
>
>
> Chen Shapira
>
--
Alex Fatkulin,
http://afatkulin.blogspot.com
http://www.linkedin.com/in/alexfatkulin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
