• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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.
0
sakthikumar
Asked:
sakthikumar
  • 5
  • 5
  • 3
  • +2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
0
 
sdstuberCommented:
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?
0
 
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?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
gajmpCommented:
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.
0
 
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?
0
 
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.
0
 
sdstuberCommented:
>>> 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.
0
 
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.
0
 
sakthikumarAuthor Commented:
sdstuber,

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

Please give me one example.

0
 
sdstuberCommented:
>>> 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.


CREATE MATERIALIZED VIEW LOG ON YOUR_SCHEMA.EMP
WITH PRIMARY KEY
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW MY_SCHEMA.EMP_MV (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
BUILD IMMEDIATE
REFRESH FAST
START WITH SYSDATE
NEXT sysdate + 1/24 
WITH PRIMARY KEY
AS 
SELECT * FROM YOUR_SCHEMA.EMP;   --- If I want transformations I include them as part of this query

Open in new window

0
 
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.
0
 
sdstuberCommented:
yes
0
 
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.
0
 
sdstuberCommented:
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.
0
 
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.
0
 
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'.
0
 
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now