User not deleted in Oracle, ORA-01940 prompt "Unable to delete current connected user"

Source: Internet
Author: User

Oracle Delete User prompt cannot delete the currently connected user two solutions are as follows: 1, first lock the user, then query the process number, and finally delete the corresponding process, in the deletion of the corresponding users Sqlalter user XXX account lock; Sqlselect * from v$session WHERE username= ' lgdb ';

Oracle Delete user prompt Unable to delete the currently connected user two workarounds are as follows:

1, first lock the user, and then query the process number, and finally delete the corresponding process, delete the corresponding user

Sql>alter user XXX account lock;

Sql>select * from v$session WHERE username= ' lgdb ';

Sql>alter system kill session ' XX,XX '

Sql>drop user XX Cascade

2.shutdown Drop the database and restart

The specific query process number, and finally delete the corresponding process steps are as follows

1) View the user's connection status

Select username,sid,serial# from V$session

The following results:

Username Sid serial#

Netbnew 513 22974

Netbnew 514 18183

Netbnew 516 21573

Netbnew 531 9

TS 532 4562

(2) Find the SID to delete the user, and serial, and remove

Such as: you want to delete the user ' ts ', you can do this:

Alter system kill session ' 532,4562 '

(3) Delete user

Drop USER TS Cascade

(* *) If after the drop also prompts ORA-01940: Unable to delete the currently linked user, indicating that there is a connection session, you can check the status of the session to determine whether the session was killed, the following statement to see:

Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null

The results are as follows (for example in my library):

SADDR SID serial# paddr username status

564a1e28 513 22974 569638f4 netbnew ACTIVE

564A30DC 514 18183 569688CC netbnew INACTIVE

564a5644 516 21573 56963340 netbnew INACTIVE

564B6ED0 531 9 56962d8c netbnew INACTIVE

564b8184 532 4562 56a1075c WUZHQ killed

Status is to delete the user's session state, if it is inactive, the description has not been killed, if the status is killed, the description has been kill.

This shows that TS this user's session has been killed. Users can be safely removed at this time.

(go) The user cannot be removed from Oracle, ORA-01940 prompt "Unable to delete the currently connected user"

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.