Solved

Comparing 2 tables for Data Migration

Posted on 2011-03-23
4
308 Views
Last Modified: 2012-05-11

Dear Friends

I have a source Environment with source tables

I have a target Environment with Target Tables.

We have written pl/sql package to migrate source tables  into target tables

All the fields are not one to one mapping.

So, many queries are written and procedures created and data migration done.

We are in the process of verification of source and target data in our environment.

Using compare script method, we are putting both source and target queries and comparing now. But we are getting out of memory error some times due to larger volume.

So, we want to try to read one row in cursor of source table and one row of the target row

1) Please provide me the best method to verifiy without out of memory error.

2) is it possible to use row type compare both source rowtype and target rowtype
I am getting error while trying to do so.

Please help and guide me on this

Regards

Sathya Narayanan

0
Comment
Question by:sathyabalaji
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Akenathon
ID: 35200833
1) Define "memory error". Please provide exact version numbers, exact error numbers (whole error stack), exact steps you take to reproduce it. As a general rule, do NOT compare huge amounts of REMOTE data. Copy all tables to only ONE database (probably to another schema), and run every comparison LOCALLY.

2) You don't compare whole rows, you have to compare fields one by one. However, you said the migration is NOT just a mapping of fields 1-to-1, so you need to do this anyway...
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 35223707
In the columns in row are different for both environments a simple comparision of rowtype data cannot be doen. However, if the columns were equal then it might have been possible.
0
 

Author Comment

by:sathyabalaji
ID: 35224643
Dear Akenathon:

We are doing all comparision locally only

ORA-04030 is Error

Oracle version is 10g

We have tried to solve the issues using the following link
http://www.dba-oracle.com/t_ora_04030_out_process_memory.htm

we have tried
ulimit to unlmited on memory, Data in unix envt

our AIX version 6

Regards

Sathya Narayanan
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 250 total points
ID: 35235014
There are many reasons for a process to go out of memory and fail to get more. Not only the error number is important, the error text is also needed to narrow the causes. Please see here for a good troubleshooting guide, but do not expect any quick answers like "change this parameter", there are no silver bullets for this one.

Now, instead of striving to solve the error, why don't you find a workaround? Try to do it in SQL instead of PL/SQL, rewrite your code using another approach, rethink whether you even need to verify that what you have just written is still there, etc.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now