Want to move modified records from target to source schema

That would depend on the tables and what makes it 'modified'.

I would look at the MERGE SQL command:
http://download.oracle.com/docs/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606

you could use materialized views or triggers or flashback queries

or,  what about just using views that read from the source tables but don't actually copy the data?

How do you determine that a record was modified?  Do all of your tables have a column something like: "date_modified" or "date_updated"?

What kind of application is this for: a custom application or a purchased one, and if purchased, which one?

It is like synchronization. If i am correct then try this steps
1. Create audit table with three columns
table_name, id, IrU
     table_name is the source table name
     id is the primary key value of the source table. if PK not there you have to store some unique identifier
     IrU column will tell Insert or update
2.  create row level trigger in the all table (in source schema) which you want syncronize with other schema. This Trigger will insert row into audit table
3. your periodical process should read audit table (step 1) and your source table and merge the same with Destination table
4. Delete the row from audit table.

I am planning to use a trigger and an audit table, in that, whatever rows that are affected, I will capture
the affected rows and store the ROWIDs, With the help of Rowids, I will query the modified rows and update the target., Will this solution work / Feasible?

That concept should work, but you need a lot more than one trigger!  You need at least one trigger for each table in the source schema.  Also, unless you plan to use dynamic SQL in your update logic (and if you do, you will need another column in your audit table to hold the source table name) you will need an audit table for each table in the source schema.  This will allow you to avoid the need for dynamic SQL in your update logic.

>>> Will this solution work / Feasible?

yes, it probably will.  You might want to look at materialized views - Oracle will do all of that for you.

if you need more functionality than mv's will provide,  look at dbms_fga  (fine grained auditting) as an alternate to triggers.

>>Will this solution work / Feasible?

It depends.  Have the tables enabled row movement?  Some operations can actually change the rowids of rows.

This is why I asked what makes a row modified.  Can rows be deleted?  Can the PK be modified?

You might look into one of the replication options available.  MVs, as suggested, being one of them.

sdstuber,

Please tell me like how materialized view can be useful for ETL.

Please give me one example.

Requirement is:

I need to compare the tables in schema1 and schema2.
If there are changes in schema1 then
I need to copy the changed records in schema1 and insert those records in schema2.

Will this be feasible through mviews.

yes

What I understand from mviews is, when the query takes long time for execution.
we use mviews, which can be used as a table.

now, in this context, "copy the changed records in schema1 and insert those records in schema2."
how mviews can be helpful, please help me with an example.

example is above.

if you change the data in the table,  the data will move to the mv.


You don't have to believe me, just try it.

I would still look into one of the replication options over a lot of custom triggers.

>> These triggers will have to insert either the rowid or a primary or unique key

I mentioned before that rowids and PKs are not a reliable indicator depending on how things are set up and what can be 'modified'.

Thanks, both of the solution will work for my problem.

In my table, i will not delete rows/modify key  values.

so both solutions are acceptable for me.

Thanks again for this much response.