mysql 查session_Oracle下session的查询与删除

Oracle下session的查询与删除 1、查询当前session SQLgt; select username,sid,serial# from v$session where username is not

Oracle下session的查询与删除

1、查询当前session

SQL> 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、删除当前session

SQL> 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命令)

更多详情见请继续阅读下一页的精彩内容：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！