How to compare two db

Create a system db_link to the other db

create database link PVG.WORLD
connect to SYSTEM
identified by BOLINA
using 'PVGG1.WORLD';

Create the following views to monitor tables presence

create view ch_tables_plus_in_ext as  
SELECT a.*, 'Table in external db and not here' "DESCRIPTION"
    FROM (SELECT "OWNER", table_name
              FROM dba_tables@pvg.world
         MINUS
         SELECT "OWNER", table_name
             FROM dba_tables) a;

create view ch_tables_plus_here as  
SELECT a.*, 'Table here and not in external db' "DESCRIPTION"
    FROM (SELECT "OWNER", table_name
              FROM dba_tables
         MINUS
         SELECT "OWNER", table_name
             FROM dba_tables@pvg.world) a;

Create the following view to monitor indexes presence

create view ch_indexes_plus_in_ext as
select a.*, 'Indexes in external db and not here' "DESCRIPTION"
from (
	select owner, index_name from dba_indexes@pvg.world
	where not table_owner||'.'||table_name in (select owner||'.'||table_name from ch_tables_plus_in_ext)
	minus
	select owner, index_name from dba_indexes
) a;

create view ch_indexes_plus_here as
select a.*, 'Indexes here and not in external db' "DESCRIPTION"
from (
	select owner, index_name from dba_indexes
	where not table_owner||'.'||table_name in (select owner||'.'||table_name from ch_tables_plus_here)
	minus
	select owner, index_name from dba_indexes@pvg.world
) a;

Create the following views to monitor roles presence

create view ch_roles_plus_in_ext as  
SELECT a.*, 'Roles in external db and not here' "DESCRIPTION"
    FROM (SELECT role
              FROM dba_roles@pvg.world
         MINUS
         SELECT role
             FROM dba_roles) a;

create view ch_roles_plus_here as  
SELECT a.*, 'Roles here and not in external db' "DESCRIPTION"
    FROM (SELECT role
              FROM dba_roles
         MINUS
         SELECT role
             FROM dba_roles@pvg.world) a;

Create the following views to monitor roles granted presence

CREATE or replace VIEW CH_ROLES_GRANTED_PLUS_IN_EXT AS 
select a.*, 'Role granted in external db and not here' "DESCRIPTION"
from (
	select grantee, granted_role from dba_role_privs@pvg.world
	where not grantee||'.'||granted_role in (select grantee||'.'||granted_role from ch_roles_plus_in_ext)
	minus
	select grantee, granted_role from dba_role_privs
) a;

create or replace view ch_roles_granted_plus_here as
select a.*, 'Role granted here and not in external db' "DESCRIPTION"
from (
	select grantee, granted_role from dba_role_privs
	where not grantee||'.'||granted_role in (select grantee||'.'||granted_role from ch_roles_plus_here)
	minus
	select grantee, granted_role from dba_role_privs@pvg.world
) a;

Select

select * from (
	select * from ch_tables_plus_in_ext
	union all
	select * from ch_tables_plus_here
) a
where owner in ('STORICO');

select * from (
	select * from ch_indexes_plus_in_ext
	union all
	select * from ch_indexes_plus_here
)
where owner in ('STORICO');

select * from (
	select * from ch_roles_plus_in_ext
	union all
	select * from ch_roles_plus_here
);

select * from (
	select * from ch_roles_granted_plus_in_ext
	union all
	select * from ch_roles_granted_plus_here
);

Drop created objects

drop view ch_tables_plus_in_ext;

drop view ch_tables_plus_here;