Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Comparing 2 tables for Data Migration

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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