Maximum Number Of Processes Per Database User

Jun 1, 2013

I am using Oracle 11g R2. (11.2.0.3)

I know that in the profiles of the user I can set limit for the number of sessions per database user, using the below command:

alter profile <profile_name> limit sessions_per_user=<required_number>;

I need to set maximum number of processes for the application user so in case they opened too many number of processes on the database they do not block the automated jobs to be run.My questions:

1. set a limit on sessions_per_user is it the same as a limit on processes? 1 proccess= 1 session ? Is there any way to limit the number of processes (not sessions) for a database user?
2. I am using RAC so the number of sessions is per the whole database, while the max number of processes is by instance (not database), so even I set a limit on the number of sessions (above the limit of processes for one of the instances) one instance may face ORA-20 errors, while the max number of sessions did not hit the limit. is there any workaround for this case?

View 5 Replies


ADVERTISEMENT

ORA-00020 Maximum Number Of Processes

Aug 23, 2012

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - ProductionI got below error on the datases.

ORA-00020: maximum number of processesBased on the suggestion got from previous posts, I did kill some (LOCAL=NO) processes to get myself in. When I looked at the number of processes the number is 150, I changed it to 1000. After a day I got the same error.

ORA-00020: maximum number of processes (1000) exceededI killed processes again to let my self in, bases on the post below, I queried database

ORA-00020: maximum number of processes (%s) exceededHere is the query to find out if there are any processes with out session attached to it.

select spid from v$process where addr not in (select paddr from v$session);Result

SPID
null
35259840
36897866
13369762

[Code]...

To determine the from where greatest number of processes originated

SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;Machine     Count(*)
     2
CAPITAL-D3F0092     1
DQAKDCDKLCSW01     14
desmond     20
DQAKDCDKLCSA01     52

My question is why I am keep getting this? Why do increasing process from 150 to 1000 did not resolve the issue?

What can I do to make sure that I don't get this error again?

View 1 Replies View Related

Maximum Number Of Processes (1100) Exceeded

Sep 16, 2011

i got this error..

ORA-00020: maximum number of processes (1100) exceeded

my oracle version is 10.2.0.2.0

when i was trying to login to oracle i got :ERROR at line 1: ORA-01012: not logged on

lucky i was having another sqlplus login ..

wht i should do to bring the number of process down without increasing the process value and shuting/restaring down db.

"Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there."

this is what i got from [URL]....

1.if i am giving ps -ef in OS it will give lot of detail ...
2. in v$session wht i should look for ....

View 4 Replies View Related

SQL & PL/SQL :: ORA-00020 Maximum Number Of Processes 150 Exceeded

Jun 9, 2013

I don't know if there is a beginner forum out there for PL SQL, but I cannot find one.I just started taking an Intermediate Oracle course to learn PL SQL. Today is my first day and I just installed Oracle 11g. I was in SQL Plus, tried the edit command. I got the message "wrote file Afiedt.buf" and then it was just a flashing cursor in the line below. I could not type any commands. I just hit the X to close the program.

What could I have done to exit the program with that flashing cursor since I was unable to type anything?

View 7 Replies View Related

Server Administration :: ORA-00020 / Maximum Number Of Processes (400) Exceeded

Aug 9, 2013

To investigate further on this error ,i want to find how many user connections got established during the error time and their details like machine,username,program,status etc in database ?

View 6 Replies View Related

Oracle 11g R2 - (SHAD) Process Causing ORA-00020 Maximum Number Of Processes

May 23, 2013

Oracle 11g R2 on WIndows 20o8 R2 Standard edition

I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.

I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.

From v$session, the program is OMS.

From v$process, the hostname is the server itself.

I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?

View 3 Replies View Related

Server Administration :: Maximum Number Of Connection Of Particular User?

May 2, 2012

how many maximum connection, a particular user can have in oracle. and how many maximum connection if user has 'resource profile' as default.and i am using oracle 11.2.0.1.0

View 1 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

Server Administration :: Getting Total Number Of Processes And Currently Used Process In Oracle?

May 4, 2012

is there any view in oracle for getting the total number of processes and currently used process in oracle? i am using oracle 11.2.0.1.0.

View 3 Replies View Related

SQL & PL/SQL :: How Many Maximum Number Of Bind Variables

Mar 10, 2010

How many maximum number of bind variables,can we use in Execute Immediate.

View 2 Replies View Related

SQL & PL/SQL :: DBMS_JOB / Maximum Number Of Failures A Job Can Allow

Mar 9, 2010

What is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.

View 1 Replies View Related

Maximum Of VALUE Field Of Each Record With Different POINT-NUMBER?

Oct 21, 2011

I need to get the maximum value of the VALUE field of each record with different POINTNUMBER, then do an update on the 2nd table.

table 1
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

table 2 (idem structure)
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

Where POINTNUMBER is six thousand different values My query only returns me the maximum of a single record:

SELECT * FROM table1 WHERE value = (SELECT MAX (value) FROM table2);

And the update:
UPDATE table2 SET to a.value = (SELECT MAX (b.value) FROM table2 b);

so does on a single record.That needed to maximize each pointnumber different? I can use a cursor to do this easier?

Insertion should first make a one-time, and then updates every 1 hour

View 1 Replies View Related

PL/SQL :: Dynamically Get Maximum Value Permitted In A Number Column?

May 18, 2013

How can we dynamically get maximum value permitted in a number column? Is there any in-built function in oracle for this?

e.g. Accrued_Interest NUMBER(10,4)

In this case maximum value that can be inserted in this column is "999999.9999".

View 2 Replies View Related

RMAN Error Maximum Number Of 128 Rows Exceeded

Nov 2, 2010

I read the error description. In my case there is only one full backup at a time running, hence it does not make any sense.

RMAN>
connected to target database: OTATEST (DBID=3130218754)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded

SQL> select count(*) from V$RMAN_STATUS;

COUNT(*)
----------
560

SQL> select count(*) from V$RMAN_OUTPUT;

COUNT(*)
----------
32834

View 1 Replies View Related

SQL & PL/SQL :: ORA-00036 Error (maximum Number Of Recursive Levels)

Aug 19, 2010

Ive just tried running a simple

update BASIC set 'column name' = NULL

Which works fine if i specify a where clause that returns a low amount of values, but im trying to run this update for the whole column (1000's or records).

Ive had no experience of this error before and am unsure of where to start, ive had a quick read around but see something of removing triggers?

The full error is :

ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "new.su_Table", line 61

View 22 Replies View Related

SQL & PL/SQL :: Finding Record In Table Having Value For Maximum Number Of Columns

Nov 25, 2011

if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.

For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1

so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".

View 4 Replies View Related

PL/SQL :: Trigger Error Maximum Number Of Recursive SQL Levels

Aug 29, 2012

I have getting error will insert into table i.e

i have a trigger as below

create or replace trigger INS_ERRORS
before insert on MIG_STG_ERRORS
for each ROW
declare
V VARCHAR2(22);
[code]........ 

when i insert into MIG_STG_ERRORS getting error message like 00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"

*Cause:    An attempt was made to go more than the specified number of recursive SQL levels.
*Action:   Remove the recursive SQL, possibly a recursive trigger.

View 4 Replies View Related

Reports & Discoverer :: Using Parameter For Maximum Rows To Fetch To Be A Number

Dec 15, 2010

In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.Is it possible to use a number that is selected from another query? When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number

View 10 Replies View Related

Server Administration :: ORA-00059 / Maximum Number Of DB_FILES Exceeded

Oct 1, 2012

i am getting ORA-00059:maximum number of DB_FILES exceeded, so i used following on one node

SQL> alter system set db_files=700 scope=spfile; system altered.

now do i need to re-bounce(restart) the db.

View 2 Replies View Related

PL/SQL :: Error ORA-01795 - Maximum Number Of Expression In A List Is 1000

Jan 30, 2013

we are using database 11g.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

i am getting one error while selecting the data from table.

SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;

Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )

View 13 Replies View Related

PL/SQL :: Trigger - Error - ORA-00036 - Maximum Number Of Recursive SQL Levels?

Aug 10, 2013

From front end the user will enter VACANCY_ID in the label box and once he saves that transaction, in background VACANCY_CD filed(sequence) willautomatically generated

.below is my code:{code}create table tmp1(vac_id number,vac_cd number);create sequence tmp1_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;select tmp1_seq.nextval from dual; ---initiating seqselect tmp1_seq.currval from dual; --checking the current val--trigger creationCREATE OR REPLACE TRIGGER TRIGGER1 AFTER  INSERT ON TMP1 FOR EACH ROW   DECLARE seqval

[code]...

Error: ORA-00036: maximum number of recursive SQL levels (50) exceededORA-00036: maximum number of recursive SQL levels (50) exceeded 

View 6 Replies View Related

Server Administration :: ORA-35571 / Maximum Number Of Load Errors Has Occurred

Jul 29, 2011

In our development database, we have created 5 dimensions and a cube with 2 measures in OLAP. All these are mapped to relational tables. When we try to load this cube (MAINTAIN CUBE option from AWM) we are getting this below error:

An error has occurred on the server
Error class: Express Failure
Server error descriptions:
INI: error creating a definition manager, Generic at TxsOqConnection::generic<BuildProcess>
INI: XOQ-01600: OLAP DML error "ORA-35571: The maximum number of load errors has occured." while executing DML

[code].....

What are the database parameter i should look for as a DBA ?

View 1 Replies View Related

Server Administration :: Find Maximum Number Of Concurrent Logins Over A Period Of 1 Month

Nov 18, 2011

Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.

View 4 Replies View Related

Performance Tuning :: Can Have Multiple LGWR Processes For Single Database

Jul 2, 2010

its possible to have multiple LGWR processes for a single database.If its possible how does the multiple processes write from redo log buffer to online redo log file.

View 3 Replies View Related

Semantic Technologies :: Maximum Node ID Larger Than Java Maximum Integer Value

Mar 7, 2013

I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:

-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....

when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.

Did I miss something when I created my network?

View 11 Replies View Related

Server Utilities :: Field In Datafile Exceeds Maximum Length For Number Field?

Apr 23, 2010

Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.

View 13 Replies View Related

Server Administration :: Maximum Process Is Set To 150 In One Of RAC Database

Apr 6, 2013

Currently maximum process is set to 150 in one of the RAC database. What should we do if the current session is about to reach maximum process or has exceeded the max process.

View 7 Replies View Related

PL/SQL :: Create Trigger To Insert Data From One User To Another User In Same Database?

Apr 16, 2013

I Created One Trigger as Follows

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT
ON table1
FOR EACH ROW

[code]......

Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist

View 3 Replies View Related

Security :: To See Any Database For The Operations Of Sys User Or Any Other User

Aug 23, 2011

I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.

Value for the parameter "AUDIT_TRAIL" is set to "db,extended".

I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved