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

Hello,

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

Thank you

Best regards
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What do you mean by "number of lines"?  Do you mean number of rows?
slightwv (䄆 Netminder) 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 DBACommented:
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.
bibi92Author 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
slightwv (䄆 Netminder) Commented:
>>but the result is not correct formatted.

Sorry but I don't know what that means.

Here is a complete test case based on the SQL from the link above.  I created users just for the test.

Setup:
/*
--my sample setup:  Create two users give them a table and some rows
drop user user1 cascade;
drop user user2 cascade;

create user user1;
create user user2;

alter user user1 quota 1M oN users;
alter user user2 quota 1M oN users;

create table user1.tab1(col1 char(1));
create table user1.tab2(col1 char(1));
create table user2.tab1(col1 char(1));

insert into user1.tab1 values('a');
insert into user1.tab1 values('b');
insert into user1.tab1 values('c');

insert into user1.tab2 values('a');

insert into user2.tab1 values('a');

analyze table user1.tab1 compute statistics;
analyze table user1.tab2 compute statistics;
analyze table user2.tab1 compute statistics;
commit;
*/

Open in new window


Query:
with mycounts as (
	select owner, table_name,
	to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '|| owner || '.' || table_name))
          	,'/ROWSET/ROW/X')) count
	from dba_tables where owner in ('USER1', 'USER2')
)
select * from mycounts
	pivot (
		sum(count) count
		for owner in ('USER1' as user1, 'USER2' as user2)
	)
/

Open in new window



My output:
TABLE_NAME                     USER1_COUNT USER2_COUNT
------------------------------ ----------- -----------
TAB1                                     3           1
TAB2                                     1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.