Home » RDBMS Server » Server Administration » problem in login the oracle user
problem in login the oracle user [message #294643] Fri, 18 January 2008 05:01 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

When i am login in to oracle through test/test123 it is login to oracle.

But when i want to login through application then i am not able to login.
At the same time the trace file is starts to create untill i will not kill the session. And those trace files are upto 10G also.

Please let me know what might be the reason behind it?

Thanks in advacne.

Re: problem in login the oracle user [message #294669 is a reply to message #294643] Fri, 18 January 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- you are not in the db server
- you did not set ORACLE_SID
- you set it to a different value
- you did not set ORACLE_HOME
- ...

Regards
Michel
Re: problem in login the oracle user [message #294671 is a reply to message #294643] Fri, 18 January 2008 07:03 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Please post the error message, the question is not clear
Re: problem in login the oracle user [message #294691 is a reply to message #294671] Fri, 18 January 2008 08:09 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I got the query while login to application.

When i execute this query I got the following error.

ORA-04030: out of process memory when trying to allocate 123404 bytes (QERGH hash-agg,kllcqas:kllsltba)

Please help on this.

I do not know anything about this error.

Thanks in advance.
Re: problem in login the oracle user [message #294694 is a reply to message #294691] Fri, 18 January 2008 08:27 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
shortage of RAM

What version are you using?

Try increasing pga_aggregate_target and Decrease sort_area_size and/or hash_area_size.

And you may even check your kernel ulimit settings


-Vik
Re: problem in login the oracle user [message #294695 is a reply to message #294691] Fri, 18 January 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-04030: out of process memory when trying to allocate %s bytes (%s,%s)
 *Cause:  Operating system process private memory has been exhausted

Regards
Michel
Re: problem in login the oracle user [message #294762 is a reply to message #294695] Sat, 19 January 2008 00:55 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I am using Oracle Release 10.2.0.1.0 vesrion.

Actually everything is working fine from last 1.5 years.
Every parameter is set as per needs.

Its strange to me that this error comes.

Please explain me in details if anyone has information.

Quote:

*Cause: Operating system process private memory has been exhausted



What need to do for this?
Please let me know.

Thanks in advance.
Re: problem in login the oracle user [message #294764 is a reply to message #294762] Sat, 19 January 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that the process already allocated the maximum space that it can, check OS or user limit ("ulimit -a" if you are on Unix).
Here it seems it happens during a hash operation.
Now it depends on what you were doing (have you PL/SQL tables?), what are your parameters (for instance those enumerated by vikramjogi).

Regards
Michel
Re: problem in login the oracle user [message #294767 is a reply to message #294643] Sat, 19 January 2008 01:22 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Dear Ora_2007 Please post maximum details & following in particular. This will indeed help forum members to arrive on some conclusion.

(a) OS & Oracle Version
(b) Program being used for connection e.g sqlplus / custom application.
(c) if custom application than, provider version/platform being used
(d) Full error message [do not hide any thing]
(e) Connecting from local or remote system
(f) any other information which you think may help in pin pointing the cause.

Regards

Manjit Kumar [mkbhati]
Re: problem in login the oracle user [message #294769 is a reply to message #294643] Sat, 19 January 2008 01:24 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Michel for your reply,

oracle@me:~> ulimit -a
core file size        (blocks, -c) unlimited
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) unlimited
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) unlimited
cpu time             (seconds, -t) unlimited
max user processes            (-u) 16384
virtual memory        (kbytes, -v) unlimited


I tried to increase pga_aggregate_target by 100m
Kept Decrease sort_area_size and/or hash_area_size as it is.
But not useful.


pga_aggregate_target = 755M Previously 655M
sort_area_size = 65536 Bytes
hash_area_size = 131072 Bytes

Is any other information is required.

I got the following error for only one oracle user for other useres everything fine.

Please help me.

Thanks in advance.
Re: problem in login the oracle user [message #294771 is a reply to message #294643] Sat, 19 January 2008 01:27 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
More information

1. OS - UNIX
2. Oracle Release 10.2.0.1.0 vesrion.
3. sqlplus Demo/Demo
4. Connection from the aplication developed in ASP.

SELECT id
, name
, viewfunc.fGetLeadAndTestCountCampaign(id) LEADCOUNT , 
ROUND(DECODE(viewfunc.fGetLeadCountDeliveredNW(id),0,0,
viewfunc.fGetLeadCountResponse(id)/viewfunc.fGetLeadCountDeliveredNW(id)*100),2) RESPONSEPCT ,
 viewfunc.fGetLeadCountResponse(id) RESPONSECOUNT , 
ROUND(DECODE(viewfunc.fGetLeadCountDelivered(id),0,0,
viewfunc.fgetleadcountopened(id)/viewfunc.fGetLeadCountDelivered(id)*100),2) OPENED , 
ROUND(DECODE(viewfunc.fgetleadcountopened(id),0,0,
viewfunc.fGetLeadCountResponse(id)/viewfunc.fgetleadcountopened(id)*100),2) CTROR 
FROM CAMPAIGN WHERE id IN 
( SELECT * FROM ( 
SELECT a.id FROM DEMO.CAMPAIGN a LEFT OUTER JOIN DEMO.iv_survey e  ON a.id=e.campaign_id , 
DEMO.users b , 
DEMO.campaignstatus c , 
DEMO.campaigntype d 
WHERE a.client_id = b.client_id 
AND b.dbusername=USER AND a.campaignstatus_id=c.id 
AND a.campaigntype_id=d.id 
AND (viewfunc.fhascampaignright(a.id)=1
OR viewfunc.fhascampaignrightstat(a.id)=1)
AND a.launcheddate IS NOT NULL
AND c.internalname = 'LAUNCHED' 
AND a.archiveddate IS NULL
AND viewfunc.fcampaignvisible(a.campaignfolder_id)=1
ORDER BY a.launcheddate DESC )
WHERE ROWNUM < 6 )
ORDER BY syscreated DESC

Error message is

ORA-04030: out of process memory when trying to allocate 123404 bytes (QERGH hash-agg,kllcqas:kllsltba)

ORA-06512: at "DEMO.VIEWFUNC", line 1471
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512: at "DEMO.VIEWFUNC", line 1478
ORA-06512:



[Updated on: Sat, 19 January 2008 01:44] by Moderator

Report message to a moderator

Re: problem in login the oracle user [message #294772 is a reply to message #294769] Sat, 19 January 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing to do without changing your application or data.
You encountered an OS limit.
If you want to prevent from this error you have to precisely know what was allocated and what Oracle was trying to do at the time the error raised.
As I said: do you have PL/SQL tables? If yes, limit their size.

Regards
Michel

Re: problem in login the oracle user [message #294777 is a reply to message #294772] Sat, 19 January 2008 01:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
WE dont have PLSQL tables used.

Quote:

If you want to prevent from this error you have to precisely know what was allocated and what Oracle was trying to do at the time the error raised.



My main concern is this user was able to login and execute all the queries as normal as other oracle user is doing.
Then what drastically happens so that this problem occurs.

As i mentioned before everything is fine from last 1.5 years.

I really do not understand why this happens in oracle?
Sad
Re: problem in login the oracle user [message #294778 is a reply to message #294777] Sat, 19 January 2008 01:42 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I am desperatly waiting for your replies.
Its very urgent for me.

Please help me.
Re: problem in login the oracle user [message #294779 is a reply to message #294643] Sat, 19 January 2008 01:47 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

I am at Mumbai,If I can be of some help. If you are willing we can discuss the problem, than you can give me a call on 25552503 or 9819509177

Regards
Manjit Kumar

Re: problem in login the oracle user [message #294780 is a reply to message #294777] Sat, 19 January 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each day I buy 10 candies and give 2 to each of my 5 children.
It has been working for years.
Now I have 6 children, why I can't give 2 candies of them?

You call too many functions in your query.
Check your functions, check the space they allocate.
You think you can do it in minutes, no it will take weeks.

Regards
Michel

[Updated on: Sat, 19 January 2008 01:49]

Report message to a moderator

Re: problem in login the oracle user [message #294791 is a reply to message #294643] Sat, 19 January 2008 04:12 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I got the cause of the problem.
The reason for the ORA-04030: out of process memory when trying to allocate 123404 bytes (QERGH hash-agg,kllcqas:kllsltba)
was because there was recursive call into the DEMO.VIEWFUNC function.

Thats why there was consumption of lot of memory.

Thanks for your time given to help me.
Re: problem in login the oracle user [message #294858 is a reply to message #294643] Sun, 20 January 2008 04:21 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Increase pga_aggregate_target and unlimited does not mean unlimited.It confine to 2GB. Set proper value.
Re: problem in login the oracle user [message #294868 is a reply to message #294858] Sun, 20 January 2008 05:42 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju, see OP previous answer, error comes from a wrong recursive call (I assume this means an infitive one as most often).

Regards
Michel
Previous Topic: connection to remote server as sysdba
Next Topic: example tablespace
Goto Forum:
  


Current Time: Wed Dec 07 03:06:37 CST 2016

Total time taken to generate the page: 0.05684 seconds