Home » SQL & PL/SQL » SQL & PL/SQL » How to monitor number of processes?
How to monitor number of processes? [message #1282] Wed, 17 April 2002 00:13 Go to next message
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 #1291 is a reply to message #1282] Thu, 18 April 2002 03:09 Go to previous messageGo to next message
Amit
Messages: 166
Registered: February 1999
Senior Member
Try this

select * from v$process
Re: How to monitor number of processes? [message #1294 is a reply to message #1282] Thu, 18 April 2002 05:08 Go to previous message
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;
Previous Topic: synonym
Next Topic: Index column
Goto Forum:
  


Current Time: Thu Mar 28 09:35:18 CDT 2024