Hello,
I have the following situation: I have two identical schemas regarding the objects in those schemas. But the data in the tables differ.
I need to compare the two schemas and the export the difference as insert statements.
I am using Toad for Oracle Xpert edition, and I am unable to figure out how to do it. The schema compare feature does not include the data in the tables comparison, it only compares at object level. And the Data Compare feature can only compare for one table at a time. My schemas have hundreds of tables, I can’t do it manually one at a time, I need to do the whole compare in one go.
Is this possible?
Thank you,
Claudiu
Claudiu,
Maybe you are not aware but data sync is something that it is really hard to achieve:
- parent tables must bu updated first
1 a) duplicate (primary key) and “obsolete” records should be deleted before
-
Deleting parent records is possible only in a case if cascade delete is active.
-
In all other cases you must first delete child records … who can also be someones parent … and so on and so on. Here is order very important (and someone might go outside of active schema!!).
What I suggest you is:
-
Use toad for non all data based compare
-
then try to compare data in a way:
set serveroutput on size 1000000;
for i in select table_name from dba_tables where owner=‘SOURCE’ loop
dbms_output.put_line(’ select * from SOURCE.’ ||’"’||i.table_name || ‘" minus select * from target.’||’"’||i.table_name ||’" ;’ );
end loop;
and then execute all generated statements. If there are too many differences-use export and import of the whole schema.
Do not forget that schemas may have dependency on data from other schemas as well!
Hope did explain and answered your question.
Brg
Damir
Hi Damir,
Thank you for your reply.
EDITED: I found exactly what I needed using Toad for Data Analysts. The compare feature there is exactly what I described I need in my initial post.
Claudiu
Here is a way to generate one table synchro script in Toad Xpert (what I know it exsts).
Database|Compare|Data
Maybe you can automatize that with Toad Automation tool (using table names as variables get from lop like shown) variables but in target schema tables must exists … nice challenge for any Toad user.
That window can’t be executed by Toad’s automation designer. I would probably enjoy making it able to do that, and then create some wrapper so it would run against every table in the DB. Honestly though, the fastest sync that you could probably do would be a data pump export from the source schema and then import to the target schema. Of course that depends on the number of rows in the tables, and the number of rows that differ, but generally, I think Data Pump will be fastest.
John, this is why I wrote last senence “nice challenge for any Toad user.”
