Useful shortcuts for vi editor

Friday, 3 February 2017

Dynamic sql script for synonyms

Define new synonyms:
SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';

SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%ALPER2%' and table_owner like '%ALPER%'

Drop synonyms:
SQL>
select 'drop public synonym ' || synonym_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';