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>