oracle用户迁移

-- 创建用户脚本

set serveroutput on
set line 100
set pagesize 0
declare
cursor c1 is select username from dba_users a  where a.account_status='OPEN' ;
v_user c1%rowtype;
begin
open c1;
loop
   fetch c1 into  v_user ;
   exit when c1%notfound;
   DBMS_OUTPUT.put_line(to_char(dbms_metadata.get_ddl('USER',v_user.username))||';');
  end loop;
  close c1;
  end;
  /




---授权系统权限脚本
select 'grant '||t.privilege ||' to '||t.grantee || decode(t.admin_option,'YES',' with admin;',';')
from dba_sys_privs t
where  t.grantee  not in
('SPATIAL_WFS_ADMIN_USR',
'DIP'
);




--授角色脚本


select  'grant '||granted_role||' to '||grantee||';' from dba_role_privs
where grantee not  in
('SPATIAL_WFS_ADMIN_USR',
'DIP',
'MDDATA'
');

-- 表权限

select 'grant '||t.privilege||' on '||t.grantor||'.'||t.table_name||' to '||t.grantee||decode(t.grantable,'YES',' with grant;',';')
from dba_tab_privs t
where t.grantor not in
('SYS','SYSTEM','DBSNMP','PERFSTAT','OUTLN','WMSYS','CONNECT','DBA',
'EXP_FULL_DATABASE','IMP_FULL_DATABASE','OEM_MONITOR','RESOURCE','JAVADEBUGPRIV')
order by t.grantee
/