Oracle下session的查询与删除
1、查询当前session
SQL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
—————————— ———- ———-
SYS 121 4
CPP 132 38
AAA 143 54
BBB 154 35
–SERIAL#:SID有可能会重复，当两个session的SID重复时，SERIAL#用来区别session
2、删除当前session
SQL> alter system kill session ‘143,54’;
System altered.
AAA的session被Kill后，如果其执行相关的Sql,会报以下的错误：
SQL> select * from test1;
select * from test1
ERROR at line 1:
ORA-00028: your session has been killed
3、删除当前session的用户
注意:不可以直接删除当前Session的用户，需要先关闭session，再删除用户
Ex：在不关闭session的情况下删除用户现象,
SQL> select username,account_status from dba_users;
–查看当前的用户
USERNAME ACCOUNT_STATUS
—————————— ——————————–
AAA OPEN
BBB OPEN
SYSTEM OPEN
CCC OPEN
SQL> select username,sid,serial# from v$session where username is not null;
–查看当前session的sid、serial#
USERNAME SID SERIAL#
—————————— ———- ———-
AAA 121 4
BBB 132 38
CCC 143 54
DDD 154 35
–此时不能删除，表明会话中的用户是不能被删除的，需要先退出会话才可以
SQL> drop user AAA;
drop user AAA
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
–开始删除会话中的AAA
SQL> alter system kill session ‘121,4’;
System altered.
–删除用户报错了，因为AAA用户下还有对象存在
SQL> drop user AAA;
drop user AAA
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop ‘AAA’
–将用户及其所有对象全部删除
SQL> drop user AAA cascade;
User dropped.
–AAA尝试连接，报错，无法登陆session
SQL> conn hyl/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
–查看dba_users数据字典，发现AAA用户已经被删除
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
—————————— ——————————–
BBB OPEN
SYSTEM OPEN
CCC OPEN
总结：
查询当时会话：select username,sid,serial# from v$session where username is not null;
删除当时会话：alter system kill session ‘sid，serial#’;
删除当时会话的用户，先kill session，再drop user（若用户下有对象，使用cascade命令）
