Oracle PL/SQL Tutorial/User Privilege/Drop User
Версия от 13:45, 26 мая 2010; (обсуждение)
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>