Home » Infrastructure » Linux » Maximum concurrent session per day (Oracle, 11.1.0.7,linux)
Maximum concurrent session per day [message #593978] Thu, 22 August 2013 06:42 Go to next message
sureshram24
Messages: 13
Registered: August 2013
Location: Bangalore
Junior Member
Hi All,

Could you please help me how can i write pl/sq to find maximum concurrent session on day basis?
I tried writing below code but throws an unique constraint violated error when executing.
Below is the snippet of the code from pl/sql.

cursor summ_1 is
select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from TEST_SESS_SUMM_DAILY where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;
summ_rec1 summ_1%rowtype;
cursor summ_2 is
select distinct trunc(start_ts,'hh24') as start_dt,
trunc(end_ts,'hh24') as end_dt,trunc(start_ts,'DDD') start_t,count(SESS_ID) sess_cn
from PCM_SESSION_SUMMARY
where trunc(start_ts,'ddd')= summ_rec1.start_ts
group by trunc(start_ts,'hh24'),trunc(end_ts,'hh24'),trunc(start_ts,'DDD')
order by 1;
summ_rec2 summ_2%rowtype;
begin
open summ_1;
loop
fetch summ_1 into summ_rec1;
exit when summ_1%NOTFOUND;
open summ_2;
loop
fetch summ_2 into summ_rec2;
exit when summ_2%NOTFOUND;
insert into TEST_SESS_SUMM_DAILY values (summ_rec1.start_ts,summ_rec1.user_cnt,summ_rec1.Sess_cnt,summ_rec2.sess_cn);
Re: Maximum concurrent session per day [message #593979 is a reply to message #593978] Thu, 22 August 2013 06:44 Go to previous messageGo to next message
sureshram24
Messages: 13
Registered: August 2013
Location: Bangalore
Junior Member
Am getting below error..

SQL> exec test_19aug13_2;
BEGIN test_19aug13_2; END;

*
ERROR at line 1:
ORA-00001: unique constraint (G3COST.SYS_C00687927) violated
ORA-06512: at "G3COST.TEST_19AUG13_2", line 27
ORA-06512: at line 1


SQL>
Re: Maximum concurrent session per day [message #593987 is a reply to message #593978] Thu, 22 August 2013 06:58 Go to previous message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Tue, 13 August 2013 16:03
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Tue, 13 August 2013 16:27
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel


What don't you understand in "ORA-00001: unique constraint (G3COST.SYS_C00687927) violated"?
Note that we have not your table(s) and constraint(s) and so can't help you more.

Regards
Michel
Previous Topic: Need Help for write script
Next Topic: How to increase Oracle SGA more than 4GB on RHEL 5.4 32 bit
Goto Forum:
  


Current Time: Sat Dec 20 00:02:21 CST 2014

Total time taken to generate the page: 0.11992 seconds