Remove users in OIM version 11gr2ps2

remove-users-oim-11gr2.sql

-- This is a simple script (TOTALLY AND UTTERLY UNSUPPORTED) which simply just removes all accounds created in OIM.
-- The users with the keys 1, 2, 3 and .. is skipped, as they are all registered as system-specific users.
--
-- Used with OIM version 11g R2 PS2
--
-- @since 01.06.2015
-- @author vegaasen
-- @version 11gR2ps2

--Custom tables
    TRUNCATE TABLE RECON_USER_MATCH;
    TRUNCATE TABLE RECON_ACCOUNT_MATCH;
    TRUNCATE TABLE RECON_ORG_MATCH;
    TRUNCATE TABLE RECON_ROLE_MATCH;
    TRUNCATE TABLE RECON_ROLE_HIERARCHY_MATCH;
    TRUNCATE TABLE RECON_ROLE_MEMBER_MATCH;
    TRUNCATE TABLE RECON_EVENT_ASSIGNMENT;
    TRUNCATE TABLE RECON_CHILD_MATCH;
    TRUNCATE TABLE RECON_HISTORY;
    TRUNCATE TABLE RA_LDAPUSER;
    TRUNCATE TABLE RA_LDAPROLE;
    TRUNCATE TABLE RA_LDAPROLEMEMBERSHIP;
    TRUNCATE TABLE RA_LDAPROLEHIERARCHY;
    TRUNCATE TABLE RA_MLS_LDAPROLE;
    TRUNCATE TABLE RA_MLS_LDAPUSER;

--Account reconciliation tables
    TRUNCATE TABLE RA_XELLERATE_ORG;
    TRUNCATE TABLE RA_XELLERATEUSER2;
    TRUNCATE TABLE RA_HRUSER10;
    TRUNCATE TABLE RA_ADGROUP5;
    TRUNCATE TABLE RA_ADORGANIZATIONALUN6;
    TRUNCATE TABLE RA_UD_ADUSRC;
    TRUNCATE TABLE RA_UD_ADUSRCLS;
    TRUNCATE TABLE RA_ADUSERTRUSTED8;
    DELETE FROM RA_ADUSER7;
    DELETE FROM RA_UD_EX_CH;
    DELETE FROM RA_UD_EX_PA;
    DELETE FROM RA_EXCHANGEUSER9;
    TRUNCATE TABLE RA_UD_IPNT_ROL;
    TRUNCATE TABLE RA_UD_IPNT_GRP;
    DELETE FROM RA_UNIXUSER15;
    TRUNCATE TABLE RA_LINUXHD11;
    TRUNCATE TABLE RA_USERPROFILE18;
    TRUNCATE TABLE RA_REMEDYUSER16;
    TRUNCATE TABLE RA_USHOME17;
    -- not trunctated yet
    TRUNCATE TABLE RA_DEFAULTUSERDF838E8A;
    TRUNCATE TABLE RA_DIRECTORYEXTERN10CF9BF3;
    TRUNCATE TABLE RA_DIRECTORYINTERN15982A58;
    TRUNCATE TABLE RA_BNET;
    TRUNCATE TABLE RA_DADUSRC;
    TRUNCATE TABLE RA_DADUSRCL;
    TRUNCATE TABLE RA_DMZADGROUP20;
    TRUNCATE TABLE RA_DMZADORGANIZATION21;
    TRUNCATE TABLE RA_DMZADUSER22;
    TRUNCATE TABLE RA_LDAPROLE;
    TRUNCATE TABLE RA_LDAPROLEHIERARCHY;
    TRUNCATE TABLE RA_LDAPROLEMEMBERSHIP;
    TRUNCATE TABLE RA_LDAPUSER;
    
-- Misc
    TRUNCATE TABLE RECON_EXCEPTIONS;
    DELETE FROM RECON_EVENTS WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    
    --Default tables from 11gR2PS2/PS3
    DELETE FROM ENT_ASSIGN_HIST WHERE usr_key NOT IN ('1', '2', '3', '4', '5');
    DELETE FROM ENT_ASSIGN WHERE usr_key NOT IN ('1', '2', '3', '4', '5');
    
    --Default tables from 9g    
    DELETE FROM oud WHERE oiu_key IN (SELECT oiu_key FROM oiu WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5')));
    DELETE FROM oiu WHERE usr_key NOT IN ('1', '2', '3', '4', '5');
    DELETE FROM oio WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM osi WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM osi WHERE req_key IN (SELECT req_key FROM req WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5')));
    DELETE FROM osi WHERE osi_assigned_to_usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM osh WHERE osh_assigned_to_usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM osh WHERE osh_assigned_by_usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rcp WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rpc 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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rcm 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.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rce WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rqu WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM oti WHERE orc_key IN (SELECT orc_key FROM orc,usr WHERE orc.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    
    DELETE FROM OSI;
    -- Note: you may need to disable the parent-child-constraint for this.
    DELETE FROM orc WHERE usr_key NOT IN ('1', '2', '3', '4', '5') AND ORC_PARENT_KEY IS NULL;
    DELETE FROM orc WHERE usr_key NOT IN ('1', '2', '3', '4', '5');
    
    DELETE FROM upd WHERE upp_key IN (SELECT upp_key FROM upp,usr WHERE upp.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM upp WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM usg WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM uhd WHERE uph_key IN (SELECT uph_key FROM uph,usr WHERE uph.usr_key = usr.usr_key and usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM uph WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM pcq WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM rcu WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr.usr_key NOT IN ('1', '2', '3', '4', '5'));
    
    -- Required additions to 11gR2PS2
    DELETE FROM emd WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM ORG_USER_MEMBERSHIPS WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM PENDING_ROLE_GRANTS WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM PWH WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM REQ WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM RML WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM USER_PROVISIONING_ATTRS WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM RECON_EVENTS WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM RECON_USER_MATCH WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    DELETE FROM RECON_USER_OLDSTATE WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    
    -- User Accounts
    TRUNCATE TABLE UD_ADGRP;
    TRUNCATE TABLE UD_ADOU;
    TRUNCATE TABLE UD_ADUCL_BN;
    TRUNCATE TABLE UD_ADURC_BN;
    --TRUNCATE TABLE UD_ADUSR_BN;
    TRUNCATE TABLE UD_ADUSRC;
    TRUNCATE TABLE UD_ADUSRCLS;
    --TRUNCATE TABLE UD_ADUSER;
    TRUNCATE TABLE UD_BNET;
    TRUNCATE TABLE UD_DADGRP;
    TRUNCATE TABLE UD_DADOU;
    --TRUNCATE TABLE UD_DADUSER;
    TRUNCATE TABLE UD_EX_CH;
    TRUNCATE TABLE UD_EX_PA;
    --TRUNCATE TABLE UD_EXCHANGE;
    TRUNCATE TABLE UD_HRUSER;
    TRUNCATE TABLE UD_IPNT_GR;
    TRUNCATE TABLE UD_IPNT_GRP;
    TRUNCATE TABLE UD_IPNT_OU;
    TRUNCATE TABLE UD_IPNT_RL;
    TRUNCATE TABLE UD_IPNT_ROL;
    --TRUNCATE TABLE UD_IPNT_USR;
    TRUNCATE TABLE UD_PFUSER;
    TRUNCATE TABLE UD_REMUSER;
    TRUNCATE TABLE UD_SSH;
    TRUNCATE TABLE UD_US_HOME;
 
    TRUNCATE TABLE UPA;
    TRUNCATE TABLE upa_fields;
    TRUNCATE TABLE UPA_GRP_MEMBERSHIP;
    TRUNCATE TABLE UPA_RESOURCE;
    TRUNCATE TABLE upa_ud_formfields;
    DELETE FROM upa_ud_forms WHERE upa_resource_key IN (SELECT upa_resource_key FROM upa_resource WHERE upa_usr_key IN (SELECT upa_usr_key FROM upa_usr WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'))));
    DELETE FROM UPA_RESOURCE;
    DELETE FROM UPA_USR WHERE usr_key IN (SELECT usr_key FROM usr WHERE usr_key NOT IN ('1', '2', '3', '4', '5'));
    
    --Clearing all Audit messages
    TRUNCATE TABLE AUD_JMS;
    
    --Clearing all user references
    DELETE FROM USR WHERE usr_key NOT IN ('1', '2', '3', '4', '5');
        
    COMMIT;

Good! Do you have some code for PS3?