Hi Riyaj, Thanks much for the reply. I will try this out. I have some doubt: Does this setup requires a separate destination queue? I guess no. Loopback database link is nothing but a dblink to the same database itself? Thanks, Gidhin 2009/3/9 Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> > Hello gidhin > 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. > > Please see example code below. Of course, I haven't tested this, but > should work just fine. > > Example: EMP table from SCOTT schema to MARY schema. > REM > REM We create a generic dml handler so that we can reuse it. > REM Following example manipulates LCRs and updates schema. > REM conn as strmadmin for this procedure > CREATE OR REPLACE PROCEDURE generic_dml_handler(in_any IN SYS.ANYDATA) IS > lcr SYS.LCR$_ROW_RECORD; > rc PLS_INTEGER; > command VARCHAR2(10); > old_values SYS.LCR$_ROW_LIST; > l_object_name varchar2(31); > BEGIN > -- Access the LCR > rc := in_any.GETOBJECT(lcr); > -- Get the object command type > command := lcr.GET_COMMAND_TYPE(); > -- Get current object name > l_object_name := lcr.GET_OBJECT_NAME(); > -- Set the object_owner in the row LCR if object name and owner matches. > if (l_object_name='EMP') then > l_owner := lcr.GET_OBJECT_OWNER(); > IF (l_owner = 'SCOTT') then > -- Schema owner modified from scott to mary for emp table. > lcr.SET_OBJECT_OWNER ( 'MARY'); > end if; > end if; > -- Apply the row LCR as an INSERT into the new table > lcr.EXECUTE(true); > END; > / > REM Add above DML Handler to the table > > BEGIN > DBMS_APPLY_ADM.SET_DML_HANDLER( > object_name => 'scott.emp', > object_type => 'TABLE', > operation_name => 'INSERT',-- modify accordingly > error_handler => false, > user_procedure => 'strmadmin.generic_dml_handler', > apply_database_link => NULL); > END; > / > > -- > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com > Specialists in Performance, Recovery and EBS11i > Blog: http://orainternals.wordpress.com > > > On Mon, Mar 9, 2009 at 7:58 AM, gidhin joy <gidhin@xxxxxxxxx> wrote: > >> Hi Lists, >> >> I need to configure streams for tables within database (From schema A to >> B) which i feels difficult since my configuration just fails. >> Can someone help me with the steps on configuring streams in same >> database or provide some links on this topic >> I am already using a streams environment between tables in databases >> located in Windows and Linux db server wich work fine. >> >> OS: AIX 5.3 >> Oracle: 10.2.0.2.0 >> >> Thanks. >> > > >