Solved

Comparing 2 tables for Data Migration

Posted on 2011-03-23
4
306 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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

11 Experts available now in Live!

Get 1:1 Help Now