其中假设数据提取原用户为:occ
新增的用户名称:db_tableau
--创建用户
create user db_tableau Identified By 123
default tablespace occ_data
temporary tablespace occ_data_temp;
grant connect to db_tableau ;
grant create synonym to db_tableau;
grant create session to db_tableau;
--1.查询所有要更新的表-赋权
select 'grant select on '||owner||'.'||object_name||' to db_tableau;'
from dba_objects
where owner in ('OCC') and object_type='TABLE';
--2.更新的表
SELECT 'create or replace SYNONYM db_tableau.' || object_name|| ' FOR ' || owner || '.' || object_name || ';' from dba_objects
where owner in ('OCC') and object_type='TABLE';
--3.查询所有要更新的视图-赋权
select 'grant select on '||owner||'.'||object_name||' to db_tableau;'
from dba_objects
where owner in ('OCC') and object_type='VIEW';
--4.更新的视图
SELECT 'create or replace SYNONYM db_tableau.' || object_name|| ' FOR ' || owner || '.' || object_name || ';' from dba_objects
where owner in ('OCC') and object_type='VIEW';