Want to move modified records from target to source schema

I want to move modified records/inserted records from the tables of one schema to another schema. How can i check for inserted / modified records in the source schema.

I am looking for some method/process to run this one periodically.

Please help me with some standard method/scripts/ideas.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
That would depend on the tables and what makes it 'modified'.

I would look at the MERGE SQL command:
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?
Mark GeerlingsDatabase AdministratorCommented:
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?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
sakthikumarAuthor Commented:
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?
Mark GeerlingsDatabase AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
sakthikumarAuthor Commented:

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

Please give me one example.

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

I'm not sure what you mean.  A materialized view does exactly what you asked for.
They copy data.  You never mentioned ETL before though.  You can do it, depending on the "T" it just makes your MV more complicated.


NEXT sysdate + 1/24 
SELECT * FROM YOUR_SCHEMA.EMP;   --- If I want transformations I include them as part of this query

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sakthikumarAuthor Commented:
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.
sakthikumarAuthor Commented:
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.
Mark GeerlingsDatabase AdministratorCommented:
I'm not sure than an MV (or multiple MVs) is the best solution to this problem.  Sure, MVs can accumulate new or changed records.  But removing them from the MV(s) after they have been processed will be a problem.

You said that you "need to compare the tables in schema1 and schema2".  If you haven't had any mechanism in place to replicate adds or changes, you will first need to do exactly what you said, that is: "compare the tables in schema1 and schema2" and insert any new rows, plus copy the changed rows to schema 2.  (What about deletes from schema 1 tables?  Are they ever allowed?  If yes, you have another problem.)

After you do a one-time compare, you could set up a mechanism to keep the two schemas in sync from now on.  To do that you will need to create "after insert" and "after update" triggers on each table in schema 1.  These triggers will have to insert either the rowid or a primary or unique key value plus an "I" for insert or "U" for update into a queue table that you set up for each table in schema 1.  Then you create a procedure to query these queue tables whenever you want, and insert or update the corresponding information for each row into schema 2, then delete that row from the queue table.
slightwv (䄆 Netminder) Commented:
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'.
sakthikumarAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.