今天下午,开发人员突然说不能连接数据库了,提示相关的错误
OERR: ORA-12519 TNS:no appropriate service handler found
客户端连接间歇性失败,报错ORA-12519
Cause: the listener could not find any available service handlers that
are
appropriate for the client connection.
Action: run "lsnrctl services" to ensure that the instance(s) have registered
with the listener, and are accepting connections. 检查lsnrctl service
,instance已经注册,
状态显示ready时,可以连接。
When the listener believes the current number of connections has reached
maximum load,
it may set the state of the service handler for an instance to
"blocked" and begin refusing
incoming client connections with either of the
following errors: ora-12519 or ora-12516
采用服务动态注册的方式,由PMON 通过SERVICE_UPDATE 来得到目前连接情况,但SERVICE_UPDATE
有时间间隔,
所以,listener显示的连接数和当前实际的连接数可能不同。
查询解决方法:
查看一下数据库现有的进程数,是否已经达到参数processes的大小。
1.select count(*) from v$process;
取得数据库目前的进程数。
2.select value from v$parameter where name = 'processes';
取得进程数的上限。
3.如已达到上限,修改initSID.ora中的processes的大小。
4.重新启动数据库到nomount状态下,执行create
spfile from pfile; 并startup open。
查询数据库自启动以来最大的并发数量
select * from v$license
------ end -------
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wyzxg/archive/2008/03/06/2154274.aspx
ORA-12519: TNS:no appropriate service handler found 的解决
有时候连得上数据库,有时候又连不上.
可能是数据库上当前的连接数目已经超过了它能够处理的最大值.
SQL> select count(*)
from v$process;--当前连接数
COUNT(*)
63
SQL> select value from
v$parameter where name =
'processes'
--数据库允许的最大连接数
VALUE
500
修改最大连接数:
SQL> alter
system set processes =
2000 scope =
spfile;
重启数据库:
SQL>
shutdown immediate;
SQL>
startup;
--查看当前有哪些用户正在使用数据
SELECT
osuser, a.username,cpu_time/executions/1000000||'s',
sql_fulltext,machine
from
v$session a, v$sqlarea b
where
a.sql_address =b.address order
by cpu_time/executions desc;
--快速删除不活动进程
set heading off
spool
on
select p.SPID from v$session s,v$process p where s.paddr= p.addr and
s.machine='woogle';
spool off
set serveroutput on ;
declare
v_sid
number;
v_serial number;
v_sql varchar2(200) ;
CURSOR cur_session
is
select sid, serial# from v$session where
machine='woogle';
begin
open cur_session ;
fetch cur_session into v_sid
, v_serial ;
while cur_session%found
loop
dbms_output.put_line(v_sid||'
killed!') ;
v_sql:= 'alter system kill session
'||''''||v_sid||','||v_serial||'''';
execute immediate v_sql ;
fetch
cur_session into v_sid , v_serial ;
end loop ;
close cur_session ;
end
;
/
Linux
下快速删除不活动进程
#!/bin/bash
tmpfile=/tmp/tmp.$$
sqlplus ' / as
sysdba' << EOF
set heading off
spool on
spool
$tmpfile
select p.SPID from v$session s,v$process p where s.paddr= p.addr and
s.STATUS='SNIPED';
spool off
set serveroutput on ;
declare
v_sid
number;
v_serial number;
v_sql varchar2(200) ;
CURSOR cur_session
is
select sid, serial# from v$session where
STATUS='SNIPED';
begin
open cur_session ;
fetch cur_session into v_sid
, v_serial ;
while cur_session%found
loop
dbms_output.put_line(v_sid||'
killed!') ;
v_sql:= 'alter system kill session
'||''''||v_sid||','||v_serial||'''';
execute immediate v_sql ;
fetch
cur_session into v_sid , v_serial ;
end loop ;
close cur_session ;
end
;
/
分享到:
相关推荐
集合了 所有的 Unix命令大全 ...telnet 192.168.0.23 自己帐号 sd08077-you0 ftp工具 192.168.0.202 tools-toolss ... 各个 shell 可互相切换 ksh:$ sh:$ csh:guangzhou% bash:bash-3.00$ ... 命令和参数之间必需用空格隔...
- Bug # 4918539: ORA-ORA-06502 or ORA-01460 may occurs if a procedure is executed through the Run PL/SQL dialog box and a string with multibyte characters is assigned to one of the parameters. ...
Oracle Essbase & Oracle OLAP will help you architect the Oracle OLAP product that is most appropriate for your application, and build, tune, and maintain OLAP solutions. From the Back Cover ...
The solution begins with service-differentiated networks capable of providing appropriate grades of service to each application. This book takes the next step, showing how continuous media ...
The library has been tested on NT4 service pack 5 and Windows 2000. Windows 95, 98, ME and XP should be supported, but has not been tested. Linux and Kylix are not supported. There are *NO* plans to ...
The unified treat- ment of decoding techniques for LDPC codes presented here provides flex- ibility in selecting the appropriate design point in high-speed applications from a performance, latency, ...
appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or ...
Expert Oracle Indexing and Access Paths is about the one database structure at the heart of almost all performance concerns: the index. Database system performance is one of the top concerns in ...
The strength of the latter on the other hand, is that it can be implemented iteratively and is thus appropriate for application to high complexity systems. An effort to combine the best attributes of...
reassembly, and lubrication--so that service personnel will be able to understand equipment function, repair the equipment in a timely manner and order spare parts as necessary.To perform appropriate ...
At the heart of any good-performing database lies a sound indexing strategy that makes appropriate use of indexing, and especially of the vendor-specific indexing features on offer. Few databases ...
After applying this hotfix, the issues outlined in the description should no longer occur. ------------------------- TO UNINSTALL: 1) Shut down all running Serv-U processes. i) Right-click the tray...
Oracle PL/SQL Best Practices Preface When I first started writing about the Oracle PL/SQL language back in 1994, the only sources of information were the product documentation (such as it was) and ...
upsearch until a device scope is found before executing _ADR. This allows PCI_Config operation regions to be declared locally within control methods underneath PCI device objects. Fixed a problem ...
management functions, and to assist FCKMS procurers, administrators, service-providing organizations, and service-using organizations to select appropriate CKMSs or CKMS services. This Profile ...
In order to choose correctly the dimension of calibration model ... The results from real data sets demonstrated that MCCV could successfully choose the appropriate model, but leave-one-out CV could not
In case the software product was ordered by company Purchase Order, the appropriate invoice sent afterwards was paid within 30 days after invoice date. In case the software product was ordered on-line...
this service if you wish), that you receive source code or can get it if you want it, that you can change the software or use pieces of it in new free programs; and that you know you can do these ...
In this book I aim to help professional J2EE developers and architects make the appropriate choices to deliver high-quality solutions on time and within budget. I'll focus on those features of J2EE ...
No more need to run a manual and resource-intensive process. Frag Shield抯 MFT fragmentation prevention technology is now fully automatic and re-written to operate invisibly using InvisiTasking...