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

bibi92
bibi92 used Ask the Experts™
on
Hello,

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

Thank you

Best regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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

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

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
Most Valuable Expert 2012
Distinguished Expert 2018
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial