How to delete the currently connected user in Oracle

Method: 1. Use "v$session" to find the session connected to the current user; 2. Use "alter system kill session 'sid,serial#'" to kill the session; 3. Use "drop user. ..cascade;" just delete the current user.

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

How to delete the user currently connected to oracle

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 There will be more and more data. 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 subsequent versions of the test. 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>

and 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'; pay attention to 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 execute multiple kills separately.

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.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to delete the currently connected user in Oracle. For more information, please follow other related articles on the PHP Chinese website!