oracle下session的查询与删除 1、查询当前session sqlgt; select username,sid,serial# from v$session where username is not
oracle下session的查询与删除
1、查询当前sessionsql> select username,sid,serial# from v$session where username is not null;
username sid serial#
------------------------------ ---------- ----------
sys 144 4
hyl 146 48
scott 147 64
hr 159 15
--serial#:sid有可能会重复，，当两个session的sid重复时，serial#用来区别session
2、删除当前sessionsql> alter system kill session '146,48';
system altered.
hyl的session下执行操作如下：
sql> show user
user is hyl
sql> select * from test1;
select * from test1
*
error at line 1:
ora-00028: your session has been killed
--------------------------------------------------------------------------------
linux-6-64下安装oracle 12c笔记
在centos 6.4下安装oracle 11gr2(x64)
oracle 11gr2 在vmware虚拟机中安装步骤
debian 下 安装 oracle 11g xe r2
--------------------------------------------------------------------------------
3、删除当前session的用户思路：先关闭session，然后再删除用户
演示：在不关闭session的情况下删除用户现象：
sql> select username,account_status from dba_users;
--查看当前有哪些用户
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 is not null;
--通过v$session视图，查看会话的sid、serial#
username sid serial#
------------------------------ ---------- ----------
sys 144 4
hyl 146 54
scott 147 64
hr 159 15
sql> drop user hyl; --删除，报错了，表明会话中存在的用户是不能被删除的，需要先将其关闭
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.
user dropped.
--尝试hyl连接，报错，无法使用hyl登陆session
sql> conn hyl/oracle
error:
ora-01017: invalid username/password; logon denied
warning: you are no longer connected to oracle.
--查看dba_users数据字典，表明hyl用户已经被删除
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.
小结：
查询当前会话：select username,sid,serial# from v$session where username is not null;
删除当前会话：alter system kill session 'sid，serial#';
删除当前会话的用户，先kill session，再drop user（若用户下有对象，使用cascade命令）
更多详情见请继续阅读下一页的精彩内容：