Rem
Rem $Header: admin_drop_users.sql 20-feb-2005.23:05:07 jsadras Exp $
Rem
Rem admin_drop_users.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_drop_users.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     02/20/05 - bug:3270406
Rem    rpinnama    03/26/03 - Remove references to DropRep
Rem    rpinnama    03/25/03 - Parameterize repos user
Rem    rpinnama    06/03/02 - rpinnama_reorg_rep_scripts_3
Rem    rpinnama    05/20/02 - Created
Rem

Rem
Rem This SQL script requires the following arguments
Rem  1. Name of the repository user
Rem
DEFINE EM_REPOS_USER ="&1"



Rem Generate Drop users script
Rem Error 1918 is received if the repository user does not exist - we simply exit and continue
Rem Error 1940 is received if there is an active session - we continue the loop
Rem Error 30 is received if the session we are killing no longer exists - we continue the loop
Rem NOTE:  The 1940 will be returned until the other end of the session re-accesses it.  
Rem        This may take up to several minutes, so we ignore these errors completely.  
Rem        If we trace in this loop, we will exit with a buffer overflow, 
Rem        so tracing is not done in this loop 


declare
  l_username varchar2(30);
  l_sid number;
  l_serial number;
  err number;
  c number;
begin

    -- First, drop all users, except SYSMAN who have MGMT_USER role and
    -- are created by EM. All users created by EM will have a record
    -- in MGMT_CREATED_USERS table
    --
    c := 0;
    begin
     loop
        BEGIN
          l_username := '';
          select grantee into l_username
            from sys.dba_role_privs
           where granted_role = 'MGMT_USER'
             AND grantee IN (SELECT user_name
                               FROM &EM_REPOS_USER..MGMT_CREATED_USERS
                               WHERE SYSTEM_USER=0)
             AND ROWNUM=1;

          l_username := upper(l_username);

          LOOP

            BEGIN
              FOR crec in (SELECT sid, serial#
                             FROM v$session
                            WHERE username=l_username
                              AND status NOT IN('KILLED'))
              LOOP
                BEGIN

                   EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
                                      crec.sid || ',' || crec.serial# || '''';
                   EXCEPTION
                      WHEN OTHERS THEN
                         err := SQLCODE;
                         IF err != -30 THEN
                            EXIT;
                         END IF;
                END;
              COMMIT;
              END LOOP; -- END OF KILLING SESSION LOOP

            EXCEPTION
                WHEN OTHERS THEN
                  err := SQLCODE;
                  IF err != -30 THEN
                    EXIT;
                  END IF;
            END;

            IF SQL%NOTFOUND THEN
               EXIT;
            END IF;

          COMMIT;

          END LOOP;

          EXECUTE IMMEDIATE 'drop user ' || l_username || ' cascade';

          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              EXIT;
            WHEN OTHERS THEN
              err := SQLCODE;
              IF err = -1918 THEN
                EXIT;
              ELSE
                IF err = -1940 THEN
                  NULL;
                ELSE
                  c := c+1;
                  IF c > 50 THEN
                     RAISE;
                  END IF;
                END IF;
              END IF;
        END;
      end loop;
    end;
end;
/