Solved

Comparing 2 tables for Data Migration

Posted on 2011-03-23
4
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

623 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