Menu
I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected I followed the link in SO to find out the sessions - Dropping a connected user from an Oracle 10g database schema
But when I ran the command I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test'; no rows selected Please help me how to drop users in this case.
460
Cause: An attempt was made to drop a user that was currently logged in. Action: Make sure the user is logged out, then re-execute the command. The ORA-01940 can always be cured by bouncing the source and replicated instance. First, double-check to ensure that the user is not connected to the current instance.
The Oracle DROP USER CASCADE command drops a user and all owned objects. The user will not be dropped and an error message will be returned if you a user owns objects and you fail to use the Oracle DROP USER CASCADE command.
To disable a user account, select it in the User List, and then select Disable from the User Actions drop-down menu. On the displayed Disable page, select the resource accounts to disable, and then click OK. Waveset displays the results of disabling the Waveset user account and all associated resource accounts.
Show activity on this post. BEGIN FOR c IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE ('DROP TABLE "' || c. table_name || '" CASCADE CONSTRAINTS'); END LOOP; FOR s IN (SELECT sequence_name FROM user_sequences) LOOP EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.
Users are all capitals in v$session (and data dictionary views). If you match with capitals you should find your session to kill.
SELECT s.sid, s.serial#, s.status, p.spid FROM v$session s, v$process p WHERE s.username = 'TEST' --<<<-- AND p.addr(+) = s.paddr / Pass actual SID and SERIAL# values for user TEST then drop user...:
ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>' / If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
