oracle查询连接池常用语句
select username , count(*), machine from v$session where username is not null group by username, machine order by username; select sid, machine, LAST_CALL_ET, PROGRAM, prev_exec_start, logon_time, status from v$session where username='username' AND STATUS='INACTIVE' ORDER BY LAST_CALL_ET DESC; SELECT * FROM V$locked_object;
oracle相关问题汇总：
1.c#\winform 应用，连接数据库采用随用随开，用完关闭机制。也即是短链接。
2.在数据库长时间不访问的时候就会有次报错，
原因 ：ManagedDataAccess 连接池机制为 OracleConnection conn=new OracleConnection （）每次都会生成新的数据库连接，conn.close() dispose()都不能有效的清除。
解决：OracleConnection.ClearPool(conn);
数据库连接池有超时时间，客户端长时间不用，数据库会主动释放这个连接。这时候就需要定时查询一下数据库，保持连接，不让数据库释放连接解决。
3.oracle 查看未关闭连接
查看连接状态、问题电脑等信息：
select sid,serial#,username,program,machine,status from v$session;
查看sql;
select sql_text, count(*) from v$sql s, v$session se where se.prev_hash_value=s.hash_value and se.status='INACTIVE' group by sql_text;
--利用Oracle数据库的动态性能视图v$open_cursor，即通过游标来查找未释放的数据库连接
select * from v$open_cursor where user_name='SCOTT';其中SCOTT为登录的用户名
如果Java数据库的连接没有关闭通过上面的sql语句就可以看到被查询的sql语句 select * from dept
在被打开的游标里面出现多次。如下所示：
123 3997DA58 315 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
120 39976A20 309 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
124 3996F9E8 303 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
然后根据sql语句去查找，写代码是那个sql语句没有关闭。
--oracle修改连接空闲自动断开
SELECT * FROM DBA_PROFILES;
CREATE PROFILE KILLIDLE LIMIT IDLE_TIME ;
alter PROFILE DEFAULT LIMIT IDLE_TIME ;
有必要还可以根据业务修改连接时长，超过多少时间就干掉连接
alter PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED;
- select count(*) from v$process;
- select count(*) from v$session;
- select max(pga_used_mem)// M from v$process;
- select min(pga_used_mem)// M from v$process where pga_used_mem>;
- select a.USERNAME,a.MACHINE, sql_text,'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||''';' ,status
from gV$session a inner join GV$sql b on a.sql_id=b.sql_id
WHERE status = 'ACTIVE' - ********************************************************************************************
- oracle如何查看当前有哪些用户连接到数据库
-
可以执行以下语句：select username,serial#, sid from v$session; ---查询用户会话alter system kill session 'serial#, sid ';---删除相关用户会话建议以后台登陆删除用户会话1、查询oracle的连接数select count(*) from v$session;2、查询oracle的并发连接数select count(*) from v$session where status='ACTIVE';3、查看不同用户的连接数select username,count(username) from v$session where username is not null group by username;4、查看所有用户：select * from all_users;5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：select * from dba_sys_privs;select * from user_sys_privs;6、查看角色(只能查看登陆用户拥有的角色)所包含的权限select * from role_sys_privs;7、查看用户对象权限：select * from dba_tab_privs;select * from all_tab_privs;select * from user_tab_privs;8、查看所有角色：select * from dba_roles;9、查看用户或角色所拥有的角色：select * from dba_role_privs;select * from user_role_privs;10、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)select * from V$PWFILE_USERS;修改数据库允许的最大连接数：alter system set processes = 300 scope = spfile;查看游标数量Select * from v$open_cursor Where user_name=''查询数据库允许的最大连接数：select value from v$parameter where name = 'processes';或者：show parameter processes;查询数据库允许的最大游标数：select value from v$parameter where name = 'open_cursors'查看oracle版本select banner from sys.v_$version;按降序显示用户"SYSTEM"为每个会话打开的游标数select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'SYSTEM' and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc;
--查看连接都在执行什么sql
select sql_text,se.* from v$sql s, v$session se
where se.prev_hash_value=s.hash_value and se.status='INACTIVE' and se.username ='ETL'-- 查看session信息
select prev_exec_start,LAST_CALL_ET sss,machine,port,process,status,program,type,username, sid, serial#, server, paddr from v$session where username ='ETL' and upper(machine) like '%-PC%' order by sss desc;-- 统计程序及所在主机的连接数
SELECT b.MACHINE, b.PROGRAM, COUNT (*) FROM v$process a, v$session b
WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;
优化：发现是线程池设置的很小，每个流程是占用一个连接的，当并行流程数超过线程池最大数时，设置的策略是新建连接，而流程完成后会将连接归还给连接池，连接池不会立即回收连接而是定时检测并关闭超出最大空闲时间的连接。这个空闲时间是10分钟，比较长，可以设置小一些就会快一些回收空闲连接，减少总的连接数。
看网上还有说定时杀session的，修改数据库连接超时的，对于我这个程序都不够安全实用，还是设置连接池快速回收空闲的连接比较安全。
不再一直持有连接,用完就释放,下次用的时候再取,把连接数又继续大大降低了。
SQL> Select count(*) from v$session where status='ACTIVE' ;
COUNT(*)
----------
20
SQL> Select count(*) from v$session;
COUNT(*)
----------
187
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ----------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 450
SQL>
并发指active,I SEESQL> select count(*) from v$session #连接数
SQL> Select count(*) from v$session where status='ACTIVE' #并发连接数
SQL> show parameter processes #最大连接
SQL> alter system set processes = value scope = spfile;重启数据库 #修改连接
unix 1个用户session 对应一个操作系统 process
而 windows体现在线程DBA要定时对数据库的连接情况进行检查，看与数据库建立的会话数目是不是正常，如果建立了过多的连接，会消耗数据库的资源。同时，对一些“挂死”的连接，可能会需要DBA手工进行清理。
以下的SQL语句列出当前数据库建立的会话情况:
select sid,serial#,username,program,machine,status
from v$session;
输出结果为:
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---- ------- ---------- ----------- --------------- --------
1 1 ORACLE.EXE WORK3 ACTIVE
2 1 ORACLE.EXE WORK3 ACTIVE
3 1 ORACLE.EXE WORK3 ACTIVE
4 1 ORACLE.EXE WORK3 ACTIVE
5 3 ORACLE.EXE WORK3 ACTIVE
6 1 ORACLE.EXE WORK3 ACTIVE
7 1 ORACLE.EXE WORK3 ACTIVE
8 27 SYS SQLPLUS.EXE WORKGROUP\\WORK3 ACTIVE
11 5 DBSNMP dbsnmp.exe WORKGROUP\\WORK3 INACTIVE
其中，
SID 会话(session)的ID号;
SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话;
USERNAME 建立该会话的用户名;
PROGRAM 这个会话是用什么工具连接到数据库的;
STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作;
如果DBA要手工断开某个会话，则执行:
alter system kill session \'SID,SERIAL#\'sql语句SQL语句如下：
SELECT username, machine, program, status, COUNT (machine) AS
连接数量
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
显示结果（每个人的机器上会不同）
SCHNEIDER|WORKGROUD\WANGZHENG|TOAD.exe|ACTIVE|1
SCHNEIDER|WORKGROUP\597728AA514F49D|sqlplusw.exe|INACTIVE|1
|WWW-Q6ZMR2OIU9V|ORACLE.EXE|ACTIVE|8
PUBLIC|||INACTIVE|0按主机名查询SELECT COUNT(*) FROM V$SESSION WHERE MACHINE = 'DXMH'; 'DXMH'为主机名数据恢复语句create table informationlaw_bak
as
select * from informationlaw as of TIMESTAMP to_timestamp('20121126 103435','yyyymmdd hh24miss');//按机器名分组查select username,machine,count(username) from v$session where username is not null group by username,machine;// IPselect USER,sid,serial#,UTL_INADDR.GET_host_ADDRESS as host,SYS_CONTEXT('USERENV','ip_ADDRESS') as local,SYSDATE from V$sessionOracle查询session连接数和inactive以及 概要文件IDLE_TIME限制用户最大空闲连接时间
-----############oracle会话和进程################-------------- --查询会话总数 select count(*) from v$session; --查询进程总数 select count(*) from v$process; --查询哪些应用的连接数此时是多少 select b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE , b.PROGRAM order by count(*) desc; --查询是否有死锁 如果查询结果为no rows selected，说明数据库中没有死锁。否则说明数据库中存在死锁。 select * from v$locked_object; 接下来说明一下会话的状态： 1.active 处于此状态的会话，表示正在执行，处于活动状态。 2.killed 处于此状态的会话，表示出现了错误，正在回滚，当然，也是占用系统资源的。 还有一点就是，killed的状态一般会持续较长时间，而且用windows下的工具pl/sql developer来kill掉， 是不管用的，要用命令：alter system kill session 'sid,serial#' ; 3.inactive 处于此状态的会话表示不是正在执行的，比如select语句已经完成。 我一开始以为，只要是inactive状态的会话，就是该杀，为什么不释放呢。 其实，inactive对数据库本身没有什么影响，但是如果程序没有及时commit，那么就会造成占用过多会话。解决inactive的方法最好的就是在oracle中直接设置超时时间。 下面是解决的方法： Oracle会话达到一定连接时间自动断开的方法 IDLE_TIME：限制每个会话所允许的最长连续空闲时间，超过这个时间会话将自动断开。参数值是一个整数，单位是分钟。 1.Oracle数据库当中至少会有一个Profie文件，它可以限制口令和资源。建立oracle数据库时候，oracle会自动建立命名为default的profile，初始化的default有默认值，下面可以查看默认的profile的资源和口令限制的详细信息。 SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT'; PROFILE RESOURCE_NAME LIMIT -------------------- -------------------- -------------------- DEFAULT COMPOSITE_LIMIT UNLIMITED DEFAULT SESSIONS_PER_USER UNLIMITED DEFAULT CPU_PER_SESSION UNLIMITED DEFAULT CPU_PER_CALL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED DEFAULT LOGICAL_READS_PER_CALL UNLIMITED DEFAULT IDLE_TIME UNLIMITED 这个是空闲时间用来限制用户连接时间 PROFILE RESOURCE_NAME LIMIT -------------------- -------------------- -------------------- DEFAULT CONNECT_TIME UNLIMITED DEFAULT PRIVATE_SGA UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS 10 DEFAULT PASSWORD_LIFE_TIME 180 DEFAULT PASSWORD_REUSE_TIME UNLIMITED DEFAULT PASSWORD_REUSE_MAX UNLIMITED DEFAULT PASSWORD_VERIFY_FUNC NULL TION PROFILE RESOURCE_NAME LIMIT -------------------- -------------------- -------------------- DEFAULT PASSWORD_LOCK_TIME 1 DEFAULT PASSWORD_GRACE_TIME 7 16 rows selected. 查看某个用户的使用的profile文件，这个通过dba_users来查看。 SQL> select username,profile from dba_users where username='SYSTEM'; USERNAME PROFILE ------------------------------------------------------------ -------------------- SYSTEM DEFAULT 2.Oracle的系统参数RESOURCE_LIMIT是一个用来监控用户对于数据库资源使用的参数，当值为true的时候即为启用，否则禁用（默认是禁用的）。该参数结合profile来控制多种资源的使用，如CPU_PER_SESSION,CONNECT_TIME等等，从而达到节省资源来实现高效性能。通过下面语句可以在RAC当中查看系统参数RESOURCE_LIMIT是否开启。 SQL> select name,value from gv$parameter where name='resource_limit'; NAME VALUE resource_limit FALSE 3.首先开启这个参数 SQL> alter system set resource_limit=true; System altered. SQL> select name,value from gv$parameter where name='resource_limit'; NAME VALUE resource_limit TRUE 4.修改profile的idle_time,单位为分钟。设置为一小时60分钟。 SQL> alter profile default limit idle_time 60; Profile altered. 之后再查看一下 SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where RESOURCE_NAME='IDLE_TIME'; PROFILE RESOURCE_NAME LIMIT -------------------- -------------------- -------------------- DEFAULT IDLE_TIME 60 说明： A:参数RESOURCE_LIMIT=TRUE用于启用数据库资源限制； B:PROFILE用于实现资源的配置，创建或者修改已存在的PROFILE来调整各个具体资源的配置 C:一旦被限制的用户超出所设定的阀值将收到资源配置相关的错误提示 D：被限制资源的状态变为sniped E：被限制资源的session对应的server process并没有释放，需要结合sqlnet.expire_time来释放。 通过idle_time限制session idle 时间。session idle超过设置时间，状态为sniped (v$session).,然而OS下的process并不会释放，当session(user process) 再次与server process 通讯，将关闭相应的server process. 5.使用SQLNET__EXPIRE_TIME清楚僵死的连接： 数据库连接的客户端异常断开后，其占有的资源并没有被释放，从v$session数视图中依旧可以看到对应的session处于inactive状态，且对应的服务器进程也没有释放，导致资源长时间被占有。SQLNET.EXPIRE_TIME专门用于清理那些异常断开的情况：通过设定参数为非0值（单位为分钟）来发送探测包以检查客户端异常断开。一旦探测包找到了异常连接将返回错误，清楚对应的server process。 配置SQLNET.EXPIRE_TIME:对于SQLNET.EXPIRE_TIME的配置，需要修改sqlnet.ora，其路径为$ORACLE_HOME/network/admin下面。然后添加SQLNET.EXPIRE_TIME选项，之后重启监听。 6.总结： A：INACTIVE SESSION 是用户建立连接之后，尚未执行任何操作或操作已经完成但没有断开，等同于与处于idle状态，在v$session视图呈现的是INACTIVE状态。 B：对于使用resource_limit以及profile配置后用户session超出idle_time的情形，在v$sessin视图呈现sniped状态 C：当在sqlnet.ora配置文件中设置SQLNET.EXPIRE_TIME参数为非领值时，僵死连接在EXPIRE_TIME制定时间后被清楚（设定24小时后将INACTIVE状态的session置为SNPIED状态，之后再半小时后清除僵死连接，理论上是行得通的） D：设定SQLNET.EXPIRE_TIME为非零值之后，系统需要产生额外的开销以及带来网络性能的下降。 E：对于需要及时释放OS以及DB资源的情形，Oracle建议使用resource_limit以及profile限制的用户连接同时并设定SQLNET.EXPIRE_TIME为非零值。
