需要管理员权限
create public synonym table_name for user.table_name; (公有)
create public synonym user1.table_name for user.table_name; (指定给user1)
GRANT CREATE SYNONYM TO IWWAPP2; 创建私有同义词赋权(选择schema)
GRANT CREATE ANY SYNONYM TO IWWAPP2; --创建私有同义词赋权(选择schema)
(user下的表赋给user1)
begin
for arr in (
select ‘create or replace synonym user1.’ || b.OBJECT_NAME || ’ for ’ || B.OWNER || ‘.’ ||
B.OBJECT_NAME row_i from dba_objects B where OWNER = ‘user’ AND object_type = ‘TABLE’
) loop
begin
execute immediate (arr.row_i);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(arr.row_i);
end;
end loop;
end;
SELECT ‘create or replace SYNONYM user1.’ || object_name|| ’ FOR ’ || owner || ‘.’ || object_name|| ‘;’ from dba_objects where owner in (‘user’) and object_type=‘TABLE’;
12C(需要切换到当前pdb容器)
show con_name; --当前cdb容器
alter session set container=CDB
R
O
O
T
;
C
D
B
ROOT; CDB
ROOT;CDBROOT
select con_id,dbid,guid,name,open_mode from v$pdbs; --查询pdb容器
alter session set container=ORCLPDB; --切换到pdb容器(sysdba级别权限)
select username,con_id,common from cdb_users where common=‘NO’;