oracle 创建同义词

在需要使用同义词的用户执行下列义语句

create public synonym SYS_PARA for POC.SYS_PARA;
create public synonym SYS_DATADICT_LIST for POC.SYS_DATADICT_LIST;
create public synonym SYS_DATADICT for POC.SYS_DATADICT;

 

poc库为表拥有者的库;(需要POC库赋权给当前用户)

SELECT * FROM SYS.ALL_SYNONYMS t WHERE t.owner in ('用户名')

如果操作错误执行以下语句删除同义词

drop public synonym SYS_PARA;
drop public synonym SYS_DATADICT_LIST;
drop public synonym SYS_DATADICT;

 

--查找不生效的同义词

select 'drop '
       || decode(s.owner,
                 'PUBLIC',
                 'public synonym ',
                 'synonym ' || s.owner || '.')
       || s.synonym_name
       || ';' as "Dropping invalid synonyms:"
  from dba_synonyms s
 where table_owner not in ('SYSTEM', 'SYS')
   and db_link is null
   and not exists
 (select null
          from dba_objects o
         where s.table_owner = o.owner
           and s.table_name = o.object_name);