What is meaning of this error message?

Hi angel thanks for you reply. Can you tell me how to decrease the number of processes running? Please explain me.

let's first start with your current value of processes, what is the value?

       This is my current value.
show parameter processes

aq_tm_processes                      integer  1
db_writer_processes                  integer  1
job_queue_processes                  integer  2
log_archive_max_processes            integer  2
processes                            integer  200

200 should be fine...
what gives this query:

select username, count(*) 
from v$session
group by username

Select allOpen in new window

It gives like below
USERNAME          COUNT(*)
APPLSYSPUB    21
APPS           170
             8

as you can see, the USER APPS takes up 170 connections... which is the "main" problem, unless you rally have 150+ active users sitting around.
if that is the case, you will have to increase the processes to a higher value.
if not, you should instead check out the application(s) to release the connections (faster).

   Yes i know that angel. Everyone is accessing APPS schema. But I can't control that. And you tell me which one is better for increasing process to higher value or decrease the process connections. And i am not able to understand the below statement

--->if not, you should instead check out the application(s) to release the connections (faster).

if you don't understand that, I have a problem :) ---
I speak about the application code, which opens the connection to the oracle database and runs some sql statements... often, developers forget to properly close the connections ASAP in their code ...

anyhow, do you know how do change the init.ora setting?

Yes ok i understood that. And another main thing is i am not able to find init.ora file in my oracle directory. I searched it. But i am not getting it. Or else shall i give the below command.

alter system set processes = 300 scope=spfile;
              If i this give command will it create any problem like memory waste, low speed etc.

>   If i this give command will it create any problem like memory waste, low speed etc.
in short: no

you will need to restart the oracle db for the parameter value to be applied.

Is there any problem to restart oracle DB?? And please tell me which is the best way??

>Is there any problem to restart oracle DB??
obviously, any user being connected will be "dropped", and any non-committed transaction will be rolled back.

>And please tell me which is the best way??
there is only 1 way: shutdown + startup.

ok can you tell the procedure of shutdown and startup. Because i am using toad application. Please guide me.

Hi i have given the below command. But i am getting the following error. Why and What is reason?
SQL> alter system set processes=300 scope=spfile;
alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

>Because i am using toad application.
you cannot do that from toad. you have to use SQL*Plus command line for example, best on the oracle machine itself.

in regards to the error, that means you must have the init<SID>.ora file in the $ORACLE_HOME$/dbs folder ...

Sorry angel this file is not available in my ORACLE_HOME folder. But it will be created when we installed oracle database in the machine. But i don't know how it will happen? Or can you tell me how to create spfile?

spfile can only be created when you have the pfile.
so, you MUST have either the init<SID>.ora or the  spfile<SID>.ora.

what does
show parameter spfile

return?

 This is result when i execute the above query

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>

and this:

show parameter pfile

Select allOpen in new window

This also the same result

what are the folders you have under $ORACLE_HOME$ (on the oracle server itself)

There are so many folders. Which folder i have to mention?

database or dbs

Ya angel now it is working fine. One of our DBA has done this. But i don't know who has done this. He did not increase the parameter value. But he reduced the number of processing. Now the following query is giving
select username, count(*)
from v$session
group by username

USERNAME          COUNT(*)
APPLSYSPUB    9
APPS           114
             8

Anyway thanks for your cooperation.

Nice solution Angel.