9 Replies Latest reply on Sep 12, 2007 4:28 PM by 561825

    delete user accounts from database

    555356
      I need to delete a bunch of user accounts from the database.
      Can I just do 'delete from dba_users where ....'
      or I have to write a loop to 'drop user ...'?
        • 1. Re: delete user accounts from database
          damorgan
          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.
          • 2. Re: delete user accounts from database
            Himanshu Kandpal
            Hi,

            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
            • 3. Re: delete user accounts from database
              555356
              Thanks hkandpal. Is this procedure looling good to you?
              create or replace procedure drop_users is
              cursor c_users is
              select username
              from dba_users
              where ...

              sqlstr varchar2(64);

              begin
              for r_users in c_users loop
              begin
              sqlstr := 'drop user ' || r_users;
              execute immediate sqlstr;
              exception
              when others then null;
              end;
              end loop;
              end drop_users;
              • 4. Re: delete user accounts from database
                Himanshu Kandpal
                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.



                Thanks
                • 5. Re: delete user accounts from database
                  45736
                  Well....after DROP USER the user(s) will be dropped for good - explicit COMMIT or not within the procedure. Right?

                  Message was edited by:
                  rxshah
                  • 6. Re: delete user accounts from database
                    511365
                    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 :))

                    Good luck.
                    • 7. Re: delete user accounts from database
                      584650
                      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.
                      • 8. Re: delete user accounts from database
                        555356
                        cascade is used. but double quote shouldn't be (it'll cause error).
                        thanks.
                        • 9. Re: delete user accounts from database
                          561825
                          really
                          SQL> select * from cat where table_name like 'LOWERCASE%';

                          no rows selected

                          SQL> create table "lowercase" (a number);

                          Table created.

                          SQL> drop table lowercase;
                          drop table lowercase
                                     *
                          ERROR at line 1:
                          ORA-00942: table or view does not exist


                          SQL> drop table "lowercase";

                          Table dropped.

                          SQL> select * from dba_users where username like 'LOWERCASE%';

                          no rows selected

                          SQL> create user "lowercase" identified by lowercase;

                          User created.

                          SQL> grant connect,resource to "lowercase";

                          Grant succeeded.

                          SQL> connect lowercase/lowercase;
                          ERROR:
                          ORA-01017: invalid username/password; logon denied


                          Warning: You are no longer connected to ORACLE.

                          SQL> connect "lowercase"/lowercase;
                          Connected.

                          SQL> connect rnd/rnd
                          Connected.

                          SQL> drop user "lowercase";

                          User dropped.
                                
                              
                          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.

                          Regards

                          Raj