当前登录用户为CM,DBA权限。CREATE USER u_a IDENTIFIED BY his
PROFILE DEFAULT
/
GRANT DBA TO u_a
/
GRANT RESOURCE TO u_aCREATE USER u_b IDENTIFIED BY his
PROFILE DEFAULT
/
GRANT DBA TO u_b
/
GRANT RESOURCE TO u_bcreate table u_a.Table_a(
IDN NUMBER(10) not null,
CODE NVARCHAR2(16) not null,
NAME NVARCHAR2(64) not null);
create table u_b.Table_b(
IDN NUMBER(10) not null,
CNAME NVARCHAR2(64) not null);
insert into u_a.table_a select 1, '01', 'name01' from dual;insert into u_a.table_a select 2, '02', 'name02' from dual;insert into u_b.table_b select 1, 'bname01' from dual;insert into u_b.table_b select 2, 'bname02' from dual;select * from u_a.table_a;
select * from u_b.table_b;//这个SELECT没问题。
select a.idn UA_TA_idn, a.code UA_TA_code, a.name UA_TA_name,
b.idn UB_TB_idn, b.CNAME UB_TB_name
from u_a.table_a a left join u_b.table_b b on a.idn = b.idn;//将SELECT结果创建为视图,提示u_b.table_b b 表不存在。
create or replace view u_a.View1
as
select a.idn UA_TA_idn, a.code UA_TA_code, a.name UA_TA_name,
b.idn UB_TB_idn, b.CNAME UB_TB_name
from u_a.table_a a left join u_b.table_b b on a.idn = b.idn;drop user u_a CASCADE;
drop user u_b CASCADE;
当前用户建立自己的视图可以用到其他用户的表,只要你有权限;
当前用户建立其他用户的视图,只能用到那个用户的表
所以楼主用u_a登陆建立那个视图就可以了。