迁移oracle数据库用来比对权限和对象数的脚本参考

新库创建dblink到源库:

CREATE public DATABASE LINK old_db CONNECT TO system IDENTIFIED BY "password" USING '192.168.52.18:1521/olddb'

实际用户条件：

owner not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSMAN','XS$NULL') 

检查对象类型及数量:–检查最后diff列，理想结果都为0

with usr as (
select old.username un
  from dba_users@old_db
 where old.created > date'2024-01-18'
)
,xd as (
select owner, object_type,count(object_name) cnt_obj
  from dba_objects obj,usr 
 where obj.owner(+)=usr.un
 group by owner, object_type
)
,old as (
select owner, object_type,count(object_name) cnt_obj
  from dba_objects@old_hisdb obj,usr 
 where obj.owner=usr.un
 group by owner, object_type
)
select old.owner, old.object_type, abs(old.cnt_obj-xd.cnt_obj) diff
  from xd, old
 where xd.owner(+)=old.owner and xd.object_type(+)=old.object_type
 ;

检查用户权限：–如果权限相同，则无记录

with usr as (
select old.username un
  from dba_users@old_db
 where old.created > date'2024-01-18'
)
--with usr as (select 'SOE' un from dual)
,val_x as (
SELECT granted_role priv , grantee, null as table_owner, null as table_name, null as ts_quota
  FROM dba_role_privs a,usr
 WHERE grantee = usr.un
UNION ALL
SELECT privilege priv, grantee, null as table_owner, null as table_name, null as ts_quota
  FROM dba_sys_privs a,usr 
 WHERE grantee = usr.un
UNION ALL
SELECT privilege priv, grantee, owner table_owner, table_name, null as ts_quota
  FROM dba_tab_privs a,usr
 WHERE grantee = usr.un 
UNION ALL
 SELECT null,username, null,null,
        'QUOTA ' || DECODE(max_bytes, -1, 'UNLIMITED', max_bytes) ||' ON ' || tablespace_name || ';' || DECODE(max_bytes, -1, '', '-- ' || ROUND(max_bytes/1024/1024) || 'M') 
        as ts_quota
   FROM dba_ts_quotas a,usr
  WHERE username = usr.un
    AND dropped = 'NO' 
) 
,val_o as (
SELECT granted_role priv , grantee, null as table_owner, null as table_name, null as ts_quota
  FROM dba_role_privs@old_hisdb a,usr
 WHERE grantee = usr.un
UNION ALL
SELECT privilege priv, grantee, null as table_owner, null as table_name, null as ts_quota
  FROM dba_sys_privs@old_hisdb a,usr 
 WHERE grantee = usr.un
UNION ALL
SELECT privilege priv, grantee, owner table_owner, table_name, null as ts_quota
  FROM dba_tab_privs@old_hisdb a,usr
 WHERE grantee = usr.un 
UNION ALL
 SELECT null,username, null,null,
        'QUOTA ' || DECODE(max_bytes, -1, 'UNLIMITED', max_bytes) ||' ON ' || tablespace_name || ';' || DECODE(max_bytes, -1, '', '-- ' || ROUND(max_bytes/1024/1024) || 'M') 
        as ts_quota
   FROM dba_ts_quotas@old_hisdb a,usr
  WHERE username = usr.un
    AND dropped = 'NO' 
)
, xd as (
select --+ no_merge
       grantee, decode(priv,'',ts_quota,priv) as priv, TABLE_OWNER||'.'||TABLE_NAME as obj_priv
  from val_x order by 1,2,3
)
, old as (
select --+ no_merge
       grantee, decode(priv,'',ts_quota,priv) as priv, TABLE_OWNER||'.'||TABLE_NAME as obj_priv
  from val_o order by 1,2,3
)
select old.grantee as T4_grantee, old.priv as T4_priv, old.obj_priv as T4_obj_priv,
        xd.grantee as xd_grantee,  xd.priv as xd_priv,  xd.obj_priv as xd_obj_priv
  from old
  full outer join xd
    on old.grantee=xd.grantee
   and old.priv=xd.priv
   and old.obj_priv=xd.obj_priv
 where old.grantee is null 
    or xd.grantee is null 
;

提取建用户和权限的脚本参考：

set serveroutput on
DECLARE
    --多个用户使用 ; 隔开
    v_users VARCHAR2(1000) := UPPER('JYC1;JYC2');
    v_sqls VARCHAR2(32767);
BEGIN
    dbms_output.enable(9999999);
    FOR x IN (SELECT regexp_substr(v_users, '[^;]+', 1, LEVEL) u
                FROM dual
              CONNECT BY LEVEL <= LENGTH(v_users) - LENGTH(REPLACE(v_users, ';')) + 1
             ) LOOP
         dbms_output.put_line(CHR(10));
         dbms_output.put_line('--------------------------------------------------------');
         dbms_output.put_line('--用户'||x.u||'脚本');
         dbms_output.put_line('--创建用户');
         SELECT to_char(dbms_metadata.get_ddl('USER',x.u)) INTO v_sqls FROM dual;
         dbms_output.put_line(v_sqls||';');
         dbms_output.put_line('--系统权限');
         SELECT to_char(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',x.u)) INTO v_sqls from dual;
         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));
         dbms_output.put_line('--角色权限');
         SELECT to_char(dbms_metadata.get_granted_ddl('ROLE_GRANT',x.u)) INTO v_sqls  from dual;
         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));
         dbms_output.put_line('--对象权限');
         SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT',x.u) INTO v_sqls from dual;
         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception!');
END;
/