What should I do if Oracle cannot delete a user?

Method: 1. Use the "select sid,serial# from v$session where username=''" statement to query session information; 2. Use the "alter system kill session ''" statement to kill the session; 3. Restart delete users.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What should I do if oracle cannot delete the user?

The Oracle database in the test environment needs to be refreshed as the software development version is upgraded, because after the test environment is used for a period of time, the data in it There will be more and more. Sometimes for the convenience of functional testing, the data in the database in the test environment will be inconsistent, which will have a certain impact and interference on the testing of subsequent versions. In order to avoid the above situation, generally all objects under the current database user will be cleared first and then re-created. But to delete all the objects and data under a user, you need to first find out what objects are under the current user, and then delete them. Using the cascade deletion method of deleting users is very simple and convenient, but if the user currently to be deleted is connected, then It cannot be deleted. You need to kill the session first and then delete it.

First you need to use the xshell tool to connect to the oracle database server (the introduction here is in the linux environment, the windows environment is simpler). If the current linux user is not an oracle user, you need to switch to the oracle user. Enter the command, su - oracle to switch

Execute under the oracle user, sqlplus / as sysdba, that is, log in to the database as the administrator sys. Deleting users generally only has the administrator account If you only need to query database tables and data, you only need to log in with a normal account. After connecting, you will be prompted with Connected to: oracle databases and other prompts, and the input prompt that appears becomes SQL>

Then enter the statement to delete the user, DROP USER cqt CASCADE; CASCADE means cascading deletion of all object information under the user. As shown in the figure, ORA-01940 error, the currently connected user cannot be deleted

If you want to continue deleting, you need to first query the user who is currently connected. Session information, then kill it, and then perform the delete operation. The query statement is: select sid,serial# from v$session where username='CQT'; note the difference in case. As shown in the figure, multiple session information connected to the user is queried. It is necessary to kill all the user sessions queried above.

The statement to kill the session is: alter system kill session '15,19095'; For multiple sessions, you can kill them multiple times.

After the above sessions are cleared, execute the command to delete the user again. As shown in the picture, it can be deleted successfully this time. Because this statement adds the cascade delete keyword, all objects and data under this user will be deleted.

The above completes the deletion of users and objects. If it is a database brush operation, you can start to re-create the user and then assign permissions and execute the database script. A new round of version testing is underway.

Notes

Be careful when deleting user objects in cascade to avoid deleting the wrong user or information. You can back up the data before operating.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What should I do if Oracle cannot delete a user?. For more information, please follow other related articles on the PHP Chinese website!