?
Solved

Comparing 2 tables for Data Migration

Posted on 2011-03-23
4
Medium Priority
?
327 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

589 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