This content has been marked as final. Show 9 replies
Performing DML in the data dictionary is one of the fastest ways to trash an Oracle database available. How did you get DBA privileges and not learn the proper way to do this? And DBA_USERS is a view ... not a table. Your suggestion is nothing less than shocking.
In any case ...
DROP USER <user_name> CASCADE;
Do them one at a time.
And given your apparent lack of knowledge please be sure you have a valid complete backup of all objects in those schemas before you do so.
you need to use DROP USER for each user. If there are many you can write a procedure, get the users from the database and use EXECUTE IMMEDIATE to drop the user.
Thanks hkandpal. Is this procedure looling good to you?
create or replace procedure drop_users is
cursor c_users is
for r_users in c_users loop
sqlstr := 'drop user ' || r_users;
execute immediate sqlstr;
when others then null;
Yes , it looks fine except in the error handler it is always better to have RAISE , for WHEN OTHERS. 1 st time you can run with out a COMMIT and test it.
Well....after DROP USER the user(s) will be dropped for good - explicit COMMIT or not within the procedure. Right?
Message was edited by:
sqlstr := 'drop user ' || r_users;Correct version should read
sqlstr := 'drop user "' || r_users.username || '" CASCADE';Double quotes and CASCADE are necessary, generally speaking.
Remove your exception handler. Be careful with your WHERE clause :))
COMMIT/ROLLBACK is only used for DML, not DDL or DROPPING users. You DROP a user they're gone. You can't ROLLBACK or COMMIT a DROP USER or even DROP TABLE.
cascade is used. but double quote shouldn't be (it'll cause error).
SQL> select * from cat where table_name like 'LOWERCASE%';Having said that i personally don't like people creating objects specifying in double quotes. It makes your life so miserable because i have seen only very few sql's handwritten (except code generators) uses doublequotes around database objects. So it is valid syntax.
no rows selected
SQL> create table "lowercase" (a number);
SQL> drop table lowercase;
drop table lowercase
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table "lowercase";
SQL> select * from dba_users where username like 'LOWERCASE%';
no rows selected
SQL> create user "lowercase" identified by lowercase;
SQL> grant connect,resource to "lowercase";
SQL> connect lowercase/lowercase;
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect "lowercase"/lowercase;
SQL> connect rnd/rnd
SQL> drop user "lowercase";