Oracle is one of the most popular relational database management systems famous for its flexibility and convenience for information management. You can easily adjust any Oracle database to meet your everyday requirements. Sometimes, it is necessary to delete particular users from your Oracle database. Basically, there are two ways of achieving this goal: by using the Oracle DROP USER command or with the help of an Oracle IDE allowing you to automate the process. This tutorial is valid for all Oracle versions starting from 11g-12c and ending with 19c.
Contents
- Oracle DROP USER command syntax
- DROP USER CASCADE in Oracle
- ORACLE DROP USER examples
- Manage and delete users’ accounts easily!
- Conclusion
To begin with, let us take a closer look at the syntax of the DROP USER command. The main purpose of the statement is to delete a database user that you no longer need. Additionally, with the DROP USER statement, you can remove the user’s schema objects as well.
Note: You must have the DROP USER privileges to successfully run the query. If you try to drop users SYS or SYSTEM, this will lead to your database corruption.
Oracle DROP USER command syntax
The basic syntax to drop a user in Oracle looks like this:
DROP USER username;The DROP USER command in Oracle will work for a user that owns no database objects and is not currently connected. Later in this article, we will figure out how to delete a user in Oracle despite these factors.
DROP USER CASCADE in Oracle
The next thing we are going to discuss is the DROP USER CASCADE command. In case an Oracle user owns any database objects, this syntax is the only way to drop it. The CASCADE clause will help you make sure everything, including contents and datafiles, is deleted along with the owner shema.
For a better understanding of the DROP USER practical usage, take a look at the basic syntax of the command:
DROP USER username CASCADE;In this syntax, it is important to specify the name of the user to be deleted after the DROP USER keywords. To ensure that all the objects are dropped, add CASCADE before executing the command.
There is another important thing to remember. You might encounter a situation where some of the objects in the database refer to the schema objects owned by the user we are going to drop. In such a scenario, these objects will be removed along with the dropped user even if they belong to a different schema.
However, if we are talking about the materialized views in other schemas that refer to the table in a dropped one, the scenario will not be the same. These views will not be removed but you will not be able to refresh them as the table does not exist anymore. The roles created by the dropped user will remain intact after the deletion.
Note: You cannot delete the user with a schema containing a table that uses a flashback data archive. You should disable the flashback data archive first.
ORACLE DROP USER examples
In this chapter, we suggest diving into the practical side of the matter by looking into some examples of how to delete a user with the help of the DROP USER statement. There is a substantial difference in approaches to dropping a user that owns some objects and a user that owns none. Thus, we will take this difference into consideration. Moreover, we will go over the scenario when we need to drop a user that is currently connected.
DROP USER including contents with CASCADE
As mentioned earlier, an Oracle user that owns any kind of objects can be dropped with the help of the CASCADE placed after the DROP USER keywords. For example, let us assume we have the visitor user that owns a couple of tables and we are going to delete it with all its belongings:
DROP USER visitor CASCADE;As a result, the visitor user and all the related objects will be deleted.
Note: If you use DROP USER as is in this case, you are going to get the following error: ORA-01922: CASCADE must be specified to drop ‘VISITOR’.
Forcefully drop connected user in Oracle
Let us imagine that we are working with another customer user that is currently connected. Let us try to drop this user by executing the DROP USER syntax. Assuming that it does not contain any objects, we will not be using the CASCADE clause:
DROP USER customer;Now we see, that Oracle does not allow us to delete a connected user and displays an error message: ORA-01940: cannot drop a user that is currently connected. To overcome the obstacle that has come in our way, we will use the exit command:
Once done, we can go ahead and execute DROP USER once again. However, with a little more luck this time:
Manage and delete users’ accounts easily!
Even though the command line is the basis of working with Oracle databases, not all people are big fans of it. Using the command line requires a lot of experience and precision, while different IDEs can make the process of creating and executing queries much more cost-effective and easy. One of such helpful tools is dbForge Studio for Oracle.
Just like we did earlier in this article, we are going to assume we have a new member user in Oracle. Currently, this guy does not own any objects whatsoever. Therefore, the DROP USER command should work just fine for our purpose:
DROP USER member;However, if our member was around for a while, it would have some contents and datafiles in its possession. Thus, we would use the CASCADE clause.
DROP USER member CASCADE;
Practically, the query would look pretty much the same and the result will be similar. Nevertheless, it is crucial to use CASCADE while dealing with schemas containing database objects since without it you will face an error.
An alternative way to drop a user in dbForge Studio for Oracle is by deleting it using Security Manager:
1. Select Security Manager on the Database menu.
2. Choose the corresponding connection from the list.
3. Expand the Users node in the Security Manager.
4. Select member and click the Delete button on the Security Manager toolbar.
You can also right-click member and select Delete on the shortcut menu.
5. Click Yes in the appeared dialog box.
Conclusion
In this article, we have discussed how to drop a user in the Oracle database in two ways. First, it’s the DROP USER statement to be executed manually. The second way is much faster and more convenient as it implies using specifically designed integrated development environments like dbForge Studio for Oracle that combines both the usual command-line interface and user-friendly graphical Security Manager.
Useful links:
