Hi,
Is compare the number of table rows between 2 Oracle schemas a missing feature of the Database-->compare Menu?
No.
Schema compare can compare number of rows using either table statistics (fast but not 100% accurate) or by running a "select count(*)" on each table (accurate but might be slow if your tables are large)
If you want to compare all of the data in one table to another table, use Compare Data. If you want to compare all of the data in all (or some) of the tables in an entire schema to all of the tables in another schema, use Compare Multiple Tables.
Great! I didnt notice that it was there on the left side. I''l try it. Thanks very much.
I compared 2 schemas with about 800 tables each.
I set the option of compare row count with select count(*).
-
It worked but what I expected is to get the row count value for each different table. But I only got the fact that the count is different. Is it possible to get the count value?
-
In addition: will the sync script include DML statements to ensure that the tables with different record counts become identical?
- No.
- No. Schema Compare doesn't sync table data. It only syncs table structure. If you want table data to be synchronized, use "Compare Multiple Tables".
In the LHS tables main tree menu, is the Num Rows column the statistics value or true value?
If you mean this, then the answer is Stastics. If you mean somewhere else, please post a screen shot.
What I need is to compare tables from two schemas and, for tables that have a different number of records, display the record count for each one.
The summary report for compare multiple tables includes that information, but it also compares all of the data in each table, so it may take longer than necessary for what you are after, and it also will include information that it sounds like you are not interested in. Toad doesn't have a built in way to just report num row differences and that's all because even if all tables have the same number of rows, you still don't know if the rows match or not.
you can get that report like this:
The report looks like this (I added the highlights, they don't come in the report):
But if you don't mind using statistics instead of row counts, you only need a simple SQL to get the info you are looking for:
select a1.table_name, a1.num_rows, a2.num_rows
from all_tables a1, all_tables a2
where a1.owner = 'first schema name'
and a2.owner = 'second schema name'
and a1.table_name = a2.table_name
and a1.num_rows <> a2.num_rows;