We help IT Professionals succeed at work.

script to compare number of lines on all tables between two schémas

bibi92
bibi92 asked
on
86 Views
Last Modified: 2018-11-12
Hello,

I search a script to compare number of lines on all tables between two schémas.

Thank you

Best regards
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What do you mean by "number of lines"?  Do you mean number of rows?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If you mean number of rows, you can use the SQL here:
https://www.experts-exchange.com/questions/24170132/List-tables-with-count-of-rows-in-Oracle-database.html#a23716743

Just run it for each schema and diff the results.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Are your statistics up to date?  If they are, the dictionary is an easy way to check.
SELECT owner1.table_name, 
       owner1.table, 
       owner1.num_rows, 
       owner2.num_rows 
FROM   dba_tables owner1 
       join dba_tables owner2 
         ON owner1.table_name = owner2.table_name 
WHERE  owner1.owner = 'USER1' 
       AND owner2.owner = 'USER2'; 

Open in new window

I just typed that free hand and didn't try it, but it should be close.

Author

Commented:
Hello,

I mean number of rows. I try already the post of slightwv but the result is not correct formatted.

Thank you

Best regards
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION