INACTIVE Sessions In Oracle?
Apr 22, 2011our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?
View 4 Replies
ADVERTISEMENT
Inactive Sessions In Database?
Dec 12, 2011In our databace there are around 300 sessions were in inactive state.
Database is not releas the sessions.
Query For Inactive Sessions Consuming Memory
Apr 5, 2013Share a query for inactive sessions consuming memory? I have found many inactive sessions with the following query and would like to join this information with memory usage as well.
select username, machine, program, to_char(LOGON_TIME, 'hh24:mi:ss DD-MM-YYYY'), LAST_CALL_ET/60, status from gv$session where status='INACTIVE';
Forms :: Killing Inactive Session Through Oracle Application Server
Mar 5, 2013I am trying use host command in Oracle forms. I get the process id as input from the user and on clicking ok the form should kill the session related to process id.
PS: Users will be entering only frmweb.exe process id which are inactive.
cmd := 'CMD /C taskkill /F /FI /pid 'process which is got as input' /IM frmweb.exe'; host(cmd)I also tried by writing the above command into a .bat file in application server.When trying to execute the bat file it din work. But when tryin to run the bat file by double clicking in Application server the session was killed.
On executing the above I am unable to kill the process. I would also like to know whether the host command was successful or not. Orakill and alter session are working but I don want to use it.
Server Administration :: Oracle 10g Blocking Sessions
Aug 19, 2010I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.
But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.
The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.
SQL & PL/SQL :: UPDATE Query Opens Multiple Oracle Sessions
Feb 24, 2011We recently migrated from 9i to 11g.
One UPDATE query runs for 2min and it opens multiple oracle sessions. Almost 40 sessions. Once the UPDATE is done, the sessions close.
When monitoring V$SESSION, found that the newly opened sessions have a PROGRAM value like "oracle@server (P001)"
The UPDATE query is as below. The query works fine, no issue with performance.
SET t1.text =
( SELECT text
FROM table2 t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ROWNUM < 2
[code].......
SQL & PL/SQL :: How Oracle Manages Multiple Sessions In A Database Performing DML
Feb 25, 2011I was just wondering how Oracle manages multiple sessions in a database performing DML. I believe this is related to 'Read Consistency' and I tried to search for the same but could not get any satisfactory online documents.
CASE 1:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
issues rollback
user C logs in to a database1
issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
CASE 2:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
user C logs in to a database1
user B issues rollback
user C issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
NOTE: All the users are currently logged in to the same database and none has logged out.
Oracle / EMC - Snap View Snapshots And Consistent Sessions
Nov 22, 2010I have some questions about Oracle + EMC shared storage. I have Oracle 11gR1 RAC (2nodes) + ASM environment with shared shorage EMC Clariion AX4.
The database is running no archivelog mode. I'd like to implement point-in-time recovery using Snap View snapshots.
Currently my AX4 platform has the following LUNS:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
Using source LUNs in consistent session will take sync snapshots of all the LUNs working against my database. Once something happens with database, the LUNs can be returned to specific point in time when snapshot consistent session was taken and I'm expecting the database will up and continue to work.
Questions:
1. Is my approach correct at all? (The database is running noarchivelog mode, not dealing with hot backups. The recovery point in time implemented purely via EMC snapshot consistent sessions.)
2. The AX4 platform has a limit 8 source LUNs in session. If I understand it correctly, I can't place more than 8 LUNs in session. What will be the workaround if my database will occupy more than 8 LUNs, I'd like to take their consistent snapshot; for example:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
LUN8 - ASM - DISKGROUP DATA DISK DATA03 (actual db datafiles)
LUN9 - ASM - DISKGROUP DATA DISK DATA04 (actual db datafiles)
SSO Preventing Multiple Concurrent Oracle Sessions From Loading
Nov 16, 2012We are experiencing a problem with SSO causing 2nd or 3rd concurrent Oracle sessions to hang. The Oracle application hangs during loading and the task manager has to be used to close the application.
I have tested logging onto our application servers using SSO and I cannot load more than 3 concurrent Oracle sessions. When I bypass the SSO and logon to the same server I can load more than 20.
Session Inactive State?
Jul 1, 2012want to know the following
1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?
How To Kill Inactive Connections Automatically
Apr 26, 2011i was gone through the below link
[URL]
so when i changed my idle_time value in profile and the when the user exceeds the idle_time value the user still i can see in-active state in v$session.
and when i was tried to execute any query on inactive session that time i got an ORA- error and then session was not visible from v$session.
in my environment inactive session was not getting used afterwards so is there any way to kill that sessions automatically once reached idle_timeout value.
lets say i have 50 max sessions, out of which 10 are inactive state and 40 in active state .what if i created one more sessions will that give me ORA- error stating max sessions reached or it will kill the one session which are in-active state.
Forms :: List Item Becomes Inactive After Using Once
Dec 6, 2012i have a form having 5 listitems for making selections. once selecting through listitems i am adding this selection into below datablock by a ok button and saving it into database by a ok button. as i made one selection and press ok buttton it's working fine but after this when i want one more selection through listitems, listitems becomes inactive. it's not working after one selection. my coding on ok button is like this:
begin
select distinct lcode intolcode_ss from vac_view where locality=:vac_view.locality;
select distinct hid intohid_ss from vac_clr_vu where vr_no=:vac_view.vr_no;
:prop_temp.vr_no :=:vac_view.vr_no;
:prop_temp.qtr_type :=:vac_view.qtr_type;
:prop_temp.locality :=:vac_view.locality;
[code]....
may be it's all because of clear_list('vac_view.locality'); but i have to put this as i want to make clear all listitems after making each selections.
Server Administration :: High CPU For Inactive Session?
Nov 22, 2012when I was analyzing high CPU utilization issue, I saw that the most of the top PID's were INACTIVE in database. But it was utilizing more than 4% CPU. how it is utilizing CPU without doing any work in database?
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
24013 oracle 2167M 1581M sleep 53 0 0:01:53 20% oracle/1
13260 oracle 2167M 1580M sleep 30 0 0:01:16 14% oracle/1
24399 oracle 2167M 1582M sleep 59 0 0:03:01 4.2% oracle/1
11509 oracle 2178M 1593M sleep 59 0 0:02:10 2.8% oracle/11
[code].....
Forms :: Change Color Of Active / Inactive Tab Canvas
Apr 19, 2010how can we change the color of active & inactive Tab canvas dynamically. I am using Dev Suite 10gR2
View 2 Replies View RelatedServer Administration :: Session Inactive But Running Queries?
Mar 4, 2010I am using 10.2.0.4 oracle database on Solaris 10. We are running some Peoplesoft upgrade on one of the database from last 6-7 hours. The status of the session is INACTIVE from couple of hours. And the sql_id is null for these sessions.
select sid,serial#,STATUS,to_char(logon_time,'DD-MON_YY HH24:MI:SS') from v$session where username like '%&usern%' ORDER BY 4 DESC;
533 2234 INACTIVE 04-MAR_10 01:59:02
525 5919 ACTIVE 04-MAR_10 01:54:50
534 4186 INACTIVE 04-MAR_10 01:05:15
520 2320 INACTIVE 04-MAR_10 00:29:44
[code].....
While when i see the current sql from the v$active_session_history, it keep on changing after every 10-15 minutes. Using below query to find the sql_text for the session from v$active_session_history.
select sql_id, sql_text
from v$sql
where sql_id in (
select sql_id
from v$active_session_history
where session_id=511
[code].....
We have not faced issue like this during Peoplesoft upgrade. what could be the reason that the status is not changing while the base sqls keep on changing after every 10 minutes.
Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days
Sep 2, 2011I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
View 7 Replies View RelatedSessions Value Always Seems To Be Higher Than Set Value
Jul 23, 2010I have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set...
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora
System parameters with non-default values:
processes = 1000
sessions = 1524
And one more thing is there any recommended formula for calculate sessions in 11g? [ I found one formual from google:
SESSIONS derived: (1.1 * PROCESSES) + 5
Sessions Based On SQL ID
Aug 31, 2012One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
View 9 Replies View RelatedPossible To Limit Sessions In These Views
Jan 17, 2013I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies View RelatedSessions Processes And Transactions
Nov 20, 2012Oracle database 11g Expression Edition 11.2.0.2
I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.
alter system set processes=150 scope=spfile;But why that modified value is not being reflected?
select name, value from v$parameter where name in ('sessions','processes','transactions');
NAME VALUE
-------------------- -----
processes 100
sessions 172
transactions 189
Processes / Sessions Connected To DB After ORA 20 Error?
Dec 28, 2010Is there any way to tell what processes/sessions were connected to a DB after I get an ORA 20error. As you can see from the query results below, I did hit the max value of processes but I would like to know when it happen and who was connected.
I realize I can up the value, but before I do that I want to see what caused this to happen.
SQL> select * from v$resource_limit where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
LIMIT_VALU
----------
processes 221 1000 1000
1000
sessions 223 1020 1105
1105
Security :: Limit Sessions Per User In RAC
May 6, 2011The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
SQL & PL/SQL :: Disconnect User Who Are Blocking Sessions
Apr 11, 2011I have an assignment, i am suppose to disconnect all the users that are blocking sessions for over three hours. Below is my code which is not working
Create or replace procedure b_user IS
Declare
ssid number;
serial number;
Begin
[code]......
Performance Tuning :: Locking Of Two Sessions?
Apr 27, 2012how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.
We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.
Client Tools :: 2 Sessions For One Connection?
Oct 30, 2010why are there in the Administration --> --> Session 2 entries for 1 database connection (here with benthic golden).But if I login in a browser, there is only 1 entry for this database connection
View 3 Replies View RelatedSQL & PL/SQL :: Number Of Active Sessions With Same Sentence?
Oct 20, 2011I have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result.
SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null
[code]...
SQL & PL/SQL :: Difference In Table Data Between Sessions
May 27, 2011I create table in session1:
create table marco_tmp_270511
as
select 5 as t from dual
Then I log in session2 and perform query to
select * from marco_tmp_270511 and receive 5 in column t.
Then I return to session1 and perform:
delete marco_tmp_270511;
insert into marco_tmp_270511
select 99 as t from dual ;
select * from marco_tmp_270511;
Then I go again to session2 and perform query to
select * from marco_tmp_270511
and still receive 5 in column t. How do I synchronize data between sessions?
SQL & PL/SQL :: Kill One Users Sessions Using A Procedure?
Mar 1, 2012I want to create a procedure that kills the sessions of the user TEST.
I have coded the below, but that dont work.
CREATE OR REPLACE PACKAGE BODY SYSTEM.test AS procedure killsessions IS
v_sessions_sql varchar2(40);
--v_sessions_sql2 varchar2(40);
[Code].....
How To Kill Invalid Sessions Automatically
Jul 3, 2013On the oracle 11g, we see too many invalid sessions. Because of which, login is getting restricted. What do i need to do to kill those invalid session periodically, which will avoid of restarting of system.
View 4 Replies View RelatedSQL & PL/SQL :: DBMS Job Commit Other Sessions Automatically
Apr 14, 2011I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid .
my procedure is
Create My_proc is
Begin
Insert into my_table values(1,2,3);
End;
And my job proc is
Create my_job_proc is
Begin
dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false);
commit;
End;
now i am calling my job proc is
Declare
Begin
my_job_proc;
End;
Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'