FAQs about removing users and tablespaces in Oracle (for example, a ORA-01940 cannot delete a solution for a currently connected user)

Common Oracle problems with removing users and tablespaces (for example, a ORA-01940 cannot delete a solution for a currently connected user) at this time enter the sqlplus command window as an administrator. when you delete a user, the following problem sometimes occurs: The ORA-01940 cannot delete the currently connected user. The solution is: 1) view the user's connection status

select username,sid,serial# from v$session

 

(2) locate the sid and serial of the user to be deleted, and delete the user. For example, if you want to delete the user 'wuzhq', you can do this:

alter system kill session'532,4562';

 

Here, "532" indicates sid, and "4562" indicates seria. Users can be deleted only after step (2). (3) users can be deleted.

drop user username cascade

 

(**) If you still prompt ORA-01940 after drop: Unable to delete the user that is currently linked, there is a connected session, you can check the session status to check whether the session is killed. Use the following statement to check the status:

select saddr,sid,serial#,paddr,username,status from v$session where username is not null

 

The result is as follows (taking my database as an example ):

saddr                     sid           serial#            paddr              username              status564A1E28              513           22974            569638F4        NETBNEW             ACTIVE564A30DC              514          18183            569688CC        NETBNEW            INACTIVE564A5644              516           21573            56963340        NETBNEW            INACTIVE564B6ED0              531           9                   56962D8C        NETBNEW           INACTIVE564B8184              532          4562               56A1075C        WUZHQ                KILLED

 

Status indicates the session status of the user to be deleted. If the session status is inactive, the user is not killed. If the session status is killed, the user is killed. It can be seen that the session of the user WUZHQ has been killed. Users can be safely deleted. To delete a tablespace, follow these steps:

drop tablespace TS_MISPS including contents and datafiles cascade constraints;

 

TS_MISPS is the name of a tablespace. In addition: // including contents: indicates the content of the tablespace To be deleted. If there is content in the tablespace before the tablespace is deleted, but this parameter is not added, the tablespace cannot be deleted, so this parameter is habitually added. // Including datafiles: Delete the data files in the tablespace // cascade constraints: Delete the Foreign keys in the Table in tablespace at the same time. The following describes how to query the Oracle user session information, this method is for your reference. If you are interested, take a look. The V $ SESSION view allows you to query SESSION information of all Oracle users.

select sid,logon_time,username,machine from v$session;

 

Counts the total number of Oracle user sessions opened by different users or hosts by grouping

select username,machine,count(*) from v$session group by username,machine;

 

User sessions can be terminated Based on SID and SERIAL #

ALTER SYSTEM KILL SESSION 'v_sid,v_serial#' immediate;

(Replace v_sid and v_serial with numbers.) SID and SERIAL of the Oracle user SESSION # You can find them in the V $ SESSION View:

SQL> select sid,serial#,username, machine,status from v$session where username like 'PCNSH%';       SID    SERIAL# USERNAME     MACHINE                      STATUS  -------- ---------- --------------- ------------------------------- --------       366      14303 PCNSH197    client197.yourdomain.com     INACTIVE       369       1745 PCNSH003    server009                    INACTIVE       370      10165 PCNSH049    client049.yourdomain.com     INACTIVE       371      18999 PCNSH056    client056                    INACTIVE       372       6207 PCNSH056    client056                    INACTIVE       373       7688 PCNSH197    client197.yourdomain.com     INACTIVE       374      19246 PCNSH003    server009                    INACTIVE       377      17649 PCNSH003    server009                    INACTIVE  

 

Sessions terminated in Oracle will be cleared by pmon only when the process tries to connect to Oracle again. Before the process tries to connect again, you can still view the SESSION when querying the V $ SESSION view. When the user session cannot respond, the above method may not be able to terminate the user session, so you have to directly kill the system process (Exercise caution ). The system PROCESS corresponding to a user SESSION can be queried through the V $ SESSION and V $ PROCESS views, and the system PROCESS number can be queried through the session sid and SERIAL.

SQL> select p.spid from v$session s, v$process p     where p.addr=s.paddr and s.sid=518 and s.serial#=41831;  SPID  ------------  16782  

 

Then kill the process in the operating system: kill-9 16782 some software will open multiple sessions at startup. When the software unexpectedly exits, the source code is in the sky and the sessions should be terminated in batches. The stored procedure terminates sessions in batches based on user name conditions. The creation script kill_user. SQL is as follows:

create or replace procedure kill_user(v_name in varchar2)  as  message varchar2(50);  cursor loguser is select sid,serial# from v$session where username=upper(v_name);  v_info loguser%rowtype;  sql_mgr varchar2(1000);  i number default 0;  begin  open loguser;  loop  fetch loguser into v_info;  exit when loguser%notfound;  i :=1;  sql_mgr :='alter system kill session '''||v_info.sid||','||v_info.serial#||''' immediate';  execute immediate sql_mgr;  dbms_output.put_line('All the sessions of '||v_name||' have been killed');  end loop;  close loguser;  if i=0 then  raise_application_error(-20004,'The user '||v_name||' is not login the database currently!');  end if;  exception  when no_data_found then  raise_application_error(-20004,'The user '||v_name||' is not login the database currently!');  end;  / 

 

The following describes how to terminate all processes of a user by calling the preceding stored procedure:

SQL> execute kill_user('user_name');