Rem
Rem $Header: admin_drop_repos_user.sql 27-nov-2007.07:14:03 edemembe Exp $
Rem
Rem admin_drop_repos_user.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_drop_repos_user.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    edemembe    11/30/07 - XbranchMerge edemembe_bug_6630587 from main
Rem    edemembe    11/26/07 - Bug 6630587
Rem    sktedla     07/30/07 - sktedla_rfi_backport_4726892_10.2.0.4
Rem    minfan      07/27/07 - revert changes by 3145611
Rem    minfan      07/16/07 - remove dbms outputs
Rem    jsadras     12/30/04 - bug4087165
Rem    rpinnama    03/26/03 - Remove references to DropRep
Rem    rpinnama    03/25/03 - Parameterize repository 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
set serveroutput on

declare
  l_username varchar2(30);
  l_sid number;
  l_serial number;
  err number;
  c number;
  l_job_process_count NUMBER ;
  TYPE TBSP_ARRAY IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER ;
  l_tablespaces TBSP_ARRAY;


    PROCEDURE set_job_process_count(p_count IN NUMBER)
    IS
    BEGIN
      --scope=memory so it will be reset on instance startup
      -- SID=* to take care of RAC
      IF p_count >=0
      THEN
        EXECUTE IMMEDIATE 'ALTER SYSTEM SET job_queue_processes='
                    ||p_count||' SID=''*''' ;
      END IF ;
    EXCEPTION WHEN OTHERS THEN NULL ;
    END set_job_process_count ;

    FUNCTION get_job_process_count
    RETURN NUMBER
    IS
    l_value NUMBER ;
    BEGIN
      SELECT value
        INTO l_value
        FROM v$parameter
       WHERE name = 'job_queue_processes' ;
       RETURN(l_value) ;
    EXCEPTION
    WHEN OTHERS THEN
       RETURN(10) ;
    END get_job_process_count ;
begin
    -- Now, drop the SYSMAN user
    c := 0;
    -- validate user exists
    BEGIN
      SELECT username
        INTO l_username
        FROM dba_users
       WHERE username = upper('&EM_REPOS_USER') ;
      EXECUTE IMMEDIATE 'ALTER USER '||l_username||' ACCOUNT LOCK' ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN ; 
    END ;
    -- reduce job_queue_processes to zero
    l_job_process_count := get_job_process_count ;
    set_job_process_count(0) ;
    begin
     loop
        BEGIN
          LOOP
            BEGIN
              FOR crec in (SELECT sid, serial#
                              FROM gv$session
                             WHERE (upper(username)=l_username OR
                                    upper(schemaname)=l_username)
                              AND status != 'KILLED')
              LOOP
                BEGIN
                   EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION IMMEDIATE ''' ||
                                     crec.sid || ',' || crec.serial# || '''';
                  EXCEPTION
                      WHEN OTHERS THEN
                         err := SQLCODE;
                         IF err != -30 THEN
                            EXIT;
                         END IF;
                END;
                commit;
              END 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;

	  -- >> START - Capturing the Tablespaces to be dropped

          for crec in (SELECT DISTINCT TABLESPACE_NAME FROM all_tables
                   WHERE owner = '&EM_REPOS_USER' AND table_name IN ('MGMT_TARGETS','MGMT_JOB_PARAMETER','MGMT_AUDIT_DATA')
                   MINUS
                   SELECT DISTINCT TABLESPACE_NAME FROM all_tables
                   WHERE owner != '&EM_REPOS_USER')
            LOOP
                l_tablespaces(l_tablespaces.count) := crec.tablespace_name;
            END LOOP;

	  -- >> END - Capturing the Tablespaces to be dropped

          execute immediate 'drop user ' || l_username || ' cascade';
          set_job_process_count(l_job_process_count) ;

   	  -- >> START - Dropping the Tablespaces
	  BEGIN
	      IF (l_tablespaces.count > 0) THEN
		FOR i IN l_tablespaces.FIRST .. l_tablespaces.LAST
		    LOOP
		    BEGIN
		       execute immediate 'drop tablespace ' || l_tablespaces(i) || ' including contents';
		       exception
		       when NO_DATA_FOUND then
			 DBMS_OUTPUT.PUT_LINE('DropRep: Tablespaces for REPOS USER do not exist');
		       WHEN OTHERS THEN
			 err := SQLCODE;
			 DBMS_OUTPUT.PUT_LINE('DropRep: Error ' || SQLCODE || ' received dropping tablespace ' || l_tablespaces(i));
		    END;
		    END LOOP;
	     ELSE
	       DBMS_OUTPUT.PUT_LINE('NO TABLESPACES Exist');
	     END IF;
	  END;
	  -- >> END - Dropping the Tablespaces

          exit;

          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              exit;
            WHEN OTHERS THEN
              err := SQLCODE; 
              IF err = -1918 THEN
                EXIT;
              ELSE
                IF err = -1940 THEN
                   NULL;
                ELSE
                   DBMS_OUTPUT.PUT_LINE('not 1940 err' || err);
                   c := c+1;
                   IF c > 50 THEN
                      RAISE;
                   END IF;
                END IF;
              END IF;
        END;
      end loop;
    end;
    commit;
EXCEPTION
WHEN OTHERS THEN
  set_job_process_count(l_job_process_count) ;
  RAISE ;
end;
/