upgrade migration - liamlamth/blog GitHub Wiki

drop table migrate_roles;
drop table migrate_tables;
drop table migrate_db_links;
drop table migrate_sys_privs;
drop table migrate_role_privs;
drop table migrate_tab_privs;
drop table migrate_objects;
drop table migrate_indexes;
drop table migrate_lobs;
drop table migrate_sequences;
drop table migrate_directories;
drop table migrate_scheduler_jobs;
drop table migrate_policies;

alter pluggable database pdb_name close immediate;
drop pluggable database pdb_name including datafiles;

-----------------------------------------------------------------------------------------------------------
select a.role mg_role, b.role 
from   migrate_roles a
full outer join dba_roles b on a.role = b.role
where  greatest(a.role, b.role) is null;
-----------------------------------------------------------------------------------------------------------
select * 
from  (select table_name mg_table_name, tablespace_name mg_tablespace_name, num_rows mg_num_rows
       from migrate_tables
       where owner='APPOWNER') a
full outer join 
      (select table_name, tablespace_name, num_rows 
       from dba_tables
       where owner='APPOWNER') b on mg_table_name = table_name
where  greatest(mg_table_name, table_name) is null
or 	 mg_num_rows <> num_rows
or     mg_tablespace_name <> tablespace_name;
-----------------------------------------------------------------------------------------------------------
select a.owner mg_owner, a.db_link mg_db_link, a.username mb_username, a.host mb_host,
       b.owner, b.db_link, b.username, b.host
from   migrate_db_links a
full outer join dba_db_links b on a.owner=b.owner
                              and a.db_link=b.db_link
                              and a.username=b.username
                              and a.host=b.host
-----------------------------------------------------------------------------------------------------------
select a.grantee mg_grantee, a.privilege mg_privilege,
       b.grantee, b.privilege
from   migrate_sys_privs a
full outer join dba_sys_privs b on a.grantee=b.grantee and a.privilege=b.privilege
where  greatest(a.privilege, b.privilege) is null
order by a.grantee, a.privilege, b.grantee;

select a.grantee mg_grantee, a.granted_role mg_granted_role,
       b.grantee, b.granted_role
from   migrate_role_privs a
full outer join dba_role_privs b on a.grantee=b.grantee and a.granted_role=b.granted_role
where greatest(a.granted_role, b.granted_role) is null
order by a.grantee, a.granted_role, b.grantee;

select *
from  (select grantee mg_grantee, owner mg_owner, table_name mg_table_name, privilege mg_privilege
       from migrate_tab_privs
       where grantee in ('APPUSER_A', 'APPUSER_B')) a
full outer join 
      (select grantee, owner, table_name, privilege
       from dba_tab_privs
       where grantee in ('APPUSER_A', 'APPUSER_B')) b on mg_grantee=grantee 
	                                             and mg_owner=owner 
	                                             and mg_table_name=table_name 
	                                             and mg_privilege=privilege
where  greatest(mg_table_name, table_name) is null;
-----------------------------------------------------------------------------------------------------------
select a.*, b.*, 'alter '||object_type||' '||owner||'.'||object_name||' compile;' action
from  (select owner mg_owner, object_name mg_object_name, object_type mg_object_type, status mg_status
       from   migrate_objects
       where  owner in ('APPUSER_A', 'APPUSER_B')) a
full outer join 
      (select owner, object_name, object_type, status
       from   dba_objects
       where  owner in ('APPUSER_A', 'APPUSER_B')) b on mg_owner=owner 
	                                            and mg_object_name = object_name 
	                                            and mg_object_type=object_type
where  greatest(mg_object_name, object_name) is null
or     mg_status <> status;

QUICK FIX:
@$ORACLE_HOME/rdbms/admin/utlrp.sql

declare 
    cursor c1 is 
        SELECT object_type,owner,object_name FROM DBA_objects where status = 'INVALID';
begin
    for c1_rec in c1 loop
        if c1_rec.owner='PUBLIC' and c1_rec.object_type='SYNONYM' then
            execute immediate 'alter PUBLIC SYNONYM "'||c1_rec.object_name||'" compile';
        elsif c1_rec.object_type='PACKAGE BODY' then
            execute immediate 'alter PACKAGE "'||c1_rec.owner||'"."'||c1_rec.object_name||'" compile';
        elsif c1_rec.object_type='TYPE BODY' then
            execute immediate 'alter TYPE "'||c1_rec.owner||'"."'||c1_rec.object_name||'" compile';
        else 
            execute immediate 'alter '||c1_rec.object_type||' "'||c1_rec.owner||'"."'||c1_rec.object_name||'" compile';
        end if;
    end loop;
end;
/
-----------------------------------------------------------------------------------------------------------
select 'create synonym '||OWNER||'.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||TABLE_NAME||';' from migrate_synonyms where table_owner = 'APPOWNER';
-----------------------------------------------------------------------------------------------------------
select *
from  (select table_name mg_table_name, count(index_name) mg_index_count
       from migrate_indexes
       where owner = 'APPOWNER'
       group by owner, table_name) a
full outer join 
      (select table_name, count(index_name) index_count
       from dba_indexes
       where owner = 'APPOWNER'
       group by owner, table_name) b on mg_table_name = table_name
where  mg_index_count <> index_count 
or     greatest(mg_table_name, table_name) is null;
-----------------------------------------------------------------------------------------------------------
select 'migrate_lobs', count(*) from migrate_lobs where owner='APPOWNER'
union all
select 'dba_lobs', count(*) from dba_lobs where owner='APPOWNER';
select 'migrate_sequences', count(*) from migrate_sequences where sequence_owner='APPOWNER'
union all
select 'dba_sequences', count(*) from dba_sequences where sequence_owner='APPOWNER';

select * from dba_directories