How to monitor number of processes? [message #1282] |
Wed, 17 April 2002 00:13 |
Jan Tischer
Messages: 1 Registered: April 2002
|
Junior Member |
|
|
Sometimes we get the ORA-0020 Message (maximum number of processes (%s) exceeded).
Is there a way to get the current number of processes by a SQL-Query?
By monitoring this parameter we could force reducing connections to database.
Thanks
...Jan.
|
|
|
|
Re: How to monitor number of processes? [message #1294 is a reply to message #1282] |
Thu, 18 April 2002 05:08 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
This will help. The prompt at the end (ALTER SYSTEM KILL SESSION) is for cut and paste if you want to kill a process.
rem =========================================================================
rem
rem ps.sql
rem
rem Copyright (C) Vladimir Begun, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This program is free software; you can redistribute it and/or modify
rem it under the terms of the GNU General Public License as published by
rem the Free Software Foundation; either version 2 of the License, or
rem any later version.
rem
rem This program is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem like Unix ps command
rem
SET LINE 132 PAGESIZE 1000 FEED OFF;
COLUMN "sid_serial" FORMAT A12
COLUMN "user" FORMAT A10
COLUMN "st" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A20
COLUMN "prg" FORMAT A30
COLUMN "logon_time" FORMAT A22
SELECT
''''
|| sid
|| ','
|| serial#
|| ''';' "sid_serial"
, username "user"
, status "st"
, schemaname "schema"
, osuser "osuser"
, machine "box"
, program "prg"
, TO_CHAR(logon_time, 'DD.MM.YYYY HH24:MI:SS') "logon_time"
FROM
v$session
WHERE type <> 'BACKGROUND'
AND osuser is not null
ORDER BY
osuser,program
/
PROMPT ALTER SYSTEM KILL SESSION;
SET LINE 120 PAGESIZE 25 FEED ON;
|
|
|