迁移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
;