Oracle PL/SQL script to drop a user if not exists

This script will run a query to count the number of users with a specified user name and if the count!=0 then drop user query is executed.

    u_count number;
    user_name VARCHAR2 (50);


        u_count :=0;
        user_name :='datauser';
        SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (user_name);

             IF u_count != 0
                 EXECUTE IMMEDIATE ('DROP USER '||user_name||' CASCADE');
              END IF;

              u_count := 0;
           WHEN OTHERS
                     DBMS_OUTPUT.put_line (SQLERRM);
                     DBMS_OUTPUT.put_line ('   ');



Include the above script in a file .sql (Ex: DropOracleUser.sql). Make sure to replace the username with correct name which is 'datauser' in above script. You can run the above oracle PL/SQL script in sqlPlus as follows.

sql> START DropOracleUser.sql


  1. wow great Article, the details you have provided are much clear, easy to understand, if you post some more Article, it will be very much useful for me.
    PL/SQL Training in Chennai


Post a Comment

Popular posts from this blog

How to add Maven dependency from a relative path referencing a local jar

PHP-SOAP web service with out a WSDL

Manually install a maven dependency