changing Oracle global schema name and re-generate public synonyms

Jephe Wu - http://linuxtechres.blogspot.com

Environment:  global schema contains some database objects such as tables, procedures, packages etc which are shared by each individual client schemas, and now we are going to change global schema name from global1 to global2.

We are using public synonyms for global schema so that each individual client schema can directly use the global schema objects name.

Objective: changing global schema name from global1 to global2 and repointing all public synonyms from global1 to global2.


Steps:
1.  check with another article regarding how to duplicate a schema at
http://linuxtechres.blogspot.com/2009/12/how-to-copy-whole-schema-from-one.html

2. generating the existing public synonyms that are related to the schema global1

cat > /u01/cronjob/public_synonym_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/public_synonym.sql;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' from dba_synonyms where table_owner in ('GLOBAL1') and owner='PUBLIC';
spool off;
exit;
END
sqlplus / as sysdba @/u01/cronjob/public_synonym_generation.sql 

after that, vi /u01/cronjob/public_synonym.sql, to change global1 to global2 (:%s#GLOBAL1\.#GLOBAL2\.#g)

sqlplus / as sysdba @/u01/cronjob/public_synonym.sql

You can also use the following script to generate all public synonyms for global schema after schema copy

cat > /u01/cronjob/public_synonym_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/public_synonym.sql;
select 'create or replace public synonym '||object_name||' for '||owner||'.'||object_name||';' from dba_objects where owner in
 ('GLOBAL2') and object_type in ('TABLE','PROCEDURE','FUNCTION','VIEW','PACKAGE','SEQUENCE');
select 'exit;' from dual;
spool off;
exit;
END

3. re-generating grant scripts from the global schema
cat > /u01/cronjob/grant_execute_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/grant_execute.sql;
select 'grant execute on '||object_name||' to public;' from dba_objects where owner in ('GLOBAL1') and object_type in ('PROCEDURE ','PACKAGE','FUNCTION');
select 'exit;' from dual;
spool off;
exit;
END

sqlplus / as sysdba @/u01/cronjob/grant_execute_generation.sql
sqlplus / as sysdba @/u01/cronjob/grant_execute.sql

cat > /u01/cronjob/grant_all_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/grant_all.sql;
select 'grant all on '||object_name||' to public;' from dba_objects where owner in ('GLOBAL1') and object_type in ('TABLE','VIEW' ,'SEQUENCE');
select 'exit;' from dual;
spool off;
exit;
END

sqlplus / as sysdba @/u01/cronjob/grant_all_generation.sql
sqlplus / as sysdba @/u01/cronjob/grant_all.sql




4. drop global1 schema
drop tablespace global1 including contents and datafiles;
drop user global1 cascade;


5. drop client schema synonyms that are related to global1 schema (otherwise, it will take priority so the same name of public synonyms will still be pointing to old schema global1)


set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/drop_user_synonyms.sql;
select 'drop synonym '||synonym_name||';' from user_synonyms where table_owner in ('GLOBAL1');
select 'exit;' from dual;
spool off;
exit;


note:
If you encountered error "ORA-00980:synonym translation is no longer valid" after re-pointing all public synonyms to the new global schema. You should check your each client schema synonyms, there might be some synonyms in client schema itself pointing to old global schema global1.