Drop Multiple Oracle Database Users Using Shell Script

Sometimes you may came across that you need to remove multiple users in Oracle database. Here is a super duper simple bash script Gotcha…..

Oracle DROP USER statement

The DROP USER statement allows you to delete a user from the Oracle Database. If the user has schema objects, the DROP USER statement also can remove all the user’s schema objects along with the user.

The following illustrates the basic syntax of the DROP USER statement:

DROP USER username [CASCADE];

In this syntax, you need to specify the user that you want to drop after the DROP USER keywords.

If the user whose schemas contain objects such as views and tables, you need to delete all schema objects of the user first and then drop the user. Deleting all schema objects of the users first before removing the user is quite tedious. Therefore, Oracle provides you with the CASCADE option. If you specify the CASCADE option, Oracle will remove all schema objects of the user before deleting the user.

DROP TABLESPACE tablespacename including contents and datafiles;

If the schema objects of the dropped user are referenced by objects in other schemas, Oracle will invalidate these objects after deleting the user.

If a table of the dropped user is referenced by materialized views in other schemas, Oracle will not drop these materialized views. However, the materialized views can no longer be refreshed because the base table doesn’t exist anymore.

Note that Oracle does not drop roles created by the user even after it deletes the user.

Drop user while login to the database

Here is a small shell/bash script to remove multiple users in the Oracle database using shell.

#!/bin/bash

cat usernames.txt | while read usr
do
sqlplus -s / as sysdba<<EOF
set head off;
set feed off;
spool drop_users.txt;
DROP USER $usr cascade;
DROP TABLESPACE $usr including contents and datafiles;
commit;
exit;
spool off;
EOF

echo USER : ${usr} dropped
done

In here I assume that this script is run directly login to your database. And usernames.txt consists of user names of Oracle database which you need to remove from database.

Drop user without login to the database

#!/bin/bash

cat usernames.txt | while read usr
do
sqlplus dbusername/dbuserpassword@alias<<EOF
set head off;
set feed off;
spool drop_users.txt;
DROP USER $usr cascade;
DROP TABLESPACE $usr including contents and datafiles;
commit;
exit;
spool off;
EOF

echo USER : ${usr} dropped
done

About Author