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');