OIM 10g 完全删除一个帐号

最新推荐文章于 2024-08-15 10:11:35 发布

原创最新推荐文章于 2024-08-15 10:11:35 发布 · 774 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#login #delete #user #table #c

IAM-Oracle IdM 专栏收录该内容

18 篇文章

订阅专栏

本文提供了一段PL/SQL代码示例，展示了如何从数据库中逐个移除以T11111开头的所有用户及其相关信息。该过程首先统计符合前缀条件的用户总数，然后逐一删除这些用户的关联数据并最终移除用户本身。

How to remove all users whose USR_LOGIN beginning with "T11111" from USR table individually?

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

   z_usr_login USR.USR_LOGIN%TYPE;
   z_total NUMBER;
   CURSOR c_user IS SELECT USR_LOGIN from USR where USR_LOGIN like 'T11111%' order by USR_LOGIN;

BEGIN

   select count(*) into z_total from USR where USR_LOGIN like 'T11111%';

   IF z_total != 0 THEN
      DBMS_OUTPUT.PUT_LINE('Starting to delete ' || z_total || ' T11111X Users ... ');
      DBMS_OUTPUT.PUT_LINE('----- ----- ----- ----- -----');

OPEN c_user;

FOR counter in 1..z_total
LOOP

            FETCH c_user INTO z_usr_login;
            delete from oiu where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from oio where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from osi where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcd where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rch where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcu where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rcb where rce_key in (select rce_key from rce,orc,usr where rce.orc_key = orc.orc_key and orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from rce where orc_key in (select orc_key from orc,usr where orc.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from orc where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from upd where upp_key in (select upp_key from upp,usr where upp.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from upp where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from usg where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from uhd where uph_key in (select uph_key from uph,usr where uph.usr_key = usr.usr_key and usr_login = z_usr_login);
            delete from uph where usr_key in (select usr_key from usr where usr_login = z_usr_login);
            delete from usr where usr_login = z_usr_login;
            commit;

END LOOP;
CLOSE c_user;

      DBMS_OUTPUT.PUT_LINE('Deletion of T11111X Users is complete. ');

   ELSE
         DBMS_OUTPUT.PUT_LINE('NO T11111X User is found. ');

END IF;

END;
/