考官方文档:How To Fix Missing Responsibilities For A User (Doc ID 429852.1)
以下仅列出此次处理使用到的脚本,具体完整的可参考官方文档
--1.职责失效后,再次启用后,登录时看不到已启用后的职责
select ura.user_name,
ura.role_name,
ur.effective_start_date,
ur.effective_end_date,
ur.expiration_date,
ura.effective_start_date,
ura.effective_end_date,
ura.end_date
from wf_local_user_roles ur, wf_user_role_assignments ura
where 1 =1
and ur.user_namelike '&P_USER_NAME' ||'%'
and ur.user_name = ura.user_name
and ur.role_name = ura.role_name
and ura.relationship_id = -1
and ((ur.effective_start_dateis null or
ur.effective_start_date <> ura.effective_start_date)or
(ur.effective_end_date is null or
ur.effective_end_date <> ura.effective_end_date));
--2.查看end_date
select a.role_end_date, a.effective_end_date, a.*
from wf_local_user_roles a
where user_namelike '&P_USER_NAME' ||'%'
and role_name ='&P_ROLE_NAME';
--3.更新同步
update wf_local_user_roles
set EFFECTIVE_END_DATE =null
where user_namelike '&P_USER_NAME' ||'%'
and role_name ='&P_ROLE_NAME'
and EFFECTIVE_END_DATEis not null;
--1.职责失效后,再次启用后,登录时看不到已启用后的职责
select ura.user_name,
ura.role_name,
ur.effective_start_date,
ur.effective_end_date,
ur.expiration_date,
ura.effective_start_date,
ura.effective_end_date,
ura.end_date
from wf_local_user_roles ur, wf_user_role_assignments ura
where 1 =1
and ur.user_namelike '&P_USER_NAME' ||'%'
and ur.user_name = ura.user_name
and ur.role_name = ura.role_name
and ura.relationship_id = -1
and ((ur.effective_start_dateis null or
ur.effective_start_date <> ura.effective_start_date)or
(ur.effective_end_date is null or
ur.effective_end_date <> ura.effective_end_date));
--2.查看end_date
select a.role_end_date, a.effective_end_date, a.*
from wf_local_user_roles a
where user_namelike '&P_USER_NAME' ||'%'
and role_name ='&P_ROLE_NAME';
--3.更新同步
update wf_local_user_roles
set EFFECTIVE_END_DATE =null
where user_namelike '&P_USER_NAME' ||'%'
and role_name ='&P_ROLE_NAME'
and EFFECTIVE_END_DATEis not null;