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.

   <source lang="sql">

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></source>

Drop the user if it exists

   <source lang="sql">

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></source>