Oracle PL/SQL Tutorial/User Privilege/Drop User

Add "CASCADE" after the user"s name in the DROP USER statement

If the user"s schema contains objects such as tables and so on.

SQL>
SQL> CREATE USER user1 IDENTIFIED BY pass1;
User created.
SQL>
SQL> GRANT CREATE SESSION TO user1;
Grant succeeded.
SQL>
SQL> DROP USER user1 cascade;
SQL>

Drop the user if it exists

SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> SPOOL create_user.log
SQL>
SQL> DECLARE
  2     v_count       INTEGER        := 0;
  3     v_statement   VARCHAR2 (500);
  4  BEGIN
  5
  6    
  7
  8     SELECT COUNT (1) INTO v_count FROM dba_users WHERE username = UPPER ("&username");
  9
 10     IF v_count != 0
 11     THEN
 12        EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
 13     END IF;
 14
 15     v_count := 0;
 16
 17  EXCEPTION
 18     WHEN OTHERS
 19     THEN
 20        DBMS_OUTPUT.put_line (SQLERRM);
 21        DBMS_OUTPUT.put_line ("   ");
 22  END;
 23  /
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL> SET FEEDBACK ON
SQL>
SQL> SPOOL OFF
SQL>
SQL>