Oracle批量创建同义词
2019-10-24 17:28 williamzheng 阅读(3244) 评论(0) 编辑 收藏 举报一、介绍
Oracle的同义词(synonyms)从字面上理解就是别名的意思,和视图的功能类似,就是一种映射关系。它可以节省大量的数据库空间,对不同用户的操作同一张表没有多少差别;它扩展了数据库的使用范围,能够在不同的数据库用户之间实现无缝交互。
很多时候,会有多个oracle账号访问一个账号下的表,比如:USER_A用户下面有很多表,相关的sql语句都是USER_A.Tablename等等等,这时候需要用USER_B用户去访问USER_A的表,但是sql语句还要保持USER_A.Tablename,就需要用到同义词了
同义词分类:
Oracle同义词有两种类型,分别是Oracle公用同义词与Oracle私有同义词。普通用户创建的同义词一般都是私有同义词,公有同义词一般由DBA创建,普通用户如果希望创建同义词,则需要CREATE PUBLIC SYNONYM这个系统权限。
-
Oracle公用同义词:由一个特殊的用户组Public所拥有。顾名思义,数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象,这些对象往往大家都需要引用。
-
Oracle私有同义词:它是跟公用同义词所对应,他是由创建他的用户所有。当然,这个同义词的创建者,可以通过授权控制其他用户是否有权使用属于自己的私有同义词。
二、创建同义词
语法:
CREATE [OR REPLACE] [PUBLIC] SYNONYM [ schema.] 同义词名称 FOR [ schema.] object [ @dblink ];
专有(私有)同义词
CREATE SYNONYM SYSN_TEST FOR TEST;
公共同义词
CREATE PUBLIC SYNONYM PUBLIC_TEST FOR TEST;
2.1首先需要sys用户授权USER_B创建同义词权限
其中USER_B是需要创建同义词的用户
GRANT CREATE SYNONYM TO USER_B;
这个时候还需要一个权限就是USER_B用户select USER_A用户table的权限,这个时候是在USER_A用户下赋值的
grant select on tableA to user_temp;
2.2创建同义词
create synonym USER_B.Tablename for USER_A.Tablename;
2.3测试同义词
使用USER_B用户oracle,有输出结果代表可以在USER_B用户下以USER_B的schema查看USER_A的表
select count(*) from USER_B.Tablename;
三、批量创建同义词
很多时候,当前环境下的表有几百张甚至几千张,很多人说可以select * from dba_tables where owner='UPCENTER' 把表名查出来,用notpad拼成命令,批量执行,这里是行不通的,因为每条命令的行尾都不一样(表名不同)
这时候可以先用notpad软件把USER_B用户select USER_A用户table的权限批量执行,然后再执行下面的命令,把输出结果执行以下即可:
select 'create synonym USER_B.'||table_name||' for upcenter.'||table_name||';' from dba_tables where owner='USER_A'
四、查看同义词和删除同义词
4.1查看用户下的同义词
SELECT * FROM DBA_SYNONYMS where OWNER = 'UPAPP';
4.2删除同义词
DROP SYNONYM SYSN_NAME;