Query and delete of Oracle's next session1. Query the current sessionsql> Select username,sid,serial# from v$session where username are not null;
USERNAME SID serial#
------------------------------ ---------- ----------
SYS 144 4
HYL 146
SCOTT 147
HR 159
--serial#:sid may Repeat, when the SID of the two sessions repeats, the serial# is used to distinguish the session
2. Delete the current sessionsql> alter system kill session ' 146,48 ';
System altered.
The Hyl session performs the following actions:
Sql> Show User
USER is "HYL"
Sql> SELECT * from Test1;
SELECT * FROM Test1
*
ERROR at line 1:
Ora-00028:your session has been killed
3, delete the current session of the userIdea: Close session first, then delete user
Demo: Delete a user behavior without closing the session:
sql> Select Username,account_status from dba_users;
--See which users are currently
USERNAME Account_status
------------------------------ --------------------------------
Csmig OPEN
Mgmt_view OPEN
SYS OPEN
SYSTEM OPEN
HYL OPEN
OE EXPIRED & LOCKED
6 rows selected.
sql> Select username,sid,serial# from v$session where username are not null;
--View the SID, serial# of the session through the V$session view
USERNAME SID serial#
------------------------------ ---------- ----------
SYS 144 4
HYL 146
SCOTT 147
HR 159
sql> drop user hyl; --Delete, error, indicates that the user in the session cannot be deleted, it needs to be closed first
Drop User Hyl
*
ERROR at line 1:
ora-01940:cannot Drop a user that is currently connected
sql> alter system kill session ' 146,54 ';
System altered.
--hyl session to view the data, the message shows that the session has been killed, indicating that the deletion session was successful
sql> select * from Test1;
SELECT * from Test1
*
ERROR at line 1:
Ora-00028:your session has been killed
--Complete Delete user below
sql> drop user hyl; --Delete user error, at this time hyl user under the existence of objects
Drop User Hyl
*
ERROR at line 1:
Ora-01922:cascade must is specified to drop ' HYL '
sql> drop user hyl cascade; --Delete all users and all of their objects
User dropped.
--Try Hyl connection, error, unable to use Hyl login session
sql> Conn hyl/oracle
ERROR:
ora-01017:invalid Username/password; logon denied
Warning:you is no longer connected to ORACLE.
--View the Dba_users data dictionary to indicate that the hyl user has been deleted
sql> Select Username,account_status from dba_users;
USERNAME Account_status
------------------------------ --------------------------------
Csmig OPEN
Mgmt_view OPEN
SYS OPEN
SYSTEM OPEN
OE EXPIRED & LOCKED
5 rows selected.
Summary:
Query current session: Select username,sid,serial# from v$session where username are not null;
Delete current session: Alter system kill session ' sid,serial# ';
Delete the current session user, kill session first, then drop user (if the user has objects, using the Cascade command)
——————————————————————————————————————————————————————————————————————
added: username is empty
--Supplement: When you query a session without adding "username is not NULL" condition, the session information will appear empty username
sql> Select username,sid,serial# from v$session;
USERNAME SID serial#
------------------------------ ---------- ----------
SYS 144 4
SCOTT 147
149 2
153 346
154 1
158 7
HR 159
1
161 1
162 1
163 1
164 1
165 1
166 1
167 1
168 1
169 1
1
rows selected.
Description:
Oracle's principles for username:
Oracle internal process user# is 0 o'clock, then username is empty;
When the username of an oracle internal process is empty, the user# is 0;
Username, user# Two fields are fields that represent users.
The results are as follows:
The foreground process belongs to a database user, so the user# of the foreground process is not empty for 0,username;
The background process does not belong to any database user, so the background process's user# is 0,username empty.
Summary:
The user can only use the server process (foreground process), cannot use the background process, and the foreground process and background process session can correspond, so in v$session username is empty (that is, user# is 0).
--Complete supplement
——————————————————————————————————————————————————————————————————————
*********************************************** declaration ************************************* ***********
Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source (Http://blog.csdn.net/huangyanlong).
There is an error in the statement, please leave a message, greatly appreciated.
Reminder: Click on the table of contents, more helpful to your view.
*************************************************************************************************** **
